Thread: EXCLUDE constraint with not equals

Kai Groner schrieb am 10.02.2015 um 01:38:
> Given the following table, I would like to ensure that all the rows for an email that have a user defined map to the
sameuser. 
>
> CREATE TABLE person (
>   id INTEGER PRIMARY KEY,
>   user TEXT,
>   email TEXT NOT NULL);
>
>
> What I think I'm looking for is something like this:
>
> CREATE TABLE person (
>   id INTEGER PRIMARY KEY,
>   user TEXT,
>   email TEXT NOT NULL,
>   EXCLUDE (email WITH =, user WITH <>)
>     WHERE (user IS NOT NULL));
>
> The not equals comparison isn't supported, but it would be useful here.
>
> Is there another way to do this, short of creating a separate table that associates email and user?

A partial unique index on (user, email) should do:

   create unique index on person (email, user)
   where user is not null;

Thomas

> 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);
>
>
> What I think I'm looking for is something like this:
>
> CREATE TABLE person (
>   id INTEGER PRIMARY KEY,
>   user TEXT,
>   email TEXT NOT NULL,
>   EXCLUDE (email WITH =, user WITH <>)
>     WHERE (user IS NOT NULL));
>
> The not equals comparison isn't supported, but it would be useful here.
>
> Is there another way to do this, short of creating a separate table that
> associates email and user?

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));