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

Unique indices and nulls

From: Roland Roberts <roland(at)astrofoto(dot)org>
To: pgsql-novice(at)postgresql(dot)org
Subject: Unique indices and nulls
Date: 2002-09-10 20:40:44
Message-ID: m2y9a9zigj.fsf@kuiper.rlent.pnet (view raw or flat)
Thread:
Lists: pgsql-novice
I see in the documentation that a unique index does not place any
constraints on the number of nulls in a table.  In my case, I have a
table with has four columns (plus other data) where that combination
of 4 columns should be unique.

    (catalog, entry, suffix, component)

forms a unique tuple.  In most (but not all) cases, (catalog, entry)
is unique and suffix and component will both be null.  In those cases,
it is common to have an entry with (catalog, entry, null, null), as
well as multiple entries with (catalog, entry, suffix, component).

But there should never be more than one entry with (catalog, entry,
null, null).

Is there any way I can enforce this?  Am I going to have to write a
trigger to check for duplicates?

roland
-- 
		       PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD                             RL Enterprises
roland(at)rlenter(dot)com                     76-15 113th Street, Apt 3B
roland(at)astrofoto(dot)org                       Forest Hills, NY 11375

Responses

pgsql-novice by date

Next:From: Terry YaptDate: 2002-09-10 22:38:50
Subject: Re: pl/pgsql and returns timestamp type
Previous:From: Tom LaneDate: 2002-09-10 19:27:29
Subject: Re: pl/pgsql and returns timestamp type

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