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
Date: Sat, 30 Apr 2005 09:41:42 -0700 (PDT)
Message-ID: <20050430093304.C99273@megazone.bigpanda.com> (raw)
In-Reply-To: <967F41F63A2D10469114F0A19E56B17E365B9A@SIRIUS.intellidot.net>
References: <967F41F63A2D10469114F0A19E56B17E365B9A@SIRIUS.intellidot.net>
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).
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: sszabo@megazone.bigpanda.com, ptornroth@intellidot.net
Subject: Re: multi-column unique constraints with nullable columns
In-Reply-To: <20050430093304.C99273@megazone.bigpanda.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