Remove Duplicate Rows from Postgresql Database

In this short tutorial, I will demonstrate how to remove duplicate rows in a postgresql table.

Remove duplicate rows from command line

Log into your server via ssh or open a terminal window and issue the following commands:

To log into psql use the following command from command prompt:

psql database_name

Then from the psql command prompt enter something like this:

DELETE FROM users_with_dups
WHERE  affid NOT IN (SELECT MAX(affid)
                  FROM   users_with_dups
                  GROUP  BY usernm,
                            pass
                  HAVING MAX(affid) IS NOT NULL)

Of course, in the above query, you will have to change the table name values(two of them which will be the same) and the id column name as well as the group by column names.

Leave a Reply

Your email address will not be published. Required fields are marked *