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@xxxxxxx> wrote:
Kai Groner schrieb am 10.02.2015 um 01:38:
A partial unique index on (user, email) should do:> 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?
create unique index on person (email, user)
where user is not null;
Thomas
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
