X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id C82579FB3FC for ; Thu, 13 Jul 2006 12:58:03 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 13481-05 for ; Thu, 13 Jul 2006 12:57:54 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by postgresql.org (Postfix) with ESMTP id C18DE9FB2DB for ; Thu, 13 Jul 2006 12:57:54 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 06E15352B3; Thu, 13 Jul 2006 08:57:54 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 04E18352B2; Thu, 13 Jul 2006 08:57:54 -0700 (PDT) Date: Thu, 13 Jul 2006 08:57:53 -0700 (PDT) From: Stephan Szabo To: Chris Hoover Cc: "pgsql-admin@postgresql.org" Subject: Re: 8.1 Unique Index Issue/Bug??? In-Reply-To: <1d219a6f0607130740g7fe13967t48c77a64fde977aa@mail.gmail.com> Message-ID: <20060713085157.H49004@megazone.bigpanda.com> References: <1d219a6f0607130740g7fe13967t48c77a64fde977aa@mail.gmail.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/93 X-Sequence-Number: 22339 On Thu, 13 Jul 2006, Chris Hoover wrote: > I am having what appears to be a bug with unique indexes on 8.1.3. > > I have created a new table. > > create table payer_835 ( > payer_id int8 not null default > nextval('payer_835_payer_id_seq'::regclass) primary key, > payer_name varchar(50) not null, > payer_trn03 varchar(10) not null, > payer_trn04 varchar(30), > sku_id int8 references skucode(sku_id) on delete cascade on > update cascade, > payer_billable boolean not null default true, > create_timestamp timestamp not null default now(), > last_mod_timestamp timestamp, > expire_timestamp timestamp > ); > > On this table, I have created a unique index on payer_trn03, payer_trn04, > and expire_timestamp. However, since the expire_timestamp is normally null, > the unique index does not appear to be working. I have been able to enter > two identical rows into this table. Two nulls are considered distinct for purposes of unique indexes and constraints. The unique constraint is defined effective as a search condition of UNIQUE ( SELECT UCL FROM TN ) (with UCL being the column list in the constraint). The definition from the UNIQUE predicate says (from sql92): "If there are no two rows in T such that the value of each column in one row is non-null as is equal to the value of the corresponding column in the other row according to Subclause 8.2, "", then the result of the is true; otherwise, the result of the is false." So, given something like 1, 2, NULL 1, 2, NULL the unique predicate is true and the data is allowed by the constraint.