-------- CREATE TABLE T (name varchar(10)); INSERT INTO T VALUES ('John'), ('Smith'), ('John'), ('Smith'), ('Smith'), ('Tom'); --------------------first method deleting duplicate WITH t_deleted AS (DELETE FROM T returning *), -- 1st step t_inserted AS (SELECT name, ROW_NUMBER() OVER(PARTITION BY name ORDER BY name) rnk FROM t_deleted) -- 2nd step INSERT INTO T SELECT name FROM t_inserted WHERE rnk=1; -- 3rd step --(here we move the search condition from the step 2 to reduce the code) SELECT * FROM T; -------------------------second method select "name",row_number()over(partition by name) into t_duplicate_fixed from t; delete from t_duplicate_fixed where row_number >1 -------------------------third method delete from t where ctid in (select ctid from (select ctid,"name",row_number()over(partition by name)as rn from t) t2 where rn >1) ---------------------------------------4th method DELETE FROM t a USING ( SELECT MIN(ctid) as ctid, name,count(*) FROM t GROUP BY name HAVING COUNT(*) > 1 ) b WHERE a.name = b.name AND a.ctid <> b.ctid
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter