unique index on fields with possible null values

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