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:
1;foo 2;foo 3;foo 4;bar 5;bar 6;anyother
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