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

Question

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.

Answer 1

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;