How to delete rows from table where some columns (but not all) are duplicate in postgresql?

Question

I read previous questions but they are not similar to mine (example this has no unique identifier while I have) .

say this are my tables rows:

  id     string       x    y    z    time_x                 
10318    'hello'   33700  5053  8  2015-07-03 12:39:49.61408
14071    'hello'   33700  5053  8  2015-07-03 12:39:49.61408

basically everything is the same except the id of the row. How can I identify these cases and delete the redundant rows?

Note: not all rows in table are duplicated. Regular rows should be left as is.

Possible duplicate of https://stackoverflow.com/questions/3777633/delete-duplicate-rows-dont-delete-all-duplicate — hruske, Feb 29 '16 at 11:43

score 5 · Accepted Answer · edited Feb 29 '16 at 11:47

5

Let say that table name is tbl

Delete from tbl where id not in (select min(id) from tbl group by string, x, y, z)

edited Feb 29 '16 at 11:47

artm

8,554
3
26
43

answered Feb 29 '16 at 11:41

dabal

410
3
15

hruske · Answer 2 · 2016-02-29T11:56:05.290

0

This query uses window function to find duplicated rows. It leaves the row with smallest id:

delete from foo_table where id in (
    select dupid from (
        SELECT lead(id) over (partition by string, x, y, z, time_x order by id) dupid
        from foo_table
    ) A where dupid is not null 
);

edited Feb 29 '16 at 11:56

answered Feb 29 '16 at 11:49

hruske

2,205
19
27

How to delete rows from table where some columns (but not all) are duplicate in postgresql?

2 Answers2