Hi list!
Consider the following table definition:
Column | Type | Modifiers
--------+------------------+-----------
id | integer | not null
date | date |
value | double precision |
The id and date field together are some sort of primary key. As you see,
the date field is nullable. For the entries, the following should be
ensured:
1. If a record with a given id and a null value in the date field
exists, no other record with the same id is allowed.
2. If multiple records with the same id exist, they must have
different values in the date field and none of them must have a
null value in this field.
How can I enforce these constraints?
Since primary keys must not contain nullable fields, I cannot define a
primary key. I tried to define two separate partial unique indices, one
for the records with a null value as date, one for those with a non-null
value:
create unique index idx1 on test (id) where date is null;
create unique index idx2 on test (id, date) where date is not null;
This ensures that at most one record with a given id and a null value as
date is possible, and that multiple records with the same id must have
different dates. However, it is still possible to insert one record
without a date and one or more records with dates, which violates my
above constraints.
My next idea was creating an own operator class which treats null values
as equal. For example, my special comparison operator =* would have the
following behaviour:
'2007-01-01'::date =* '2007-01-01'::date -> true
'2007-01-01'::date =* '2007-01-02'::date -> false
'2007-01-01'::date =* null -> true (!)
null =* '2007-01-01'::date -> true (!)
null =* null -> true (!)
If these operators would be used when checking for uniqueness, the
records with a null date would always be equal to any record with a
non-null date; thus, it would not be allowed to insert more than one
record with the same id unless they had different non-null dates.
Unfortunately, this doesn't work. :-( I assume that the date column is
never used at all so that my comparison operator is never asked. So what
can I do to make this work?
I hope someone has a solution for me. Many thanks in advance!
Christian
P.S.: I'm using PostgreSQL 8.2.3
--
Deriva GmbH Tel.: +49 551 489500-42
Financial IT and Consulting Fax: +49 551 489500-91
Hans-Böckler-Straße 2 http://www.deriva.de
D-37079 Göttingen
Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer
