Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cHuby-0008Rw-Mw for pgsql-admin@arkaria.postgresql.org; Fri, 16 Dec 2016 15:39:54 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cHuby-0002bC-9x for pgsql-admin@arkaria.postgresql.org; Fri, 16 Dec 2016 15:39:54 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cHuaE-00066N-Vk for pgsql-admin@postgresql.org; Fri, 16 Dec 2016 15:38:07 +0000 Received: from host3.dynacom.ondsl.gr ([62.103.35.211] helo=smadev.internal.net) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cHuaB-0005VK-Qb for pgsql-admin@postgresql.org; Fri, 16 Dec 2016 15:38:05 +0000 Received: from smadev.internal.net (smadev [10.9.200.131]) by smadev.internal.net (8.15.2/8.15.2) with ESMTP id uBGFbvQv014946 for ; Fri, 16 Dec 2016 17:37:58 +0200 (EET) (envelope-from achill@matrix.gatewaynet.com) Subject: Re: unique constraints To: pgsql-admin@postgresql.org References: From: Achilleas Mantzios Message-ID: <58540A55.3000507@matrix.gatewaynet.com> Date: Fri, 16 Dec 2016 17:37:57 +0200 User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:38.0) Gecko/20100101 Thunderbird/38.4.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------060203020200070800070109" X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org This is a multi-part message in MIME format. --------------060203020200070800070109 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit 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 --------------060203020200070800070109 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

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

--------------060203020200070800070109--