Re: EXCLUDE constraint with not equals

[Kai Groner <kai@xxxxxxxxx>]

On Tue, Mar 3, 2015 at 3:27 AM, Emre Hasegeli <emre@xxxxxxxxxxxx> wrote:
>
> > Given the following table, I would like to ensure that all the rows for an
> > email that have a user defined map to the same user.
> >
> > CREATE TABLE person (
> >   id INTEGER PRIMARY KEY,
> >   user TEXT,
> >   email TEXT NOT NULL);
>
> You can use the btree_gist extension from contrib:
>
> CREATE EXTENSION btree_gist;
>
> CREATE TABLE person (
>   id INTEGER PRIMARY KEY,
>   "user" TEXT,
>   email TEXT NOT NULL,
>   EXCLUDE USING gist (email WITH =, "user" WITH <>)
>     WHERE ("user" IS NOT NULL));

Thanks, Emre.  The btree_gist extension seems to be just what I was looking for.

I found it necessary to add the gist_text_ops opclass for the inequality:

CREATE TABLE person (
  id INTEGER PRIMARY KEY,
  "user" TEXT,
  email TEXT NOT NULL,
  EXCLUDE USING gist (email WITH =, "user" gist_text_ops WITH <>)
    WHERE ("user" IS NOT NULL));

Is that expected?

Kai