Re: Unique Index

On Thu, 20 Jan 2005 15:20:26 +1100, Alex <alex(at)meerkatsoft(dot)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