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 01D7B9FB2F5 for ; Thu, 13 Jul 2006 11:41:02 -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 01629-02 for ; Thu, 13 Jul 2006 11:40:49 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wx-out-0102.google.com (wx-out-0102.google.com [66.249.82.201]) by postgresql.org (Postfix) with ESMTP id 53E929FB2DB for ; Thu, 13 Jul 2006 11:40:48 -0300 (ADT) Received: by wx-out-0102.google.com with SMTP id h26so114686wxd for ; Thu, 13 Jul 2006 07:40:44 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:mime-version:content-type; b=S5iUrUYV8JQH3gPAKVrTA2lX27WoR3zCxr8KQOwsSrTJk10i28e9QDn7DlS9vCbWi16GAT0eJvZkG8uZl89ZNtWl4YMuDOY1r2pv325V5li50iPEAA0dAJSNjPSaVEJU9VXjOzhWD3o1HlKF2fxU4/lESeSkrwZUwVK+Ynx/oy8= Received: by 10.70.84.9 with SMTP id h9mr1351599wxb; Thu, 13 Jul 2006 07:40:44 -0700 (PDT) Received: by 10.70.73.15 with HTTP; Thu, 13 Jul 2006 07:40:43 -0700 (PDT) Message-ID: <1d219a6f0607130740g7fe13967t48c77a64fde977aa@mail.gmail.com> Date: Thu, 13 Jul 2006 10:40:43 -0400 From: "Chris Hoover" To: "pgsql-admin@postgresql.org" Subject: 8.1 Unique Index Issue/Bug??? MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_10557_17090930.1152801643433" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/87 X-Sequence-Number: 22333 ------=_Part_10557_17090930.1152801643433 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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. Why is PostgreSQL not enforcing this index? This appears to be a pretty major a bug? It would seem that you could have a unique index across columns that might have a null in them. Here is the data from the table: COPY payer_835 (payer_id, payer_name, payer_trn03, payer_trn04, sku_id, payer_billable, create_timestamp, last_mod_timestamp, expire_timestamp) FROM stdin; 1 CAHABA GBA-AL PART B 1630103830 \N 1 t 2006-07-13 09:57:52.834631 \N \N 2 FEP 123456789 00402 1 t 2006-07-10 10:56:23 \N \N 3 NC Medicaid 123123123 \N 1 t 2006-07-10 10:56:41 \N \N 4 CAHABA GBA-AL PART B 1630103830 \N 1 t 2006-07-11 16:13:43.808181 2006-07-12 10:09:46.066204 \N \. Notice records 1 and 4 have identical data (as far as my unique index is concerned), and the index it not complaining. thanks, Chris RHAS 4.0 PG 8.1.3 (from rpms) ------=_Part_10557_17090930.1152801643433 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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.

Why is PostgreSQL not enforcing this index? This appears to be a pretty major a bug?  It would seem that you could have a unique index across columns that might have a null in them.

Here is the data from the table:

COPY payer_835 (payer_id, payer_name, payer_trn03, payer_trn04, sku_id, payer_billable, create_timestamp, last_mod_timestamp, expire_timestamp) FROM stdin;
1       CAHABA GBA-AL PART B    1630103830      \N      1       t       2006-07-13 09:57: 52.834631      \N      \N
2       FEP     123456789       00402   1       t       2006-07-10 10:56:23     \N      \N
3       NC Medicaid     123123123       \N      1       t       2006-07-10 10:56:41     \N      \N
4       CAHABA GBA-AL PART B    1630103830      \N      1       t       2006-07-11 16:13:43.808181      2006-07-12 10:09:46.066204      \N
\.


Notice records 1 and 4 have identical data (as far as my unique index is concerned), and the index it not complaining.

thanks,

Chris
RHAS 4.0
PG 8.1.3 (from rpms)
------=_Part_10557_17090930.1152801643433--