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