How can I remove one of fields in unique index? PostgreSQL

Question

I've created unique index smth. like this

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

But now I wanna exclude one of fields, for example parent_id What should I do in this case? I need to save unique index without parent_id.

P.S. One more thing, there are duplicates migth be. This point needs to be taken into

Thank you

---

Answer 1

Clean up the table so that it no longer violates the unique constraint you want to add, then add it. Instead of just wiping the violating rows, you can move them elsewhere using delete...returning clause: demo

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;

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

You can rely on ctid when you combine statements into one, but don't expect it to be stable between separate, even consecutive queries.