From: Marc Mamin <M.Mamin@intershop.de>
To: "Voillequin, Jean-Marc" <Jean-Marc.Voillequin@moodys.com>,
	"pgsql-sql@lists.postgresql.org" <pgsql-sql@lists.postgresql.org>
Subject: AW: unique index with several columns
Thread-Topic: unique index with several columns
Thread-Index: Adgv15Nf9+BSttqaTPmnX2Q0fyVmZAAAg5tgAADRWPE=
Date: Fri, 4 Mar 2022 15:32:38 +0000
Message-ID: <d931301d59d14c25a1a41e416ac4adf1@intershop.de>
References: 
 <MN2PR20MB2735507A85A89B7279AB6A2CBE059@MN2PR20MB2735.namprd20.prod.outlook.com>,<MN2PR20MB2735677B2B4E5D3C4EA71B5EBE059@MN2PR20MB2735.namprd20.prod.outlook.com>
In-Reply-To: 
 <MN2PR20MB2735677B2B4E5D3C4EA71B5EBE059@MN2PR20MB2735.namprd20.prod.outlook.com>
Accept-Language: de-DE, en-US
Content-Language: de-DE
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/d931301d59d14c25a1a41e416ac4adf1%40intershop.de>
Precedence: bulk

you can use partial indexes:

create unique index idx  on t(coalesce(c1,''),coalesce(c2,'') )   where (co=
alesce(c1, c2) is not null);

  but '' is considered equals to null here ...


or more partial indexes which are  more likely to be considered by the plan=
er:

create unique index idx_1 on t(c1) where (c2 is null );
create unique index idx_2 on t(c2) where (c1 is null);
create unique index idx_3 on t(c1, c2)  where (c1 is not null and c2 is not=
 null);

hth,

Marc


________________________________________
Von: Voillequin, Jean-Marc <Jean-Marc.Voillequin@moodys.com>
Gesendet: Freitag, 4. M=E4rz 2022 16:09:43
An: pgsql-sql@lists.postgresql.org
Betreff: RE: unique index with several columns

Sorry, the test case is:

On PG:

RRT=3D> create table t(c1 char, c2 char);
CREATE TABLE
RRT=3D> create unique index idx on t(c1,c2);
CREATE INDEX
RRT=3D> insert into t(c1,c2) values (null,null);
INSERT 0 1
RRT=3D> insert into t(c1,c2) values (null,null);
INSERT 0 1
RRT=3D> insert into t(c1,c2) values ('a',null);
INSERT 0 1
RRT=3D> 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=3D> create table t(c1 char, c2 char);
ERROR:  relation "t" already exists
RRT=3D> create unique index idx on t(c1,c2);
ERROR:  relation "idx" already exists
RRT=3D> insert into t(c1,c2) values (null,null);
INSERT 0 1
RRT=3D> insert into t(c1,c2) values (null,null);
INSERT 0 1
RRT=3D> insert into t(c1,c2) values ('a',null);
INSERT 0 1
RRT=3D> 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 dup=
licate key error.

Thanks & regards

-----------------------------------------
Moody's monitors email communications through its networks for regulatory c=
ompliance purposes and to protect its customers, employees and business and=
 where allowed to do so by applicable law. The information contained in thi=
s e-mail message, and any attachment thereto, is confidential and may not b=
e disclosed without our express permission. If you are not the intended rec=
ipient or an employee or agent responsible for delivering this message to t=
he intended recipient, you are hereby notified that you have received this =
message in error and that any review, dissemination, distribution or copyin=
g of this message, or any attachment thereto, in whole or in part, is stric=
tly prohibited. If you have received this message in error, please immediat=
ely notify us by telephone, fax or e-mail and delete the message and all of=
 its attachments. Every effort is made to keep our network free from viruse=
s. You should, however, review this e-mail message, as well as any attachme=
nt thereto, for viruses. We take no responsibility and have no liability fo=
r any computer virus which may be transferred via this e-mail message.
-----------------------------------------