Hi Tom,
Thanks for the explanation about standard sql.
The goodness of it must be accepted by faith. :-)
I still have a doubt about the result of the GROUP BY clause.
It seems to me that there's an inconsistence between the GROUP BY clause
and the unique index.
The GROUP BY clause, consider NULLs as known and equal values
while the index unique constraint consider NULLs as unknown values and
not equals between them.
Don't you think, there's an inconsistence here?
j
Tom Lane wrote:
jose.soa...@sferacarta.com writes:
I think I have found an error in pg or at least inconsistency, take a look
at this.
I created an unique index on two columns and pg let me enter repeated values
as NULLs (unknown value),
This is entirely correct per SQL standard: unique constraints do not
reject duplicated rows that include nulls. If you read the standard,
unique constraints are defined in terms of UNIQUE predicates, and a
UNIQUE predicate for a table T is defined thus:
2) If there are no two rows in T such that the value of each column
in one row is non-null and is equal to the value of the cor-
responding column in the other row according to Subclause 8.2,
"<comparison predicate>", then the result of the <unique predi-
cate> is true; otherwise, the result of the <unique predicate>
is false.
(SQL92 section 8.9 <unique predicate>)
This is why a primary key constraint is defined as requiring both UNIQUE
and NOT NULL; you need that to ensure that there are indeed no two
indistinguishable rows.
(Mind you, I'm not here to defend *why* the standard is written that
way. But that is what it says.)
Oracle don't allows to insert two NULLs in such column.
Oracle is not exactly the most standards-compliant implementation
around. They are well-known to be particularly wrong with respect to
NULLs behavior.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
