pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

NULLL comparison in multiple column unique index
2+ messages / 2 participants
[nested] [flat]

* NULLL comparison in multiple column unique index
@ 2003-01-02 23:19  Brian Walker <brianw@mcsdallas.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Brian Walker @ 2003-01-02 23:19 UTC (permalink / raw)
  To: pgsql-sql

I have an application currently running under MSSQL and I am in the process of porting it to PostgreSQL.

I am having a problem with differences in MSSQL and PostgreSQL in relation to NULL columns with a multiple column unique index.

Here is my test script.

create table test1 (name varchar(64),num1 int,num2 int);
create unique index idx1 on test1(name,num1);
insert into idx1 values('row1',10,20);
insert into idx1 values('row2',11,21);

So "insert into idx1 values ('row2',11,50)" will fail which is what I want.


My problem is that sometimes the value of "num1" will be NULL for a column.  This is because in my application these are foreign keys into another table.  So if I do this:

insert into idx1 values ('row3',null,22);

then I do this again:

insert into idx1 values ('row3',null,23);

This is allowed to happen.  In Microsoft SQL the second insert will fail because of the unique index.  This looks like in MSSQL for the unique index checks that NULL is equal to NULL so the unique check fails.  In PostgreSQL NULL != NULL so the unique check passes because even though the name is the same the "num1" field is different..

Does anybody have any ideas on how I can work around this difference?

Thanks





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: NULLL comparison in multiple column unique index
@ 2003-01-03 13:10  Manfred Koizar <mkoi-pg@aon.at>
  parent: Brian Walker <brianw@mcsdallas.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Manfred Koizar @ 2003-01-03 13:10 UTC (permalink / raw)
  To: brianw@mcsdallas.com; +Cc: pgsql-sql

On Thu, 02 Jan 2003 17:19:52 -0600, "Brian Walker"
<brianw@mcsdallas.com> wrote:
>create table test1 (name varchar(64),num1 int,num2 int);
>create unique index idx1 on test1(name,num1);
>insert into idx1 values ('row3',null,22);
>insert into idx1 values ('row3',null,23);
>
>This is allowed to happen.  In Microsoft SQL the second insert will
>fail because of the unique index.  This looks like in MSSQL for the
>unique index checks that NULL is equal to NULL so the unique check
>fails.  In PostgreSQL NULL != NULL so the unique check passes because
>even though the name is the same the "num1" field is different..

This is just one more issue where Postgres is standard compliant and
MS is not.  Your problem has been discussed before:

http://archives.postgresql.org/pgsql-novice/2002-09/msg00062.php

>Does anybody have any ideas on how I can work around this difference?

Also read the other messages of that thread; thus you should get an
idea of possible solutions.

<nitpicking>
You wrote:
>In PostgreSQL NULL != NULL

While accurate enough for the context you used it in, it is not
completely exact.  NULL = NULL is neither TRUE nor FALSE, it is
UNKNOWN.  The same holds for NULL != NULL.  Try

	SELECT * FROM anytable WHERE NULL = NULL;
	SELECT * FROM anytable WHERE NULL != NULL;

to illustrate this; you get 0 rows in both cases, even in MSSQL ;-).

What's relevant here is that NULL = NULL doesn't evaluate to TRUE,
which explains why rows containing NULL cannot violate a unique
constraint.
</nitpicking>

Servus
 Manfred



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2003-01-03 13:10 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-02 23:19 NULLL comparison in multiple column unique index Brian Walker <brianw@mcsdallas.com>
2003-01-03 13:10 ` Manfred Koizar <mkoi-pg@aon.at>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox