Add a suffix to duplicates on PostgreSQL

Posted on

Question :

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

Answer :

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<>fiddle here

Leave a Reply

Your email address will not be published. Required fields are marked *