how to remove duplicated rows?

this questions pops every once in a while on irc.

some guy has a table, and it contains duplicated rows.

basically there are 2 possible scenarios:

  • all columns are duplicated
  • only some columns are duplicated

so, how to remove duplicates from such tables?

let's check what to do when all columns are duplicated, like in here:

# select * from test;
 some_text | some_int
-----------+----------
 a         |        1
 a         |        1
 b         |        1
 b         |        2
 b         |        2
(5 rows)

the simplest approach to fix the table is to:

# create temp table temp_test as select distinct * from test;
truncate test;
insert into test select * from temp_test;
drop table temp_test;

after this – contents of the table looks like this:

# select * from test;
 some_text | some_int
-----------+----------
 a         |        1
 b         |        1
 b         |        2
(3 rows)

but what if you don't want to copy values between tables?

it's also quite simple – we will simply add new column, enter some unique data there, and then use it to differentiate the rows:

# create temp sequence temp_seq;
CREATE SEQUENCE
# alter table test add column unique_id int4;
ALTER TABLE
# update test set unique_id = nextval('temp_seq');
UPDATE 5
# delete from test as t1 where exists (Select * from test t2 where (t2.some_text, t2.some_int) = (t1.some_text, t1.some_int) and t2.unique_id > t1.unique_id);
DELETE 2
# alter table test drop column unique_id;
ALTER TABLE

after this it is good to do vacuum of the table.

also – if you dont have index on (some_text, some_int) – it will be good to add such a index to speed up the removal.

and what in case not all of the columns are duplicated? like in this case:

# select * from test;
 id | some_text | some_int
----+-----------+----------
  2 | a         |        1
  3 | a         |        1
  4 | b         |        1
  5 | b         |        2
  6 | b         |        2
(5 rows)

it's simple – use the trick shown above, but this time you don't have to add new column – you already have it.

interesting thing is when you consider that only some_text should be unique. and if many records have the same some_text – we should keep the one with lowest some_int. and if more then one have the same some_int – keep the newest (with highest id).

how to do it?

simple:

# delete from test as t1 where exists (select * from test t2 where t2.some_text = t1.some_text and (t2.some_int < t1.some_int or (t2.some_int = t1.some_int and t2.id > t1.id)));
DELETE 3
# select * from test;
 id | some_text | some_int
----+-----------+----------
  3 | a         |        1
  4 | b         |        1
(2 rows)

7 thoughts on “how to remove duplicated rows?”

  1. Instead of using a Sequence, is it possible to use the CTID to distinguish between two row?

  3. @Richard Broersma Jr.:
    it is of course possible (like Pavel showed), but i’m not really enthusiastic about using system-data in standard (ekhem) sql queries.
    the idea and usage is great. i just have “moral” issues with it 🙂

  6. @depesc, ofcourse .. on bigger tables can be slow, corelated subqueries are efective in 10% very specific cases and it’s better doesn’t use it

Comments are closed.