How does PostgreSQL treat null values in unique composite constraints???

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:

think of NULL as a placeholder for a future value.

Dennis Gearon wrote:

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?"