From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Dann Corbit" <DCorbit(at)connx(dot)com> |
Cc: | vhikida(at)inreach(dot)com, "J(dot) Greenlees" <jaqui(at)telus(dot)net>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Unique Index |
Date: | 2005-01-20 00:18:27 |
Message-ID: | 868.1106180307@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
"Dann Corbit" <DCorbit(at)connx(dot)com> writes:
> Or (perhaps better yet, violating trichotomy) ...
> If <Some_column> has a null numeric value, then ALL of the following are
> FALSE for that case:
> Some_column < 0
> Some_column > 0
> Some_column = 0
> Some_column <> 0 // This is the one that many find surprising
> Some_column <= 0
> Some_column >= 0
It's worse than that: the above do *not* yield FALSE, they yield NULL.
Which does act like FALSE in a simple WHERE clause, but there are other
cases (like CHECK clauses) where it doesn't. "x NOT IN (SELECT ...)"
is a case that newbies routinely get bitten by.
> Even at that, I think that being able to insert more than one null value
> into a unique index should be considered as a bug (or diagnosed as an
> error).
Direct your complaints to the ISO SQL standards committee.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Dann Corbit | 2005-01-20 00:23:09 | Re: Unique Index |
Previous Message | Frank D. Engel, Jr. | 2005-01-20 00:11:53 | Re: sorting library of congress numbers |