Re: PRIMARY KEY on a *group* of columns imply that each - Mailing list pgsql-general

Stephane Bortzmeyer wrote:
> On Wed, Apr 27, 2005 at 10:26:30AM -0400,
>  Tom Lane <tgl@sss.pgh.pa.us> wrote
>  a message of 9 lines which said:
>
>
>>If that's what you want, declare it as UNIQUE not PRIMARY KEY.
>
>
> As shown by Patrick TJ McPhee, it does not work:
>
> tests=>  create table x (
> tests(>    name TEXT NOT NULL,
> tests(>    address INET,
> tests(>    CONSTRAINT na UNIQUE (name, address)
> tests(>  );
> NOTICE:  CREATE TABLE / UNIQUE will create implicit index "na" for table "x"
> CREATE TABLE
> tests=> INSERT INTO x (name) values ('foobar');
> INSERT 45380 1
> tests=> INSERT INTO x (name) values ('foobar');
> INSERT 45381 1
> tests=> INSERT INTO x (name) values ('foobar');
> INSERT 45382 1
> tests=> INSERT INTO x (name) values ('foobar');
> INSERT 45383 1
> tests=> select * from x;
>   name  | address
> --------+---------
>  foobar |
>  foobar |
>  foobar |
>  foobar |
> (4 rows)
>

If i understand correctly, you want something like:

create table x (
   name TEXT NOT NULL PRIMARY KEY,
   address INET
);

CREATE UNIQUE INDEX na ON x (name, address) WHERE address IS NULL;

HTH

Sebastian