unique index on fields with possible null values

From: Dmitry Ruban <dmitry(at)ruban(dot)biz>
To: pgsql-sql(at)postgresql(dot)org
Subject: unique index on fields with possible null values
Date: 2007-07-27 00:59:38
Message-ID: 248746715.20070727125938@ruban.biz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Paul Lambert 2007-07-27 01:33:20 Join question
Previous Message Harald Armin Massa 2007-07-26 15:17:03 Re: Tunning PostgreSQL performance for views on Windows