On Tue, Mar 3, 2015 at 3:27 AM, Emre Hasegeli <e...@hasegeli.com> 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