Re: Notation of index

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