Postgres - update CTE with incrementing values

Question

I have a table with organization_id and name columns. I need to add a composite unique index to those columns, but we have some duplicate names that need to be updated. I can find the offending records with this query:

with cte as (select l.id, t.*
from locations l
join (
  select name, organization_id, count(*) as qty
  from locations
  group by name, organization_id
  having count(*) > 1
) t on l.name = t.name and l.organization_id = t.organization_id )

I would like to update the records by appending some some incrementing values to the names (this isn't production data, don't judge me :D).

something like

update locations
set name = locations.name || (select i from generate_series(1..some_count))::text
from cte

Adam · Accepted Answer · 2018-04-12 21:53:35Z

3

Try with sequence:

CREATE SEQUENCE public.blahblah;

Then

UPDATE
    locations
SET
    name = locations.name || nextval('public.blahblah')
FROM
    cte
WHERE
    locations.id = cte.id;

It's safe because

even if multiple sessions execute nextval concurrently, each will safely receive a distinct sequence value

and

To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value has been fetched it is considered used and will not be returned again. This is true even if the surrounding transaction later aborts, or if the calling query ends up not using the value.

See documentation.

edited Apr 12, 2018 at 21:53

answered Apr 12, 2018 at 20:59

Adam

5,6296 gold badges35 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

NoobException Over a year ago

This worked, just had to add a where locations.id = cte.id after the from.

Adam Over a year ago

It's always a pleasure to help ;) I've added where clause to the answer.

Collectives™ on Stack Overflow

Postgres - update CTE with incrementing values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related