Thread: Notation of index

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.
And as far as I understand I do have to give an plain INDEX explicitely 
a name while Pg makes one up for the UNIQUE.

The point is, that I want to use foreign keys and I figure it helped if 
those fk-columns were indexed but PG won't create indices for columns in 
fk-constraints automatically.
Am I right ?

Why can we use INDEX the same way as UNIQUE ?
Perhaps even as in
... numba   INT4    NOT NULL    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

Andreas wrote:
> Why can['t] we use INDEX the same way as UNIQUE ?
> Perhaps even as in
> ...
>   numba   INT4    NOT NULL    INDEX
> ...

If the choice were just index or no index, then this might be ok, but 
when you create an index you can choose the index type, the operator 
class, a partial index predicate, so the notation would quickly become 
complicated.  You can also consider similar arguments about why rules 
and triggers are created separately.

What would clearly be useful is a way to automatically generate names 
for indexes.  Maybe we should think about implementing something for 
that.