Detect duplicate records before aplying a unique Index in postgresql

Question

I have the following tables:

create workers (
  id SERIAL PRIMARY KEY,
  name STRING
);

create works_zones (
  id SERIAL PRIMARY KEY,
  area_name String,
  work_from TIME WITHOUT TIMEZONE,
  work_until TIME WITHOUT TIMEZONE
) 

create table shifts (
  id SERIAL PRIMARY KEY,
  worker_id INT,
  day_of_week INT,
  work_zone INT,
  CONSTRAINT fk_worker FOREIGN KEY(worker_id) REFERENCES workers(id) ON DELETE CASCADE,
  CONSTRAINT fk_work_zone FOREIGN KEY(work_zone) REFERENCES works_zones(id) ON DELETE CASCADE
)

And in my table shifts I have the folowing records:

id	worker_id	day_of_week	work_zone
1	1	1	1
2	1	1	1
3	2	2	1
3	2	2	1

In my case I want to apply a unique Index on columns worker_id, day_of_week, work_zone at table shifts:

CREATE UNIQUE INDEX CONCURRENTLY unique_day_zone_worker ON shifts (worker_id,work_zone,"day_of_week");

But according to my records will fail. Therefore, I would like to detect duplicate records so I can manually fix them up. Any Ideas how I can do it?

In other wqords I want to detect the shift.id that have duplicate records at columns worker_id,day_of_week,work_zone.

Answer 1

An answer to that is the following: https://stackoverflow.com/a/32265524/4706711

In your case you'll need to run:

select * from (
  SELECT id,
  ROW_NUMBER() OVER(PARTITION BY worker_id, day_of_week,work_zone ORDER BY id asc) AS Row
  FROM work_zone
) dups
where 
dups.Row > 1

The you'll need either to delete manually and update the records accorditly using update and select queries. Then you'll need to create then unique index:

CREATE UNIQUE INDEX CONCURRENTLY unique_day_zone_worker ON shifts (worker_id,work_zone,"day_of_week");

I suggest using a Single transaction for both updates, deletes and Unique indexes so you can avoid any mishaps.