Re: Using EXCLUDE in 9.0 with <> operator ...

On Wed, 2010-11-10 at 13:45 +1300, Derek Harland wrote:
> Theoretically the following would be nice if it worked
>
> EXCLUDE (X WITH =, Y WITH <>)
>
> but it complains that
>
> ERROR: operator <>(text,text) is not a member of operator family
> "text_ops"
>
> because the Btree index method only allows = in an exclude constraint.
> Or am I missing a simpler way of doing this? (without having to
> create and then foreign key to another table, which is obviously a
> possibility)
>

Try using 9.1alpha ( http://www.postgresql.org/developer/alpha ) and
installing btree_gist. Then, use:

EXCLUDE USING gist (X WITH =, Y WITH <>)

In 9.0 this particular constraint won't work because there is an
additional sanity check in the code that won't pass if the operator is
"<>". The sanity check was deemed worthwhile for the first release of
the feature, but will be lifted in version 9.1.

Also, "<>" doesn't work (yet) with btree, but in principle there is no
reason why not. Perhaps for 9.1 as well.

Can you please share your use case for this particular constraint? I'd
like to hear it.

Regards,
Jeff Davis