Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9K3q4P61690 for ; Fri, 19 Oct 2001 23:52:04 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id f9K3pbc11622; Fri, 19 Oct 2001 23:51:37 -0400 (EDT) To: "David Allardyce" cc: pgsql-sql@postgresql.org Subject: Re: Table Constraints with NULL values In-reply-to: <045b01c1590c$f3751220$6100a8c0@pod13.com> References: <026601c15849$b4f38680$6100a8c0@pod13.com> <11522.1003474419@sss.pgh.pa.us> <045b01c1590c$f3751220$6100a8c0@pod13.com> Comments: In-reply-to "David Allardyce" message dated "Fri, 19 Oct 2001 22:14:29 -0400" Date: Fri, 19 Oct 2001 23:51:37 -0400 Message-ID: <11619.1003549897@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200110/288 X-Sequence-Number: 5241 "David Allardyce" writes: > ... If they had defined uniqueness as "all rows > must be distinct" then two all null rows would violate the uniqueness > constraint. Not the behavior I want at all. Er, why not? You're essentially arguing that the UNIQUE constraint should treat nulls as equal, so I'd think two all-null rows should be considered equal too. I do not see how you can consistently maintain that (x,NULL) equals (x,NULL) but (NULL,NULL) doesn't equal (NULL,NULL). But we have been around the maypole on this question several times before, with no permanent resolution --- the plain fact is that the spec isn't very clearly written. Useful data would be tests demonstrating how other systems (Oracle, DB2, etc) interpret the issue. regards, tom lane