Date: Sat, 30 Apr 2005 09:41:42 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: "Tornroth, Phill" <ptornroth@intellidot.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: multi-column unique constraints with nullable columns
In-Reply-To: <967F41F63A2D10469114F0A19E56B17E365B9A@SIRIUS.intellidot.net>
Message-ID: <20050430093304.C99273@megazone.bigpanda.com>
References: <967F41F63A2D10469114F0A19E56B17E365B9A@SIRIUS.intellidot.net>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

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).