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

Hi,

i searched the docs and the archives and was really wondering that i have not
found anything searching for "functional index primary key".

i would like to have a table of members with nicknames which are
case-insensitive but i would like to save the user given name case-sensitive
to display 'JimKnopf' instead of 'jimknopf':

CREATE TABLE members (
  nickname      text      NOT NULL,
  CONSTRAINT pk_test PRIMARY KEY (lower(name))
);

psql:scratch.sql:7: ERROR:  syntax error at or near "(" at character 92

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)?

can anybody help me with this problem or give me a link to a archived mail
about this topic?

kind regards,
janning

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