Skip site navigation (1) Skip section navigation (2)

Peripheral Links

Header And Logo

PostgreSQL
| The world's most advanced open source database.

Site Navigation

Search archives
  Advanced Search

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: Mon, 29 Jun 2009 15:42:09 +1000
  • Message-id: <4A485431.3080707@cs.anu.edu.au> <text/plain>

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.



Home | Main Index | Thread Index

Privacy Policy | About PostgreSQL
Copyright © 1996 – 2012 PostgreSQL Global Development Group