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?
# 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)