pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNotation of index
3+ messages / 3 participants
[nested] [flat]
* Notation of index
@ 2004-01-07 17:09 Andreas <maps.on@gmx.net>
0 siblings, 2 replies; 3+ messages in thread
From: Andreas @ 2004-01-07 17:09 UTC (permalink / raw)
To: pgsql-sql
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
...
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Notation of index
@ 2004-01-07 18:25 Richard Huxton <dev@archonet.com>
parent: Andreas <maps.on@gmx.net>
1 sibling, 0 replies; 3+ messages in thread
From: Richard Huxton @ 2004-01-07 18:25 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Notation of index
@ 2004-01-07 18:36 Peter Eisentraut <peter_e@gmx.net>
parent: Andreas <maps.on@gmx.net>
1 sibling, 0 replies; 3+ messages in thread
From: Peter Eisentraut @ 2004-01-07 18:36 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-01-07 18:36 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-07 17:09 Notation of index Andreas <maps.on@gmx.net>
2004-01-07 18:25 ` Richard Huxton <dev@archonet.com>
2004-01-07 18:36 ` Peter Eisentraut <peter_e@gmx.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox