Re: unique index with several columns
| От | David G. Johnston |
|---|---|
| Тема | Re: unique index with several columns |
| Дата | |
| Msg-id | CAKFQuwa4dELLaEdm2UsZK-LyRA08VYOYQZyxNs8zF25nYzgv3w@mail.gmail.com обсуждение исходный текст |
| Ответ на | unique index with several columns ("Voillequin, Jean-Marc" <Jean-Marc.Voillequin@moodys.com>) |
| Ответы |
RE: unique index with several columns
|
| Список | pgsql-sql |
On Fri, Mar 4, 2022 at 8:07 AM Voillequin, Jean-Marc <Jean-Marc.Voillequin@moodys.com> wrote:
When one of the field is null, PG considers that the tuple is not the same:
('a',null) is not equal to ('a',null)
So, the unique constraint is not violated in PG.
But is there a way to have the same feature than Oracle?
I already tried with:
create unique index idx on t(coalesce(c1,''),coalesce(c2,''))
But in this case, I cannot insert several (null,null) without raising a duplicate key error.
You just said you wanted nulls to be considered equal to each other...
Not tested but these should work in conjunction with each other:
CREATE UNIQUE INDEX idx ON t (c1, c2) WHERE c1 IS NOT NULL AND c2 IS NOT NULL;
CREATE UNIQUE INDEX idx2 ON t (c1) WHERE c2 IS NULL AND c1 IS NOT NULL;
CREATE UNIQUE INDEX idx3 ON t (c2) WHERE c1 IS NULL AND c2 IS NOT NULL;
--CREATE INDEX idx4 ON t (c1, c2) WHERE c1 IS NULL AND c2 IS NULL; (pointless...but symmetric with the others)
Hopefully this helps.
David J.
В списке pgsql-sql по дате отправления: