Search code examples
postgresqlpostgresql-14

How to delete duplicate rows and keep just one row with multiple columns

I have duplicate data in a table called bank_currency that looks like this:

currencyid | bankid
--------------------
8             1
8             1
8             1
16            2
16            2
16            2
14            3
14            3
14            3

I have no idea why the data has been duplicated in triplicate, but I need to get rid of all the duplicates and keep only one of each row. So I end up like this:

currencyid | bankid
--------------------
8             1
16            2
14            3

I cannot ORDER BY the bankid or currencyid to tell postgresql which row to keep, because they are duplicate. Perhaps an order by ROW_NUMBER (if thats possible) and just keep the lowest ROW_NUMBER? Any suggestions greatly appreciated.

Solution

  • If your table doesn't have id column, the best option could be using temporaray table:

    CREATE TABLE bank_currency_temp AS
SELECT DISTINCT bankid, currencyid
FROM bank_currency;

    After that remove original table

    DROP TABLE bank_currency

    Then rename temp table

    ALTER TABLE bank_currency_temp
RENAME TO bank_currency;