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 EBEACDB6AB for ; Tue, 1 Nov 2005 13:28:33 -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 80395-09 for ; Tue, 1 Nov 2005 17:28:28 +0000 (GMT) Received: from www.goldencode.com (www.goldencode.com [72.17.138.114]) by svr1.postgresql.org (Postfix) with ESMTP id 9A197DB6CC for ; Tue, 1 Nov 2005 13:28:28 -0400 (AST) Received: from emlsrv01.internal.goldencode.com (emlsrv01.internal.goldencode.com [192.168.1.51]) by www.goldencode.com (Postfix) with SMTP id EEED7635 for ; Tue, 1 Nov 2005 12:29:23 -0500 (EST) Received: from goldencode.com (ecf.internal.goldencode.com [192.168.1.103]) by emlsrv01.internal.goldencode.com (Hethmon Brothers Smtpd) id 20051101132004-54183-7 ; Tue, 01 Nov 2005 13:20:04 -0500 Message-ID: <4367A5B2.9000508@goldencode.com> Date: Tue, 01 Nov 2005 12:28:18 -0500 From: Eric Faulhaber Organization: Golden Code Development User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.6) Gecko/20040114 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Diego Cc: pgsql-admin@postgresql.org Subject: Re: UNIQUE KEY with null value... References: <4367AF54.6010909@unimedijui.com.br> In-Reply-To: <4367AF54.6010909@unimedijui.com.br> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200511/5 X-Sequence-Number: 19378 Diego wrote: > Hi, > Iīm 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') =========> [Have the same UNIQUE KEY] > > Why postgresql donīt break insertion in this case???? > > Thanks > Diego Ziquinatti > Brazil Diego, In PostgreSQL, NULL is not considered a unique value, as it represents an "unknown" value. From the docs regarding unique constraints: "...null values are not considered equal in this comparison. That means even in the presence of a unique constraint it is possible to store an unlimited number of rows that contain a null value in at least one of the constrained columns." See: http://www.postgresql.org/docs/8.0/interactive/ddl-constraints.html#AEN1996 Regards, Eric Faulhaber