Re: Unique Index

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