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

Reply via email to