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
On Tue, 8 Apr 2003, 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? Two NULL values still satisfy a unique constraint. A unique constraint is defined in terms of the unique predicate. From the unique predicate: "If there are no two rows T such that the value of each column in one row is non-null and is equal to the value of the corresponding column in the other row according to Subclause 8.2, "<comparison predicate>", then the result of the <unique predicate> is true; otherwise the result of the <unique predicate> is false".
I suppose null is 'absence of value' rather than any value (like an empty string) that can be compared with any other value for uniqueness. Mike. > -----Original Message----- > From: pgsql-admin-owner@postgresql.org > [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of P G > Sent: Tuesday, April 08, 2003 2:14 PM > To: pgsql-admin@postgresql.org > Subject: [ADMIN] 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 > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
Where can I find the documentation cited for the "unique predicate" mentioned below? TIA. --- Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote: > > On Tue, 8 Apr 2003, 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? > > Two NULL values still satisfy a unique constraint. > A unique constraint is > defined in terms of the unique predicate. > > From the unique predicate: > "If there are no two rows T such that the value of > each column in one row > is non-null and is equal to the value of the > corresponding column in the > other row according to Subclause 8.2, "<comparison > predicate>", then the > result of the <unique predicate> is true; otherwise > the result of the > <unique predicate> is false". > > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: you can get off all lists at once with the > unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) __________________________________________________ Do you Yahoo!? Yahoo! Tax Center - File online, calculators, forms, and more http://tax.yahoo.com
Also, the "unique predicate" only explains what it means to be unique, but it says nothing about how null values are treated. Will someone explain what the value of null is? TIA. --- P G <pg_dba@yahoo.com> wrote: > Where can I find the documentation cited for the > "unique predicate" mentioned below? > > TIA. > > --- Stephan Szabo <sszabo@megazone23.bigpanda.com> > wrote: > > > > On Tue, 8 Apr 2003, 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? > > > > Two NULL values still satisfy a unique constraint. > > > A unique constraint is > > defined in terms of the unique predicate. > > > > From the unique predicate: > > "If there are no two rows T such that the value of > > each column in one row > > is non-null and is equal to the value of the > > corresponding column in the > > other row according to Subclause 8.2, "<comparison > > predicate>", then the > > result of the <unique predicate> is true; > otherwise > > the result of the > > <unique predicate> is false". > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 2: you can get off all lists at once with the > > unregister command > > (send "unregister YourEmailAddressHere" to > majordomo@postgresql.org) > > > __________________________________________________ > Do you Yahoo!? > Yahoo! Tax Center - File online, calculators, forms, > and more > http://tax.yahoo.com > > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster __________________________________________________ Do you Yahoo!? Yahoo! Tax Center - File online, calculators, forms, and more http://tax.yahoo.com
On Tue, 8 Apr 2003, P G wrote: > > Where can I find the documentation cited for the > > "unique predicate" mentioned below? That's the SQL92 draft, although I believe the SQL99 text is effectively the same. The sections are 11.7 (for the definition of the constraint in terms of the predicate) and 8.9 for the predicate itself (General Rule 2 specifically) > Also, the "unique predicate" only explains what it > means to be unique, but it says nothing about how null > values are treated. Will someone explain what the > value of null is? It isn't a precise value. It's a holder for unknown. It's unknown whether it's equal or not equal to any other value (including another NULL), so 1 = NULL returns unknown, NULL=NULL returns unknown, 1!=NULL returns unknown.
There's a good run down of NULL's at http://techdocs.postgresql.org/guides/BriefGuideToNulls Robert Treat On Tue, 2003-04-08 at 15:48, P G wrote: > Also, the "unique predicate" only explains what it > means to be unique, but it says nothing about how null > values are treated. Will someone explain what the > value of null is? >