[SQL] uniqueness constraint with NULLs

[Robert Edwards]

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.

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] uniqueness constraint with NULLs

[A. Kretschmer]

In response to Robert Edwards :
> Can anyone suggest a way that I can impose uniqueness on a and b when
> c is NULL?

Sure, use a functional index:

test=# create table bobtest (a int, b int, c int);
CREATE TABLE
test=*# create unique index idx_bobtest on
bobtest(a,b,coalesce(c::text,'NULL'));
CREATE INDEX
test=*# insert into bobtest (a, b) values (1, 4);
INSERT 0 1
test=*# insert into bobtest (a, b, c) values (1, 4, NULL);
ERROR:  duplicate key value violates unique constraint "idx_bobtest"
test=!#


Regards, Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql