From: Brian Walker <brianw@mcsdallas.com>
To: pgsql-sql@postgresql.org
Subject: NULLL comparison in multiple column unique index
Date: Thu, 02 Jan 2003 17:19:52 -0600
Message-ID: <200301021719520868.01EFA8DC@mail.mcsdallas.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: brianw@mcsdallas.com
Subject: Re: NULLL comparison in multiple column unique index
In-Reply-To: <200301021719520868.01EFA8DC@mail.mcsdallas.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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