Unique constraint over null values

From: Thrasher <thrasher(at)fibers(dot)upc(dot)es>
To: pgsql-sql(at)postgresql(dot)org
Subject: Unique constraint over null values
Date: 2002-09-30 17:22:23
Message-ID: 3D98884F.9070704@fibers.upc.es
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hi all,

I have a table like

CREATE TABLE a (
type CHAR (1) NOT NULL,
data1 CHAR (16) NOT NULL,
data2 CHAR (16) NULL
);

where type can be 's' for 'single' and 'x' for extended, so s should
mean that there is only the type and data1 field, and x means that all
fields are set.

How can I set a unique constraint like CHECK (type = 's' AND UNIQUE
(type, data1)) OR (type = 'x' AND UNIQUE (type, data1, data2)) ?

The documentation says that 2 NULL values are different, so no unique
constraint can be set over it.

Thanks in advance,

Thrasher

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Mike Sosteric 2002-09-30 17:24:19 Re: [GENERAL] arrays
Previous Message Josh Berkus 2002-09-30 17:18:48 Re: [GENERAL] arrays