Postgres remove duplicates (multiple columns) in order to add unique constraint

Question

I have a table:

CREATE TABLE public.assignment (
id integer NOT NULL,
dining_table_id integer NOT NULL,
guest_group_id integer NOT NULL,
start_timestamp timestamp without time zone DEFAULT '1999-01-01 00:00:00'::timestamp without time zone NOT NULL,
end_timestamp timestamp without time zone DEFAULT '1999-01-02 00:00:00'::timestamp without time zone NOT NULL,
assignment_related_id text
);

When I add an unique constraint:

 ALTER TABLE assignment ADD CONSTRAINT unique_assignment UNIQUE (dining_table_id, guest_group_id, start_timestamp, end_timestamp);

I get:

ERROR: could not create unique index "unique_assignment" DETAIL: Key (dining_table_id, guest_group_id, start_timestamp, end_timestamp)=(1433, 101476, 2019-07-16 18:30:00, 2019-07-16 20:30:00) is duplicated.

So how can I delete all duplicates, which have the same values in the concerning columns.

dassum · Accepted Answer · 2019-08-05 17:41:38Z

2

DELETE FROM assignment
WHERE id IN (SELECT id
              FROM (SELECT id,
                             ROW_NUMBER() OVER (partition BY dining_table_id, guest_group_id, start_timestamp, end_timestamp ORDER BY id) AS rnum
                     FROM assignment) t
              WHERE t.rnum > 1);

answered Aug 5, 2019 at 17:41

dassum

5,1422 gold badges30 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres remove duplicates (multiple columns) in order to add unique constraint

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related