UNIQUE, btree index allows duplicate records, if some fields are null

Поиск
Список
Период
Сортировка
От Bryce Nesbitt
Тема UNIQUE, btree index allows duplicate records, if some fields are null
Дата
Msg-id 43EB9A3B.2080507@obviously.com
обсуждение исходный текст
Ответы Re: UNIQUE, btree index allows duplicate records, if some fields are null  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-sql
This threw me for a loop.  Is this my fault, or a
problem in postgres?

I have a table with the following:   "eg_vehicle_event_pkey" PRIMARY KEY, btree (event_id)   "no_duplicate_events"
UNIQUE,btree (thing, other_thing,
 
"timestamp", number, other_number)

The "no_duplicate_events" constraint works fine, but if I insert records
where
"other_thing" is null, they all go in without complaint. I can insert as
many duplicates as I want.

I had expected two records, identical in all respects including the
null value, to be rejected by the constraint.

This is with:
PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
20030502 (Red Hat Linux 3.2.3-20)



В списке pgsql-sql по дате отправления:

Предыдущее
От: "Keith Worthington"
Дата:
Сообщение: Re: Change definition of a view
Следующее
От: Tom Lane
Дата:
Сообщение: Re: UNIQUE, btree index allows duplicate records, if some fields are null