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

Last active July 9, 2016 00:42

Select an option

Learn more about clone URLs
Clone this repository at <script src="https://gist.github.com/nepsilon/aa65b04ad46836a608b3.js"></script>
Save nepsilon/aa65b04ad46836a608b3 to your computer and use it in GitHub Desktop.

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

Raw

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