Stephane Bortzmeyer wrote:
On Wed, Apr 27, 2005 at 10:26:30AM -0400,
Tom Lane <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
