Short notation for creating indices ?

From: Andreas <maps(dot)on(at)gmx(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: Short notation for creating indices ?
Date: 2004-01-07 17:11:07
Message-ID: 3FFC3DAB.8030403@gmx.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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
...

Browse pgsql-novice by date

  From Date Subject
Next Message Rob Sell 2004-01-07 19:08:07 See how many connections
Previous Message Martin Hampl 2004-01-07 16:27:02 Re: partial index on varchar-coloumn in 7.4.1