TIL PostgreSQL exposes the physical position of a row in a table by giving each table a hidden ctid
column. No, you can't use this as a substitute for a primary key; anything that causes rows to be rearranged will cause their ctids to change.
Despite this, they remain stable for long enough if you have one of those tables that don't have a proper primary key and end up with duplicate rows where you want to get rid of the duplicates.
DELETE FROM tablename
WHERE ctid NOT IN (
SELECT MIN(ctid)
FROM tablename
GROUP BY tablename.*
)
So, grouping all identical rows together, and selecting the minimum ctid
from reach group. Those are the rows in the table that get kept (one for each group of duplicate rows). Delete all the other rows.
If you have a primary key in your table (as you generally should) then you don't need this; group by the columns other than the primary key, and use max or min or something to pick a representative primary key value. That's assuming you don't mind any dependent rows in other tables being deleted as well. It's more likely you'd want them to repoint to the definitive copy of the row you're keeping.