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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-novice by date

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