From: | Emre Hasegeli <emre(at)hasegeli(dot)com> |
---|---|
To: | Kai Groner <kai(at)gronr(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: EXCLUDE constraint with not equals |
Date: | 2015-03-03 08:27:57 |
Message-ID: | CAE2gYzyqWzyyUCXRcqgthcGqjsHX=zNWHVMN2myrEEqduaJSyQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> 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));
From | Date | Subject | |
---|---|---|---|
Next Message | Igor Stassiy | 2015-03-03 09:01:20 | ANALYZE command question |
Previous Message | David Kerr | 2015-03-03 00:11:43 | Re: Replication fell out of sync |