From: Robert Edwards <bob@cs.anu.edu.au>
To: pgsql-sql@postgresql.org
Subject: uniqueness constraint with NULLs
Date: Mon, 29 Jun 2009 15:42:09 +1000
Message-ID: <4A485431.3080707@cs.anu.edu.au> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bob@cs.anu.edu.au
Subject: Re: uniqueness constraint with NULLs
In-Reply-To: <4A485431.3080707@cs.anu.edu.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox