Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table port (
  device_id uuid default gen_random_uuid() not null,
  name text not null,
  parent_id uuid default gen_random_uuid() not null);
insert into port values 
  (default, 'usb1',default),
  (default, 'usb2',default),
  (default, 'usb3',default);
insert into port (name,parent_id) select 'usb4',device_id from port where name='usb2';
table port;

CREATE TABLE



INSERT 0 3



INSERT 0 1




device_id
name
parent_id


40d6642a-cf83-44bb-a3b0-8c334d17eb65
usb1
2d948aba-496e-428c-bb26-c052bbfcba0b

50d46064-e14f-4c03-9573-f3e5a5401ccb
usb2
4d894347-c070-4349-9e2e-f39d2d726557

68f208e0-e4fe-4c9c-af9f-47af54e4d589
usb3
50b4dfdd-e656-4acb-b3db-c8916bb7fac4

2595f426-0373-4bfd-881d-c12a4a6e04a2
usb4
50d46064-e14f-4c03-9573-f3e5a5401ccb



SELECT 4


create unique index port_uidx ON port USING btree (device_id, lower(name), parent_id);

CREATE INDEX


--duplicating a random row
insert into port select * from port where name='usb4';
table port;

ERROR:  duplicate key value violates unique constraint "port_uidx"
DETAIL:  Key (device_id, lower(name), parent_id)=(2595f426-0373-4bfd-881d-c12a4a6e04a2, usb4, 50d46064-e14f-4c03-9573-f3e5a5401ccb) already exists.


insert into port(device_id,name) 
select device_id,name from port;
table port;

INSERT 0 4




device_id
name
parent_id


40d6642a-cf83-44bb-a3b0-8c334d17eb65
usb1
2d948aba-496e-428c-bb26-c052bbfcba0b

50d46064-e14f-4c03-9573-f3e5a5401ccb
usb2
4d894347-c070-4349-9e2e-f39d2d726557

68f208e0-e4fe-4c9c-af9f-47af54e4d589
usb3
50b4dfdd-e656-4acb-b3db-c8916bb7fac4

2595f426-0373-4bfd-881d-c12a4a6e04a2
usb4
50d46064-e14f-4c03-9573-f3e5a5401ccb

40d6642a-cf83-44bb-a3b0-8c334d17eb65
usb1
8bea1709-836c-4c41-98b9-0270c159a6eb

50d46064-e14f-4c03-9573-f3e5a5401ccb
usb2
686d0a6d-34f8-4ab8-b598-74815ccc99ba

68f208e0-e4fe-4c9c-af9f-47af54e4d589
usb3
9694bb78-989a-4243-b921-bb7ee50575ea

2595f426-0373-4bfd-881d-c12a4a6e04a2
usb4
3d232afe-68c0-4a8e-96b3-ca24a9833a91



SELECT 8


create unique index port_uidx2 ON port USING btree (device_id, lower(name));

ERROR:  could not create unique index "port_uidx2"
DETAIL:  Key (device_id, lower(name))=(40d6642a-cf83-44bb-a3b0-8c334d17eb65, usb1) is duplicated.


create table port_rejects as 
with first_instances as (
  select device_id,name,min(ctid) as ctid_
  from port group by device_id,name),
cleanup as (
  delete from port where ctid not in (select ctid_ from first_instances)
  returning *)
select * from cleanup;

SELECT 4


create unique index port_uidx2 ON port USING btree (device_id, lower(name));
drop index port_uidx;
table port;
table port_rejects;

CREATE INDEX



DROP INDEX




device_id
name
parent_id


40d6642a-cf83-44bb-a3b0-8c334d17eb65
usb1
2d948aba-496e-428c-bb26-c052bbfcba0b

50d46064-e14f-4c03-9573-f3e5a5401ccb
usb2
4d894347-c070-4349-9e2e-f39d2d726557

68f208e0-e4fe-4c9c-af9f-47af54e4d589
usb3
50b4dfdd-e656-4acb-b3db-c8916bb7fac4

2595f426-0373-4bfd-881d-c12a4a6e04a2
usb4
50d46064-e14f-4c03-9573-f3e5a5401ccb



SELECT 4




device_id
name
parent_id


40d6642a-cf83-44bb-a3b0-8c334d17eb65
usb1
8bea1709-836c-4c41-98b9-0270c159a6eb

50d46064-e14f-4c03-9573-f3e5a5401ccb
usb2
686d0a6d-34f8-4ab8-b598-74815ccc99ba

68f208e0-e4fe-4c9c-af9f-47af54e4d589
usb3
9694bb78-989a-4243-b921-bb7ee50575ea

2595f426-0373-4bfd-881d-c12a4a6e04a2
usb4
3d232afe-68c0-4a8e-96b3-ca24a9833a91



SELECT 4


--this no longer works
insert into port(device_id,name) 
select device_id,name from port;
table port;

ERROR:  duplicate key value violates unique constraint "port_uidx2"
DETAIL:  Key (device_id, lower(name))=(40d6642a-cf83-44bb-a3b0-8c334d17eb65, usb1) already exists.

device_id	name	parent_id
40d6642a-cf83-44bb-a3b0-8c334d17eb65	usb1	2d948aba-496e-428c-bb26-c052bbfcba0b
50d46064-e14f-4c03-9573-f3e5a5401ccb	usb2	4d894347-c070-4349-9e2e-f39d2d726557
68f208e0-e4fe-4c9c-af9f-47af54e4d589	usb3	50b4dfdd-e656-4acb-b3db-c8916bb7fac4
2595f426-0373-4bfd-881d-c12a4a6e04a2	usb4	50d46064-e14f-4c03-9573-f3e5a5401ccb

device_id	name	parent_id
40d6642a-cf83-44bb-a3b0-8c334d17eb65	usb1	8bea1709-836c-4c41-98b9-0270c159a6eb
50d46064-e14f-4c03-9573-f3e5a5401ccb	usb2	686d0a6d-34f8-4ab8-b598-74815ccc99ba
68f208e0-e4fe-4c9c-af9f-47af54e4d589	usb3	9694bb78-989a-4243-b921-bb7ee50575ea
2595f426-0373-4bfd-881d-c12a4a6e04a2	usb4	3d232afe-68c0-4a8e-96b3-ca24a9833a91