pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedmulti-column unique constraints with nullable columns
6+ messages / 3 participants
[nested] [flat]
* multi-column unique constraints with nullable columns
@ 2005-04-29 16:13 Tornroth, Phill <ptornroth@intellidot.net>
2005-04-30 03:48 ` Re: multi-column unique constraints with nullable columns Stephan Szabo <sszabo@megazone.bigpanda.com>
2005-05-05 19:03 ` Re: multi-column unique constraints with nullable columns Mikey <mikeboscia@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Tornroth, Phill @ 2005-04-29 16:13 UTC (permalink / raw)
To: pgsql-sql
I have many tables who's natural key includes a nullable column. In this cases it's a soft-delete or 'deprecated' date time. I'd like to add a table constraint enforcing this constraint without writing a custom procedure, but I've found that postgres treats NULLs very consistently with respect to the NULL != NULL behavior. As a result, when I define a constraint on the last two columns in these insert statements... they both succeed.
insert into mytable values (1,300, null);
insert into mytable values (1,300, null);
This is frustrating, and while there may be someone who actually wants constraints to work this way... I can't understand why.
Now, I understand that the best way to solve my problem would be to use only non-nullable columns for my natural keys. I actually plan to do that, and use a very high value for my 'undeprecated' date to solve most of my problems related to this. However, I can't release that version of software carelessly and I need to tighten up customer databases in the meantime.
Is there a way to get the behavior I want?
Also, is this in compliance with SQL92? I'm surprised constraints work this way.
Thank you,
Phill
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: multi-column unique constraints with nullable columns
2005-04-29 16:13 multi-column unique constraints with nullable columns Tornroth, Phill <ptornroth@intellidot.net>
@ 2005-04-30 03:48 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
1 sibling, 0 replies; 6+ messages in thread
From: Stephan Szabo @ 2005-04-30 03:48 UTC (permalink / raw)
To: Tornroth, Phill <ptornroth@intellidot.net>; +Cc: pgsql-sql
On Fri, 29 Apr 2005, Tornroth, Phill wrote:
> I have many tables who's natural key includes a nullable column. In this
> cases it's a soft-delete or 'deprecated' date time. I'd like to add a
> table constraint enforcing this constraint without writing a custom
> procedure, but I've found that postgres treats NULLs very consistently
> with respect to the NULL != NULL behavior. As a result, when I define a
> constraint on the last two columns in these insert statements... they
> both succeed.
>
> insert into mytable values (1,300, null);
> insert into mytable values (1,300, null);
>
> This is frustrating, and while there may be someone who actually wants
> constraints to work this way... I can't understand why.
>
> Now, I understand that the best way to solve my problem would be to use
> only non-nullable columns for my natural keys. I actually plan to do
> that, and use a very high value for my 'undeprecated' date to solve most
> of my problems related to this. However, I can't release that version of
> software carelessly and I need to tighten up customer databases in the
> meantime.
>
> Is there a way to get the behavior I want?
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.
> Also, is this in compliance with SQL92? I'm surprised constraints work
> this way.
As far as we can tell, this is explicitly what SQL wants to happen. The
UNIQUE predicate (which the UNIQUE constraint is described in terms of)
is defined as "If there are no two rows in T such that the value of each
column in one row is non-null and is equal to the value of the
corresponding column in the other row according to Subclause 8.2 ... then
the result of the <unique predicate> is true; otherwise, the result of the
<unique predicate> is false."
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: multi-column unique constraints with nullable columns
2005-04-29 16:13 multi-column unique constraints with nullable columns Tornroth, Phill <ptornroth@intellidot.net>
@ 2005-05-05 19:03 ` Mikey <mikeboscia@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Mikey @ 2005-05-05 19:03 UTC (permalink / raw)
To: Tornroth, Phill <ptornroth@intellidot.net>; +Cc: pgsql-sql
It may be possible to bypass the NULL != NULL by using coalesce. Here
is an example:
<BEGIN SQL>
create table foo2 (
a integer not null,
b integer not null,
c integer null,
UNIQUE (a,b,c)
);
create function foo2_unique_func() RETURNS trigger AS '
DECLARE
isfound integer = 0;
BEGIN
isfound = (select count(*) from foo2 where
(new.a,new.b,coalesce(new.c::TEXT,''EmPtY'')) in (select
a,b,coalesce(c::TEXT,''EmPtY'')
from foo2));
RAISE NOTICE ''isfound: %'', isfound;
IF isfound > 0 THEN
RAISE EXCEPTION ''Columns a,b,c Must Be Unique values
(%,%,%)'', new.a, new.b, new.c;
ELSE
RETURN NEW;
END IF;
END; ' language 'plpgsql';
CREATE TRIGGER foo2_unique BEFORE INSERT OR UPDATE ON foo2
FOR EACH ROW EXECUTE PROCEDURE foo2_unique_func();
insert into foo2 values (1,300, null);
insert into foo2 values (1,300, null);
select * from foo2;
select * from foo2 where (1,300,coalesce(null::TEXT,'EmPtY')) in
(select a,b,coalesce(c::TEXT,'EmPtY') from foo2);
drop table foo2 cascade;
drop function foo2_unique_func() cascade;
<END SQL>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: multi-column unique constraints with nullable columns
@ 2005-04-30 15:01 Tornroth, Phill <ptornroth@intellidot.net>
2005-04-30 16:41 ` Re: multi-column unique constraints with nullable columns Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tornroth, Phill @ 2005-04-30 15:01 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: pgsql-sql
>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?
> 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);
IF FOUND THEN
RAISE EXCEPTION ''Invalid Row!'';
END IF;
RETURN NEW;
END;
'LANGUAGE 'plpgsql';
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: multi-column unique constraints with nullable columns
2005-04-30 15:01 Re: multi-column unique constraints with nullable columns Tornroth, Phill <ptornroth@intellidot.net>
@ 2005-04-30 16:41 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 6+ messages in thread
From: Stephan Szabo @ 2005-04-30 16:41 UTC (permalink / raw)
To: Tornroth, Phill <ptornroth@intellidot.net>; +Cc: 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).
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: multi-column unique constraints with nullable columns
@ 2005-05-05 19:06 Tornroth, Phill <ptornroth@intellidot.net>
0 siblings, 0 replies; 6+ messages in thread
From: Tornroth, Phill @ 2005-05-05 19:06 UTC (permalink / raw)
To: Mikey <mikeboscia@gmail.com>; +Cc: pgsql-sql
Someone else suggested using coalesce to me as well. Isn't your function equivilant to mine? If so, I think the most elegant of these solutions is closer to the one I proposed (with Stephen's modification). Unfortunately, we've still got the concurrency problems that Stephen pointed out.
I sure would like the ability to affect the UNIQUE constraint's behavior to support this. Although, I'm probably in the minority and it would obviously be un-portable.
I think my plan will actually be to plug leaks in the domain layer and move toward non-nullable natural keys. I was hoping to find something better, but I frankly don't have the experience in house to write contstraints I'll have confidence in.
Thanks for the tip!
Phill
-----Original Message-----
From: Mikey [mailto:mikeboscia@gmail.com]
Sent: Thu 5/5/2005 12:03 PM
To: Tornroth, Phill
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] multi-column unique constraints with nullable columns
It may be possible to bypass the NULL != NULL by using coalesce. Here
is an example:
<BEGIN SQL>
create table foo2 (
a integer not null,
b integer not null,
c integer null,
UNIQUE (a,b,c)
);
create function foo2_unique_func() RETURNS trigger AS '
DECLARE
isfound integer = 0;
BEGIN
isfound = (select count(*) from foo2 where
(new.a,new.b,coalesce(new.c::TEXT,''EmPtY'')) in (select
a,b,coalesce(c::TEXT,''EmPtY'')
from foo2));
RAISE NOTICE ''isfound: %'', isfound;
IF isfound > 0 THEN
RAISE EXCEPTION ''Columns a,b,c Must Be Unique values
(%,%,%)'', new.a, new.b, new.c;
ELSE
RETURN NEW;
END IF;
END; ' language 'plpgsql';
CREATE TRIGGER foo2_unique BEFORE INSERT OR UPDATE ON foo2
FOR EACH ROW EXECUTE PROCEDURE foo2_unique_func();
insert into foo2 values (1,300, null);
insert into foo2 values (1,300, null);
select * from foo2;
select * from foo2 where (1,300,coalesce(null::TEXT,'EmPtY')) in
(select a,b,coalesce(c::TEXT,'EmPtY') from foo2);
drop table foo2 cascade;
drop function foo2_unique_func() cascade;
<END SQL>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-05-05 19:06 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-29 16:13 multi-column unique constraints with nullable columns Tornroth, Phill <ptornroth@intellidot.net>
2005-04-30 03:48 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2005-05-05 19:03 ` Mikey <mikeboscia@gmail.com>
2005-04-30 15:01 Re: multi-column unique constraints with nullable columns Tornroth, Phill <ptornroth@intellidot.net>
2005-04-30 16:41 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2005-05-05 19:06 Re: multi-column unique constraints with nullable columns Tornroth, Phill <ptornroth@intellidot.net>
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