Re: How does PostgreSQL treat null values in unique composite - Mailing list pgsql-general

the sql paradigm says NULL is 'unknown'

So, you can have unique values in a column, but the unknown ones are ignored.
this can be very handy, but is best used only in date/date related fields
because NULLS complicate many SQL statements.

P G wrote:
> I have the following table with a unique constraint.
>
> create table testt (
> id int,
> aa int,
> bb int,
> constraint pk_testt primary key (id),
> constraint cons_testt unique (aa,bb));
>
> Let's say I insert the following:
>
> insert into testt values (1,2,null);
> insert into testt values (2,2,null);
>
> I thought this should fail because of the unique
> constraint, but it doesn't.  How does PostgreSQL view
> the null values in this constraint?  Will someone
> explain why I am allowed to make this insertion?
>
> TIA.
>
> __________________________________________________
> Do you Yahoo!?
> Yahoo! Tax Center - File online, calculators, forms, and more
> http://tax.yahoo.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>