Message-ID: <4A485431.3080707@cs.anu.edu.au>
Date: Mon, 29 Jun 2009 15:42:09 +1000
From: Robert Edwards <bob@cs.anu.edu.au>
User-Agent: Thunderbird 2.0.0.18 (X11/20081125)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Subject: uniqueness constraint with NULLs

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.