How to remove duplicated rows in a PostgreSQL table? — First published in fullweb.io issue #23

how-to-remove-duplicated-rows-postgres.md

How to remove duplicated rows in a PostgreSQL table?

The simplest and certainly safest way remains to copy data from a DISTINCT query to a new table, and to rename it to the original table.

Here our table with duplicated rows is named t_word:

STEP 1:

Create a new table without the duplicated rows:

CREATE TABLE t_word_tmp AS 
  SELECT DISTINCT * FROM t_word;

Your SELECT DISTINCT query will return records without duplicated rows.

STEP 2:

Now rename the new table:

ALTER TABLE t_word_tmp RENAME TO t_word; 

BONUS: Check table structure with:

\d+ t_word