Hi Thomas, The partial unique index would prevent multiple person records with the same email. I want to allow that as long as they agree on the value of user.
Kai On Tue, Feb 10, 2015 at 2:14 AM, Thomas Kellerer <spam_ea...@gmx.net> wrote: > 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 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? > > A partial unique index on (user, email) should do: > > create unique index on person (email, user) > where user is not null; > > Thomas > > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
