unique index on fields with possible null values

[Dmitry Ruban <dmitry(at)ruban(dot)biz>]

Hello pgsql-sql,

I'm trying to find a solution for unique index on fields with possible
null values.

Example table:

CREATE TABLE test (
 a integer NOT NULL,
 b integer NULL
);

As long as unique index can't check if there are records with null
values i found the only one solution for this problem:

CREATE UNIQUE INDEX test_uniq1 ON test (a, COALESCE(b,0));

Are there any other ways of doing this?

-- 
Best regards,
 Dmitry                          mailto:dmitry(at)ruban(dot)biz