How to use as Functional Index to be used as Primary KEY

Janning Vygen wrote:

Of course i can use an index like this:

CREATE TABLE members (
nickname text NOT NULL,
);
CREATE UNIQUE INDEX ix_name ON test (lower(name));

but the key isn't marked as primary then.

So here is my question: How can i define a functional index to be
used with a primary key (using postgreSQL 7.4.3)?

You can't. The primary key must be an actual data value. You can
define a more restrictive unique constraint in addition.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Janning Vygen <vygen@gmx.de> writes:

So here is my question: How can i define a functional index to be used
with a primary key (using postgreSQL 7.4.3)?

You can't. The SQL spec says that primary keys are columns or lists of
columns.

I don't see any particular difference between a primary key and a unique
index, anyway, except that the primary key is the default target for
foreign-key references.

regards, tom lane

Am Samstag, 31. Juli 2004 17:13 schrieb Tom Lane:

Janning Vygen <vygen@gmx.de> writes:

So here is my question: How can i define a functional index to be used
with a primary key (using postgreSQL 7.4.3)?

You can't. The SQL spec says that primary keys are columns or lists of
columns.

I don't see any particular difference between a primary key and a unique
index, anyway, except that the primary key is the default target for
foreign-key references.

Thanks to Tom and Peter for your answers. I will design my table without a
primary key and use my unique index instead of a primary key. As this unique
key is the same as a primary key i dont see the reason why postgresql
should't extend the specs and allow functional primary key indizes.

kind regards,
janning

Janning Vygen <vygen@gmx.de> wrote:

[snip]

Thanks to Tom and Peter for your answers. I will design my table without a
primary key and use my unique index instead of a primary key. As this unique
key is the same as a primary key i dont see the reason why postgresql
should't extend the specs and allow functional primary key indizes.

Because, as Tom Lane wrote: "The SQL spec says that primary keys are
columns or lists of columns." Unlike some other SQL RDBMs, PostgreSQL
at least *tries* (mostly) to be SQL standards compliant.

Jim

jseymour@linxnet.com (Jim Seymour) writes:

Janning Vygen <vygen@gmx.de> wrote:

Thanks to Tom and Peter for your answers. I will design my table without a
primary key and use my unique index instead of a primary key. As this unique
key is the same as a primary key i dont see the reason why postgresql
should't extend the specs and allow functional primary key indizes.

Because, as Tom Lane wrote: "The SQL spec says that primary keys are
columns or lists of columns." Unlike some other SQL RDBMs, PostgreSQL
at least *tries* (mostly) to be SQL standards compliant.

Or at least we try to pick our extensions carefully ;-). I don't see
the point of this one ...

regards, tom lane

Also, UNIQUE INDEX allows not null values, whereas PRIMARY KEY does not.

Take care.

Am Montag, 2. August 2004 13:57 schrieb Jim Seymour:

Janning Vygen <vygen@gmx.de> wrote:

[snip]

Thanks to Tom and Peter for your answers. I will design my table without
a primary key and use my unique index instead of a primary key. As this
unique key is the same as a primary key i dont see the reason why
postgresql should't extend the specs and allow functional primary key
indizes.

Because, as Tom Lane wrote: "The SQL spec says that primary keys are
columns or lists of columns." Unlike some other SQL RDBMs, PostgreSQL
at least *tries* (mostly) to be SQL standards compliant.

There are MANY things which are not standard compliant in PostgreSQL and my
guess is that the SQL spec doesn't even know anything about indizes.

kind regards,
janning