Re: unique constraints

On 16/12/2016 17:30, Campbell, Lance wrote:
>
> 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.
>
Because null is not a normal value you can compare against. Null basically is equivalent to "do not know". I haven't caught up wit the latest SQL flavors of pgsql, but old school you would do this by
adding this :

CREATE UNIQUE INDEX test_id_uk ON test(id) WHERE (tags_id IS NULL);
>
> Thanks,
>
> Lance
>

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt