Message-ID: <200301021719520868.01EFA8DC@mail.mcsdallas.com>
Date: Thu, 02 Jan 2003 17:19:52 -0600
Reply-To: brianw@mcsdallas.com
From: "Brian Walker" <brianw@mcsdallas.com>
To: pgsql-sql@postgresql.org
Subject: NULLL comparison in multiple column unique index
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

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 tab=
le.  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 be=
cause of the unique index.  This looks like in MSSQL for the unique index c=
hecks that NULL is equal to NULL so the unique check fails.  In PostgreSQL =
NULL !=3D NULL so the unique check passes because even though the name is t=
he same the "num1" field is different..

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

Thanks