uniqueness constraint with NULLs

From: Robert Edwards <bob(at)cs(dot)anu(dot)edu(dot)au>
To: pgsql-sql(at)postgresql(dot)org
Subject: uniqueness constraint with NULLs
Date: 2009-06-29 05:42:09
Message-ID: 4A485431.3080707@cs.anu.edu.au
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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.

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message A. Kretschmer 2009-06-29 06:26:02 Re: uniqueness constraint with NULLs
Previous Message Shane Ambler 2009-06-27 15:34:45 Re: .psql_history": No such file