I have a table with a uniqueness constraint on three columns:
# \d bobtest Table "public.bobtest" Column | Type | Modifiers
--------+---------+------------------------------------------------------ id | integer | not null default
nextval('bobtest_id_seq'::regclass)a | integer | b | integer | c | integer |
Indexes: "bobtest_id_key" UNIQUE, btree (id) "bobtest_unique" UNIQUE, btree (a, b, c)
I can insert multiple rows with identical a and b when c is NULL:
...
# insert into bobtest (a, b) values (1, 4);
INSERT 0 1
# insert into bobtest (a, b, c) values (1, 4, NULL);
INSERT 0 1
# select * from bobtest; id | a | b | c
----+---+---+--- 1 | 1 | 2 | 1 2 | 1 | 3 | 1 3 | 1 | 4 | 1 4 | 1 | 4 | 5 | 1 | 4 | 6 | 1 | 4 |
(6 rows)
Can anyone suggest a way that I can impose uniqueness on a and b when
c is NULL?
In the real app., c is a date field and I require it to be NULL for
some rows. In these cases, I only want at most one row with identical
a and b, but I can have identical a and b when c is a real date as long
as that date is also unique for a given a and b.
I'm guessing I'm going to need to use a function and that someone will
yell at me for using NULLs to represent real data, but I thought I'd be
brave and ask anyway, in case I am missing some other solution that
doesn't involve the use of triggers etc.
Cheers,
Bob Edwards.
