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