Re: Deferred partial/expression unique constraints

On 13 July 2011 01:23, Andres Freund <andres(at)anarazel(dot)de> wrote:
> On Tuesday, July 12, 2011 08:57:44 PM Dean Rasheed wrote:
>> On 12 July 2011 19:26, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>> > On 7/12/11 9:46 AM, Andres Freund wrote:
>> >> Hi,
>> >>
>> >> I guess $subject wasn't implemented because plain unique indexes aren't
>> >> represented in pg_constraint and thus do not have a place to store
>> >> information about being deferred?
>> >> Other than that I do not see any special complications in implementing
>> >> it?
>> >
>> > Um, I thought that deferrable unique constraints were a 9.0 feature, no?
>>
>> Yes, but there is no syntax to create a unique constraint on an
>> expression, and hence to create a deferrable unique expression check.
>>
>> However, that doesn't seem like such a serious limitation, because the
>> same functionality can be achieved using an exclusion constraint with
>> the equality operator.
> That doesn't solve the issue of a partial index, right? Also I find it that
> intuitive to package a expression inside an operator (which needs to be
> complicated enough not to be accidentally used and still be expressive...).
> Especially if that expression involves more than one column (which isn't that
> hard to imagine).
>

Yes, it also appears to cover partial indexes. For example:

CREATE TABLE foo
(
a int,
b int,
CONSTRAINT sum_unique EXCLUDE ((a+b) WITH =) WHERE (a>0 AND b>0)
);
INSERT INTO foo VALUES(3,7);
INSERT INTO foo VALUES(-1,11);
INSERT INTO foo VALUES(2,8);

I agree that expressing that using a UNIQUE constraint would perhaps
be more intuitive, but it would be new non-SQL-spec syntax that AFAICS
wouldn't actually add any new functionality.

Regards,
Dean