Re: How does PostgreSQL treat null values in unique composite

Perhaps more exactly, an unknown value is not equal to any other unknown
value, and is therefore unique.

Dennis Gearon wrote:
> 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(at)postgresql(dot)org
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

--
/* =============================================================== */

William E. Colls Manager
Tel 613 225 6566 ext 2320 Applications Development Group
Fax 613 225 6651 CBN Lotteries
bcolls(at)cbnlottery(dot)com www.cbnco.com