xxxxxxxxxx
delete duplicate rows in psql
xxxxxxxxxx
WITH duplicates AS (
SELECT column1, column2, , columnN, ROW_NUMBER() OVER(PARTITION BY column1, column2, , columnN ORDER BY id) AS row_num
FROM your_table
)
DELETE FROM your_table
WHERE (column1, column2, , columnN, id) IN (
SELECT column1, column2, , columnN, id
FROM duplicates
WHERE row_num > 1
);
xxxxxxxxxx
DELETE FROM
basket a
USING basket b
WHERE
a.id < b.id
AND a.fruit = b.fruit;
xxxxxxxxxx
DELETE FROM sf_table
WHERE id IN
(SELECT id
FROM
(SELECT id,
ROW_NUMBER() OVER( PARTITION BY tosp, icd
ORDER BY id ) AS row_num
FROM sf_table ) t
WHERE t.row_num > 1 );