From: Richard Huxton <dev@archonet.com>
To: Andreas <maps.on@gmx.net>, pgsql-sql@postgresql.org
Subject: Re: Notation of index
Date: Wed, 7 Jan 2004 18:25:46 +0000
User-Agent: KMail/1.5
References: <3FFC3D30.2080508@gmx.net>
In-Reply-To: <3FFC3D30.2080508@gmx.net>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200401071825.46187.dev@archonet.com>

On Wednesday 07 January 2004 17:09, Andreas wrote:
> Hi,
>
> I'm moving from MySQL to Pg.
> MySQL lets me create indices like this:
>
> CREATE TABLE t (
>   id         INTEGER        NOT NULL,
>   numba      INTEGER         NOT NULL,
>   txt        VARCHAR(100) NOT NULL,
>   anosanumba INTEGER         NOT NULL ,
>
>   PRIMARY KEY (id),
>   INDEX (numba),
>   UNIQUE (anosanumba)
> );
>
> PostgreSQL doesn't like the line :
>   INDEX (numba),
>
> Instead I have to use :
>   CREATE INDEX idx_t_numba ON t (numba);
> outside the CREATE TABLE statement.

AFAIK there are two strands to this:
1. The INDEX keyword inside a CREATE TABLE statement is non-standard
2. PRIMARY KEY/UNIQUE are not indexes - they just happen to create one.

In theory, you can enforce uniqueness without the use of an index. In practice 
of course, with any reasonable number of rows you will want one.

-- 
  Richard Huxton
  Archonet Ltd