Re: unique constraints

Because NULL != NULL, therefore you can't have a unique value for NULL.

From: pgsql-admin-owner(at)postgresql(dot)org
[mailto:pgsql-admin-owner(at)postgresql(dot)org] On Behalf Of Campbell, Lance
Sent: Friday, December 16, 2016 8:30 AM
To: pgsql-admin(at)postgresql(dot)org
Subject: [ADMIN] unique constraints

PostgreSQL 9.5

I have a table:

CREATE TABLE test

(

id integer NOT NULL,

tag_id integer,

CONSTRAINT test_id_tag_id_key UNIQUE (id, tag_id)

)

WITH (

OIDS=FALSE

);

I noticed I can insert this into the database:

Insert into test (id, tag_id) values(1,null);

Insert into test (id, tag_id) values(1,null);

But this does not work:

Insert into test (id, tag_id) values(2,1);

Insert into test (id, tag_id) values(2,1); (error occurs)

Select * from test;

1, null

1, null

2, 1

I understand why in the second set of inserts I get an error when trying to
insert the same set of values. But why does the first example work? I
would have thought that it would have only allowed the first insert to work.

Thanks,

Lance