Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cHubw-0008Rj-PQ for pgsql-admin@arkaria.postgresql.org; Fri, 16 Dec 2016 15:39:52 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cHubw-0002WV-CB for pgsql-admin@arkaria.postgresql.org; Fri, 16 Dec 2016 15:39:52 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cHuaD-00066B-IM for pgsql-admin@postgresql.org; Fri, 16 Dec 2016 15:38:05 +0000 Received: from mail-pf0-x22e.google.com ([2607:f8b0:400e:c00::22e]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1cHua9-0005kH-W8 for pgsql-admin@postgresql.org; Fri, 16 Dec 2016 15:38:04 +0000 Received: by mail-pf0-x22e.google.com with SMTP id i88so14879612pfk.2 for ; Fri, 16 Dec 2016 07:38:01 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:to:references:in-reply-to:subject:date:message-id:mime-version :content-language:thread-index; bh=rT4DbDAAtRnQl0NIXakYl/hocqGEMaCoWxLgstCcNcM=; b=QFzrZLKBPjC0MNlva3AKNcFGff1e74/HDn5N3H5BJjVBAp69+GVm0sVIRGLzMN102L R97wjEvq3GPZhBTC6EmfuTSzZrjWPKcx1BCzsU6zQXRaSlUw5iNPUMlhbpmI5xzOepIS fuv3Td50fmXOBlEZWhRH5Am9JmlaibcQH7p95RG4PEQRYkrQSJRTTt044aQqrW7CxpZ0 QRpGr5+gBxdmsEs2ikv/sSwxAfByPqxmYgreVn0FsKE/SY5xFtj0FoF3djcxDjgFjH/F DQqSR+QOjGjh6ikYEeKsoU3e8VgUiGv4rHE43mODMIPDu1VayYrs+QO2mkp1q1X7v+p+ WtEQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:to:references:in-reply-to:subject:date :message-id:mime-version:content-language:thread-index; bh=rT4DbDAAtRnQl0NIXakYl/hocqGEMaCoWxLgstCcNcM=; b=c8gDuG5R1eiDbELn69IInA+mfsGCAH7Y1Y2kJVhrM5pyZbcPi1sIw2KFvIk5p5YOWg McvQuXAYyKz+kNtNm5m8savXi1cTUPVYhnseYkdZOdoQlssvJDFwE+85VpWB63GzZhks 4JkqvLfuBJ2jetg+ddtcEPuTn8BeY6KE2QLr7tR8yd4SGWj/IhGO1n4xzApXLCwIjBiO Cm8n/+vYqKVaaZxLKpeuGFQfGMZyFphu76sLfO7HJNvl/kmrEnUz0U0hx24U7REWlbDO Dd1ga8mk1mol9shS7X0cLv/50a3ly36XFLUsM3anFApvNK5h/IvFZXcD/KYem5KH+dPI 0WBw== X-Gm-Message-State: AKaTC03yycYmQ5calzOzXlrb34zc5ZyAsUhcJQNdWYQN+ZQUOuwLj2xA4tIyxhN5mMHmzg== X-Received: by 10.99.143.90 with SMTP id r26mr6665606pgn.164.1481902679997; Fri, 16 Dec 2016 07:37:59 -0800 (PST) Received: from RonPC (wsip-72-214-222-226.ph.ph.cox.net. [72.214.222.226]) by smtp.gmail.com with ESMTPSA id s2sm12879982pfi.10.2016.12.16.07.37.59 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Fri, 16 Dec 2016 07:37:59 -0800 (PST) From: "Ron Watkins" To: "'Campbell, Lance'" , References: In-Reply-To: Subject: Re: unique constraints Date: Fri, 16 Dec 2016 08:37:49 -0700 Message-ID: <1c6e01d257b2$5c201f90$14605eb0$@gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_1C6F_01D25777.AFC2A720" X-Mailer: Microsoft Outlook 14.0 Content-Language: en-us Thread-Index: AQIA8BpPuIl/xBKY59njv9JIEMi6F6CthGjg X-Pg-Spam-Score: -2.7 (--) 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 multipart message in MIME format. ------=_NextPart_000_1C6F_01D25777.AFC2A720 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Because NULL != NULL, therefore you can't have a unique value for NULL. From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Campbell, Lance Sent: Friday, December 16, 2016 8:30 AM To: pgsql-admin@postgresql.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 ------=_NextPart_000_1C6F_01D25777.AFC2A720 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Because NULL !=3D NULL, = therefore you can’t have a unique value for = NULL.

 

From:= = pgsql-admin-owner@postgresql.org = [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Campbell, = Lance
Sent: Friday, December 16, 2016 8:30 AM
To: = pgsql-admin@postgresql.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=3DFALSE

);

 

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

 

------=_NextPart_000_1C6F_01D25777.AFC2A720--