Re: Unique Index - Mailing list pgsql-general

From Greg Stark
Subject Re: Unique Index
Date
Msg-id 87d5w0drpv.fsf@stark.xeocode.com
Whole thread Raw
In response to Re: Unique Index  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Unique Index
List pgsql-general
Tom Lane <tgl@sss.pgh.pa.us> writes:

> Greg Stark <gsstark@mit.edu> writes:
> > Tom Lane <tgl@sss.pgh.pa.us> writes:
> >> Direct your complaints to the ISO SQL standards committee.
>
> > The SQL standard generally treats NULLs as a escape hatch for constraints.

Huh? I thought I was agreeing with you. By "escape hatch" I meant that having
a NULL value in the indexed column allowed multiple records with otherwise
identical values because the NULL compared unequal. Ie, that the unique index
doesn't apply to the record with NULL columns.


> Not for UNIQUE constraints.  SQL92 section 4.10 "Integrity constraints":
>
>          A unique constraint is satisfied if and only if no two rows in
>          a table have the same non-null values in the unique columns.

That's ambiguous. Does it mean no two rows have all non-null columns that are
all identical? Or does it mean no two rows have columns that excluding any
null columns are identical.

It actually sounds more like the latter to me which would mean Postgres's
interpretation is wrong.


> The short answer to this thread is that the OP is misusing nulls,
> and should pick some non-null value to be his "placeholder".

We're in "violent agreement".


--
greg

pgsql-general by date:

Previous
From: troyston campano
Date:
Subject: Re: [ADMIN] Oracle and Postgresql Play Nice Together on Same Computer?
Next
From: Greg Stark
Date:
Subject: Re: Unique Index