The ISO SQL Standard does not even define an index, and so any index is an extension to the standard (though primary keys and foreign keys imply them). At least in the SQL Standard that I have (ANSI/ISO/IEC 9075-1-1999 and related documents) has no definition of an index. Perhaps the newer version contains such a definition. So, in the creation of an extension, I think it is up to the programmer to do whatever is best. -----Original Message----- From: Tom Lane [mailto:tgl@xxxxxxxxxxxxx] Sent: Wednesday, January 19, 2005 4:18 PM To: Dann Corbit Cc: vhikida@xxxxxxxxxxx; J. Greenlees; pgsql-general@xxxxxxxxxxxxxx Subject: Re: Unique Index "Dann Corbit" <DCorbit@xxxxxxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
