Thread: How does PostgreSQL treat null values in unique composite constraints???
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
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 >
think of NULL as a placeholder for a future value. 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@postgresql.org >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
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@postgresql.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@cbnlottery.com www.cbnco.com
On Tue, Apr 08, 2003 at 11:46:41AM -0700, 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. Take note that there is no standard for how NULLs are handled in unique constraints, though. Some databases act like pgsql, some treat null as a single unique value (what you were expecting), and some disallow unique constraints on any columns that are nullable. BTW, I think pgsql's ability to index on a function would enable you to get the behavior you were expecting by indexing on something like coalesce(aa,'null'), coalesce(bb,'null') -- Jim C. Nasby (aka Decibel!) jim@nasby.net Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
On Tue, 2003-04-08 at 20:31, Jim C. Nasby wrote: > Take note that there is no standard for how NULLs are handled in unique > constraints, though. On the contrary, there *is* a standard for this behaviour (namely, the SQL standard). For example, section 4.18.2 of the SQL 200x draft standard says: "A unique constraint is satisfied if and only if no two rows in a table have the same non-null values in the _unique columns_." Some databases might not follow this part of the spec, but the standard is pretty clear, AFAICS. Cheers, Neil
On Tue, Apr 08, 2003 at 09:08:04PM -0400, Neil Conway wrote: > Some databases might not follow this part of the spec, but the standard > is pretty clear, AFAICS. Well, there's a lot of things in the SQL standard that many databases don't follow, this being one of them. :) It is good to see how much pgsql tries to follow the standard, though. -- Jim C. Nasby (aka Decibel!) jim@nasby.net Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"