Delete duplicate rows in relationship table

Question

I have a relationship table in a PostgreSQL database. I want to delete duplicate rows.

My table looks like this:

idUser      idFunction
1           1
2           1
3           1
3           1
4           1
4           1
4           2

And I want this:

idUser      idFunction
1           1
2           1
3           1
4           1
4           2

I have already tried this :

DELETE TOP (SELECT COUNT(*) -1 FROM user_function 
        WHERE idUser IN
            (SELECT idUser FROM user_function
            GROUP BY idUser, idFunction
            HAVING COUNT(*) > 1)
        AND idFunction IN
            (SELECT idFunction FROM user_function
            GROUP BY idUser, idFunction
            HAVING COUNT(*) > 1))
FROM user_function
WHERE idUser IN
    (SELECT idUser FROM user_function
    GROUP BY idUser, idFunction
    HAVING COUNT(*) > 1)
AND idFunction IN
    (SELECT idFunction FROM user_function
    GROUP BY idUser, idFunction
    HAVING COUNT(*) > 1)

But PostgreSQL tells me that there is an error at 'TOP'.

How can I delete duplicate rows in a relationship table in PostgreSQL?

score 0 · Answer 1 · answered Dec 04 '19 at 15:30

I might suggest just recreating the table:

create table temp_user_function as
    select distinct idUser, idFunction
    from user_function;

truncate table user_function;   -- backup first!!!

insert into user_function (idUser, idFunction)
    select idUser, idFunction
    from temp_user_function;

You should declare the pair to be unique when you have fixed the data.

Delete duplicate rows in relationship table

1 Answers1