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:
Then from the psql command prompt enter something like this:
DELETE FROM users_with_dups
WHERE affid NOT IN (SELECT MAX(affid)
GROUP BY usernm,
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.