How to remove duplicate entries in postgresql table?

Question

I have a postgresql table without primary key. I want to remove all entries that have the same id, but retain the most recent one.

The following statement almost works:

DELETE FROM mytable USING mytable t
  WHERE mytable.id = t.id AND mytable.modification < t.modification;

Problem: when two entries have the same modification timestamp (which is possible), both are retained. What would I have to change to just keep one of them, does not matter which one?

I cannot change the condition to AND mytable.modification <= t.modification; as this would then remove all dublicates not retaining any entry.

Gordon Linoff · Accepted Answer · 2015-02-27 11:06:27Z

2

If you have rows that are complete duplicates (i.e., no way to distinguish one from the other), then you have two options. One is to use a built-in row identifier such as ctid:

DELETE FROM mytable USING mytable t
  WHERE mytable.id = t.id AND
        (mytable.modification < t.modification OR
         mytable.modification = t.modification AND mytable.ctid < t.ctid);

Or use a secondary table:

create table tokeep as
    select distinct on (t.id) t.*
    from mytable
    order by t.id, t.modification;

truncate table mytable;

insert into mytable
    select * from tokeep;

answered Feb 27, 2015 at 11:06

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

jarlh · Accepted Answer · 2015-02-27 10:51:40Z

0

Use EXISTS to see if there are other rows with same id:

DELETE FROM mytable t
WHERE EXISTS (SELECT 1 from mytable
              WHERE id = t.id AND modification > t.modification);

answered Feb 27, 2015 at 10:51

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

4 Comments

membersound Over a year ago

I tried it but 0 rows where affected in a table with 2 entries having exactly the same id and modification timestamp. But one of them should get deleted.

jarlh Over a year ago

It's hard to remove just one of two equal rows. Move to temp-table using select distinct?

membersound Over a year ago

Could I tell the select distinct(id) to fetch the most recent row with the latest modification stamp?

jarlh Over a year ago

See Gordon Linoff's answer above.

Collectives™ on Stack Overflow

How to remove duplicate entries in postgresql table?

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related