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.
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 |