Add a suffix to duplicates on PostgreSQL Not sure about the term "undouble", I am just trying to add a unique index on a column but it contains doubloons, so I need to update data first.

Figure this data:

BEFORE update

1;foo
2;foo
3;foo
4;bar
5;bar
6;anyother


AFTER update

1;foo0
2;foo1
3;foo2
4;bar0
5;bar1
6;anyother


Note than values without duplicates are unchanged.

My DB is Postgres 9.5.6
You can use ROW_NUMBER function to add a suffix to each repeated value.

If you'd rather avoid the first one (mostly for those rows without duplicates):

with ct as
(
  select id , row_number() over (partition by data order by id) rn
  from   t
)
update t
set    data = data || case when ct.rn = 1 then '' else (ct.rn-1)::text end
from   ct
where  ct.id = t.id;





select * from t;



id | data
-: | :---
 1 | foo 
 2 | foo1
 3 | foo2
 4 | bar 
 5 | bar1



db&lt;>fiddle here