From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-admin@postgresql.org
Subject: Re: unique constraints
Date: Fri, 16 Dec 2016 17:37:57 +0200
Message-ID: <58540A55.3000507@matrix.gatewaynet.com> (raw)
In-Reply-To: <B75CD08C73BD3543B97E4EF3964B7D70479DACB2@CITESMBX1.ad.uillinois.edu>
References: <B75CD08C73BD3543B97E4EF3964B7D70479DACB2@CITESMBX1.ad.uillinois.edu>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: achill@matrix.gatewaynet.com
Subject: Re: unique constraints
In-Reply-To: <58540A55.3000507@matrix.gatewaynet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox