Skip site navigation (1) Skip section navigation (2)

How to create unique constraint on NULL columns

From: "Andrus" <eetasoft(at)online(dot)ee>
To: pgsql-general(at)postgresql(dot)org
Subject: How to create unique constraint on NULL columns
Date: 2005-07-15 10:46:15
Message-ID: db8464$cgq$1@news.hub.org (view raw or flat)
Thread:
Lists: pgsql-general
I have table

CREATE TABLE test( col1 CHAR NOT NULL, col2 CHAR,
  UNIQUE (col1, col2) );

This table allows to insert duplicate rows if col2 is NULL:

INSERT INTO test VALUES ( '1', NULL );
INSERT INTO test VALUES ( '1', NULL );

does NOT cause error!

How to create constraint so that NULL values are treated equal and second 
insert is rejected ?

Andrus. 



Responses

pgsql-general by date

Next:From: Kevin MurphyDate: 2005-07-15 10:57:55
Subject: Re: What's Popular for CMS and RAD with PHP/PostgreSQL?
Previous:From: Richard HuxtonDate: 2005-07-15 09:11:13
Subject: Re: Function returning any (tuple) type

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group