Postgres Pro
Downloads
Home   >   mailing lists

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

Using EXCLUDE in 9.0 with <> operator ...

From
Derek Harland
Date:
Hallo all,

I have a question about Exclusion constraints in 9.0:
* Lets say I have a table  with two columns X and Y (of type varchar or int)
* Is there any way I can add add an EXCLUDE constraint that says, for each value of X the values of Y must be the same.
* So the following rows are ok:

  X | Y
  ----
  A , 1
  A , 1
  B , 1
  B , 1
  C , 2
  C , 2

But adding

  A , 3

should fail, as everything with value X = "A" should have the same value as existing data (where Y = 1)

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?
(withouthaving to create and then foreign key to another table, which is obviously a possibility) 

des.

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

From
Jeff Davis
Date:
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