Re: EXCLUDE constraint with not equals

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_eater(at)gmx(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>