Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id E8905475FFF
	for <pgsql-sql@postgresql.org>; Mon, 30 Sep 2002 13:23:07 -0400 (EDT)
Received: from egwn.net (ns2.egwn.net [193.172.5.4])
	by postgresql.org (Postfix) with ESMTP id AB1334762D4
	for <pgsql-sql@postgresql.org>; Mon, 30 Sep 2002 13:23:00 -0400 (EDT)
Received: from fibers.upc.es (gw01.es3.egwn.net [212.9.66.13]) (authenticated)
	by egwn.net (8.11.6/8.11.6/EGWN) with ESMTP id g8UHMNf05080
	for <pgsql-sql@postgresql.org>; Mon, 30 Sep 2002 19:22:23 +0200
Message-ID: <3D98884F.9070704@fibers.upc.es>
Date: Mon, 30 Sep 2002 19:22:23 +0200
From: Thrasher <thrasher@fibers.upc.es>
Organization: =?ISO-8859-15?Q?Delegaci=F3_Alumnes_FIB?=
User-Agent: Mozilla/5.0 (Windows; U; Win98; en-US; rv:1.1b) Gecko/20020721
X-Accept-Language: es-es, en-us
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Unique constraint over null values
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-MailScanner: Found to be clean
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200209/427
X-Sequence-Number: 9704

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