X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id CD6C3535C1
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 30 Apr 2005 00:48:21 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 48028-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 30 Apr 2005 03:48:13 +0000 (GMT)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210])
	by svr1.postgresql.org (Postfix) with ESMTP id 1E5945380D
	for <pgsql-sql@postgresql.org>; Sat, 30 Apr 2005 00:48:12 -0300 (ADT)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id 069BB358D4; Fri, 29 Apr 2005 20:48:12 -0700 (PDT)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id 04E6F358BB; Fri, 29 Apr 2005 20:48:12 -0700 (PDT)
Date: Fri, 29 Apr 2005 20:48:11 -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: <967F41F63A2D10469114F0A19E56B17E365B8F@SIRIUS.intellidot.net>
Message-ID: <20050429204100.T60676@megazone.bigpanda.com>
References: <967F41F63A2D10469114F0A19E56B17E365B8F@SIRIUS.intellidot.net>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.008 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200504/413
X-Sequence-Number: 21306

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