Re: EXCLUDE constraint with not equals
| От | Kai Groner |
|---|---|
| Тема | Re: EXCLUDE constraint with not equals |
| Дата | |
| Msg-id | CALiRuxm22LXHo2YjQ7HEWsEmWeSSc=xg1Kr8ZYo83=LzuKy3Rg@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: EXCLUDE constraint with not equals (Emre Hasegeli <emre@hasegeli.com>) |
| Ответы |
Re: EXCLUDE constraint with not equals
|
| Список | pgsql-general |
On Tue, Mar 3, 2015 at 3:27 AM, Emre Hasegeli <emre@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));
>
> > 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.
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));
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
В списке pgsql-general по дате отправления: