X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 1C544DB099 for ; Tue, 1 Nov 2005 13:32:07 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 84051-05 for ; Tue, 1 Nov 2005 17:32:04 +0000 (GMT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id 1A393DB6A7 for ; Tue, 1 Nov 2005 13:32:04 -0400 (AST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 4576035579; Tue, 1 Nov 2005 09:32:03 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 433F235577; Tue, 1 Nov 2005 09:32:03 -0800 (PST) Date: Tue, 1 Nov 2005 09:32:02 -0800 (PST) From: Stephan Szabo To: Diego Cc: pgsql-admin@postgresql.org Subject: Re: UNIQUE KEY with null value... In-Reply-To: <4367AF54.6010909@unimedijui.com.br> Message-ID: <20051101092644.Y59282@megazone.bigpanda.com> References: <4367AF54.6010909@unimedijui.com.br> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=ISO-8859-1 Content-Transfer-Encoding: QUOTED-PRINTABLE X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.006 required=5 tests=[AWL=0.006] X-Spam-Score: 0.006 X-Spam-Level: X-Archive-Number: 200511/6 X-Sequence-Number: 19379 On Tue, 1 Nov 2005, Diego wrote: > Hi, > I=B4m a brazilian programmer! > > I have a question about Postgres UNIQUE KEY. Look: > > (Step 1) > CREATE TABLE test ( > id integer not null primary key, > id_uni integer not null, > cod integer, > name varchar(40), > constraint test_uk unique(id_uni, cod) > ) > > (Step 2) > insert into test (id, id_uni, cod, name) > values (1, 1, null, 'A') > > (Step 3) > insert into test (id, id_uni, cod, name) > values (2, 1, null, 'B') =3D=3D=3D=3D=3D=3D=3D=3D=3D> [Have the same UNIQ= UE KEY] > > Why postgresql don=B4t break insertion in this case???? AFAICS, SQL (at least 92) says that those do not violate UNIQUE. The unique predicate on which the UNIQUE constraint is defined says: 2) If there are no two rows in T such that the value of each column in one row is non-null and is equal to the value of the cor- responding column in the other row according to Subclause 8.2, "", then the result of the is true; otherwise, the result of the is false. The unique constraint for the above is effectively UNIQUE (SELECT id_uni, col FROM test) The two rows in question look like (1,null) and (1,null). There are no rows in that would make the predicate return false AFAICS.