October 11th, 2013, 07:50 AM
UNIQUE NULL rows
Hey guys. I have a big problem. I have this table:
So I want the triple (a, b, c) to be unique. This does not work if c is NULL though.
CREATE TABLE foo
id serial NOT NULL,
a integer NOT NULL,
b integer NOT NULL,
CONSTRAINT foo_pkey PRIMARY KEY (id),
CONSTRAINT foo_a_b_c_key UNIQUE (a, b, c)
For example, this works:
Does anyone know a way to solve this problem without assigning c a value (like coalesce, because I want to distinguish between an empty value and NULL)?
id a b c
1 1 2 NULL
2 1 2 NULL
What I mean with the last sentence is this:
This works, but that way i can't distinguish between NULL and an empty string.
create unique index idx_notnull on foo (a, b, coalesce(c, ''));
Thanks for your help.
October 12th, 2013, 05:14 AM
NULL is never equal to anything.
If you want to make a and b unique when C is NULL, then you can do exactly that: create two partial unique constraints, one for WHERE c IS NULL and one for WHERE C IS NOT NULL.
October 12th, 2013, 08:32 AM
Thanks Vinny42. That was exactly what I was looking for