Re: multi-column unique constraints with nullable columns - Mailing list pgsql-sql

From Stephan Szabo
Subject Re: multi-column unique constraints with nullable columns
Date
Msg-id 20050430093304.C99273@megazone.bigpanda.com
Whole thread Raw
In response to multi-column unique constraints with nullable columns  ("Tornroth, Phill" <ptornroth@intellidot.net>)
List pgsql-sql
On Sat, 30 Apr 2005, Tornroth, Phill wrote:

> >I believe you can add partial unique indexes to cover the case where a
> >column is null, but if you have multiple nullable columns you need to
> >worry about you end up with a bunch of indexes.
>
> Hmmm. I hadn't thought of that, thanks. Yes, the indexes would be
> unnessecary though. Speaking of, should be concerned about indexing
> NULLABLE columns?

I believe you're at least safe with btree indexes.

The advantage of using the unique partial indexes is that it'll handle
concurrent inserts without you having to worry about it.

> > Also, is this in compliance with SQL92? I'm surprised constraints work
> > this way.
> he
> I read that. I think you're right, it sounds like any comparison
> containing NULL at all will fail.
>
>
> I wrote the following procedure, which seems to do the trick. I guess my
> plan would be to write a bunch of these, and create the indexes
> manually. If anyone sees any problems with this, I'd love some input.
> Also, if anyone at the end of this email is a DBA/Consultant type and
> works in the San Diego area... Definitely let me know :)
>
> CREATE FUNCTION check_mytable_natkey() RETURNS opaque AS '
>  DECLARE
>   conflictingpk integer;
>  BEGIN
>
>    SELECT INTO conflictingpk a
>     FROM mytable
>     WHERE ((b is null and NEW.b is null) or b = NEW.b)
>       AND ((c is null and NEW.c is null) or c = NEW.c);

Unfortunately, I don't think this will work if two sessions come in at the
same time trying to insert the same values since they won't see each
other's changes. I think it also will raise an error if the existing row
has been deleted by a not yet committed transaction while our current
implementation of unique constraints would wait to see if the transaction
commits.

On a side note, I believe (x is null and y is null) or x=y can be written
a little more succintly with NOT(x IS DISTINCT FROM y).



pgsql-sql by date:

Previous
From: Tom Lane
Date:
Subject: Re: Build issues: "-static" builds resulting initdb problems
Next
From: Cosimo Streppone
Date:
Subject: pgtop, display PostgreSQL processes in `top' style