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 9282DDAEAD for ; Tue, 1 Nov 2005 19:43: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 40899-02 for ; Tue, 1 Nov 2005 23:43:03 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 357DEDACA1 for ; Tue, 1 Nov 2005 19:43:05 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.13.1/8.13.1) with ESMTP id jA1Nh2u6041266 for ; Tue, 1 Nov 2005 23:43:02 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.13.1/8.13.1/Submit) id jA1NMqVl032717 for pgsql-admin@postgresql.org; Tue, 1 Nov 2005 23:22:52 GMT (envelope-from news) From: Tzvetan Tzankov X-Newsgroups: pgsql.admin Subject: Re: UNIQUE KEY with null value... Date: Wed, 02 Nov 2005 01:22:55 +0200 Organization: Hub.Org Networking Services Lines: 50 Message-ID: References: <4367AF54.6010909@unimedijui.com.br> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit X-Complaints-To: usenet@news.hub.org User-Agent: Mozilla Thunderbird 1.0.7 (X11/20051013) X-Accept-Language: en-us, en In-Reply-To: <4367AF54.6010909@unimedijui.com.br> To: pgsql-admin@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=-2.13 required=5 tests=[ALL_TRUSTED=-1.44, AWL=-0.690] X-Spam-Score: -2.13 X-Spam-Level: X-Archive-Number: 200511/29 X-Sequence-Number: 19402 there are answers why it is that, here is how you can surround the problem you can create the unique index after you create the table, something like CREATE TABLE test ( id integer not null primary key, id_uni integer not null, cod integer, name varchar(40)); CREATE UNIQUE INDEX test_id_uni_cod ON test (id_uni, cod) WHERE cod IS NOT NULL; CREATE UNIQUE INDEX test_id_uni ON test (id_uni) WHERE cod IS NULL; 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 > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org >