RE: unique index with several columns - Mailing list pgsql-sql
From | Voillequin, Jean-Marc |
---|---|
Subject | RE: unique index with several columns |
Date | |
Msg-id | MN2PR20MB2735677B2B4E5D3C4EA71B5EBE059@MN2PR20MB2735.namprd20.prod.outlook.com Whole thread Raw |
In response to | unique index with several columns ("Voillequin, Jean-Marc" <Jean-Marc.Voillequin@moodys.com>) |
Responses |
AW: unique index with several columns
|
List | pgsql-sql |
Sorry, the test case is:
On PG:
RRT=> create table t(c1 char, c2 char);
CREATE TABLE
RRT=> create unique index idx on t(c1,c2);
CREATE INDEX
RRT=> insert into t(c1,c2) values (null,null);
INSERT 0 1
RRT=> insert into t(c1,c2) values (null,null);
INSERT 0 1
RRT=> insert into t(c1,c2) values ('a',null);
INSERT 0 1
RRT=> insert into t(c1,c2) values ('a',null);
INSERT 0 1
On Oracle:
SQL> create table t(c1 char, c2 char);
Table created.
SQL> create unique index idx on t(c1,c2);
Index created.
SQL> insert into t(c1,c2) values (null,null);
1 row created.
SQL> insert into t(c1,c2) values (null,null);
1 row created.
SQL> insert into t(c1,c2) values ('a',null);
1 row created.
SQL> insert into t(c1,c2) values ('a',null);
insert into t(c1,c2) values ('a',null)
*
ERROR at line 1:
ORA-00001: unique constraint (RRT.IDX) violated
The question is the same.
From: Voillequin, Jean-Marc
Sent: Friday, March 4, 2022 4:07 PM
To: pgsql-sql@lists.postgresql.org
Subject: unique index with several columns
Hello,
On PG:
RRT=> create table t(c1 char, c2 char);
ERROR: relation "t" already exists
RRT=> create unique index idx on t(c1,c2);
ERROR: relation "idx" already exists
RRT=> insert into t(c1,c2) values (null,null);
INSERT 0 1
RRT=> insert into t(c1,c2) values (null,null);
INSERT 0 1
RRT=> insert into t(c1,c2) values ('a',null);
INSERT 0 1
RRT=> insert into t(c1,c2) values ('a',null);
INSERT 0 1
On Oracle:
SQL> create table t(c1 char, c2 char);
Table created.
SQL> create unique index idx on t(c1,c2);
Index created.
SQL> insert into t(c1,c2) values (null,null);
1 row created.
SQL> insert into t(c1,c2) values (null,null);
1 row created.
SQL> insert into t(c1,c2) values ('a',null);
1 row created.
SQL> insert into t(c1,c2) values ('a',null);
insert into t(c1,c2) values ('a',null)
*
ERROR at line 1:
ORA-00001: unique constraint (RRT.IDX) violated
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.
Thanks & regards