Re: Unique index with Null value in one field

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Hrishi Joshi <hjoshi(at)abcsinc(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Unique index with Null value in one field
Date: 2005-10-12 02:51:52
Message-ID: 25944.1129085512@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hrishi Joshi <hjoshi(at)abcsinc(dot)com> writes:
> I need to define a Unique index on 3 non-PK fields (composite key) on my
> table in PostgreSQL 8.0.3.

> The problem is, if any of those 3 fields is Null, PostgreSQL allows
> duplicate rows to be inserted.

That is the behavior defined by the SQL standard.

> But I need to know how can I make PostgreSQL throw error on attempt to
> insert second record having same 3 field values, one of them being Null.

You can't. Rethink your data representation, instead. You are misusing
NULL if you think that it represents something unique.

regards, tom lane

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2005-10-12 02:59:16 Re: [GENERAL] Oracle buys Innobase
Previous Message Aileen 2005-10-12 01:54:56 Xampp, PostgreSQL & phpPgAdmin