How to remove duplicate rows in postresql?

Question

PostgreSQL

Hello.
I have a table which has a field whose value can be repeated in different rows. I need to leave only one line with this value.
Please tell me how to do it?
Thanks in advance to all who respond.

Answer 1

I need to leave only one line with this value. Please tell me how to do it?

And how does PostgreSQL guess which one?
Answer: WHERE in DELETE.
In light of the many options for answering the highlighted question, I give the correct answer :

DELETE FROM TestTable
WHERE (f1 = 'НужноУдалить') AND (ID <> НужноОставить)

Answer 2

The canvas is something like this:
there is ip - primary key and dup_fld - a field that could be duplicated
1. select dup_fld from table group by dup_fld having count(*)>1 - these are duplicates as such
2. select max(id) from table group by
dup_fld having count(*)>1 is id (unique, last) in
duplicates addition and the latest addition is the most correct, but you can take min - then "leave the very first" or develop the design to the desired logic (such as the price is closest to the average, etc.)
Well, how to implement it is a matter of taste and compromise with the optimizer. I would do 2 cte - first inner join "cut" the set down to only duplicates, second - left join where cte2.id is null

with cte_dbls(dup_fld) as (select dub_fld from table group by dup_fld having count(dup_fld)>1),
cte_ones( id ) as ( select max(id)  from table group by dup_fld having count(dup_fld)>1)

--delete from table where id in (

select 
id
from table
inner join cte_dbls on cte_dbls.dup_fld=table.dup_fld
left join cte_ones on cte_ones.id=table.id
where cte_ones.id is null
--)

For convenience, the deletion itself is commented out to see the result of the select, otherwise you can get rid of the where in construct