On Thu, 20 Jan 2005 15:20:26 +1100, Alex <alex@meerkatsoft.com> wrote:
> I actually just wanted to know if there is a way around this problem.
> Obviously it is implemented that way for whatever reason.
Well, if you really need it, partial indexes are your friends! :)
For clarity, let's say you have:
CREATE TABLE foo (
a int,
b int,
c int,
);
And an INDEX:
CREATE UNIQUE INDEX foo_abc_index ON foo (a,b,c);
Now, you want to make sure a and b are UNIQUE, when c is null; just do:
CREATE UNIQUE INDEX foo_abN_index ON foo (a,b) WHERE c IS NULL;
Or even, to make b UNIQUE when a and c are null:
CREATE UNIQUE INDEX foo_NbN_index ON foo (b) WHERE a IS NULL AND c IS NULL;
You need to create such partial indexes for each set of columns
you want to be unique-with-null.
Don't worry about "index bloat". These additional indexes will be used
only when your main (foo_abc_index) is not used, so there won't be
any duplicate data in them.
Isn't PostgreSQL great? :)
Regards,
Dawid