0

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

1

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

  • Thanks you that sound perfects! – Slim Aug 29 at 15:32
  • How may I generate the update from this select? – Slim Aug 29 at 15:36
  • I need to update data yes – Slim Aug 29 at 15:38
  • wow, what a professionalism.. You won't remember it (you said it on your description), but I will :) gracias amigo – Slim Aug 29 at 15:52
  • 1
    I'm glad to help – McNets Aug 29 at 15:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.