Обсуждение: 8.1 Unique Index Issue/Bug???

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)

On Thu, 2006-07-13 at 09:40, 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.
>
> 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.

But you have NULL expire_timestamps, and NULL <> NULL

On Thu, Jul 13, 2006 at 10:40:43AM -0400, Chris Hoover wrote:
> 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.

This comes up frequently due to a misunderstanding of how NULL
comparison works.

http://www.postgresql.org/docs/8.1/interactive/ddl-constraints.html#AEN2016

"In general, a unique constraint is violated when there are two or
more rows in the table where the values of all of the columns
included in the constraint are equal.  However, null values are not
considered equal in this comparison.  That means even in the presence
of a unique constraint it is possible to store duplicate rows that
contain a null value in at least one of the constrained columns.
This behavior conforms to the SQL standard, but we have heard that
other SQL databases may not follow this rule.  So be careful when
developing applications that are intended to be portable."

--
Michael Fuhr

On 7/13/06, Chris Hoover <revoohc@gmail.com> wrote:
==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
==================================================================

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, "<comparison predicate>", then
the result of the <unique predicate> is true; otherwise, the result of the
<unique predicate> 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.

Am Donnerstag, 13. Juli 2006 16:40 schrieb Chris Hoover:
> 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.

This behavior is correct according to the SQL standard, as two null values are
not considered equal.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

PostgreSQL does not consider a NULL value to be an equal value,
therefore it cannot be a duplicate.

If you must have a blank value in a column and need it to be unique,
you'd need to do something like an empty string, or a string/figure your
application will know is a null value that makes sense.

It's also the same on multi-column indexes - if one column is NULL-able,
the index won't be enforced against the null values.

 From http://www.postgresql.org/docs/8.1/static/indexes-unique.html:

"When an index is declared unique, multiple table rows with equal
indexed values will not be allowed. Null values are not considered
equal. A multicolumn unique index will only reject cases where all of
the indexed columns are equal in two rows."

Andy.

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.
>
> 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)
> !DSPAM:14,44b66a2a34531616211146!

On 7/13/06, Chris Hoover <revoohc@gmail.com> wrote:
> 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.

From:

http://www.postgresql.org/docs/8.1/interactive/indexes-unique.html

Quote:

"When an index is declared unique, multiple table rows with equal
indexed values will not be allowed. Null values are not considered
equal. A multicolumn unique index will only reject cases where all of
the indexed columns are equal in two rows."

Regards,

Rodrigo

>> 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.
>
> From:
>
> http://www.postgresql.org/docs/8.1/interactive/indexes-unique.html
>
> Quote:
>
> "When an index is declared unique, multiple table rows with equal
> indexed values will not be allowed. Null values are not considered
> equal. A multicolumn unique index will only reject cases where all of
> the indexed columns are equal in two rows."

To further this... in PostgreSQL:

IS NULL != ''

'' is a blank string and thus an actual value where IS NULL is nothing,
vapor, a blackhole.

That is why IS NULL can be placed in a unique index because it isn't
actually there (in the theorectical sense).

Sincerely,

Joshua D. Drake



>
> Regards,
>
> Rodrigo
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>


--

    === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
    Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

jd@commandprompt.com ("Joshua D. Drake") writes:
> That is why IS NULL can be placed in a unique index because it isn't
> actually there (in the theorectical sense).

In effect, NULL is not actually a value.
--
(reverse (concatenate 'string "gro.mca" "@" "enworbbc"))
http://www.ntlug.org/~cbbrowne/sap.html
"Has anyone ever thought about the fact that in general, the only web
sites that are consistently making money are the ones dealing in
pornography?  This brings new meaning to the term, "obscene
profits". :)"  -- Paul Robinson <postmaster@paul.washington.dc.us>