CODEWITHSUNDEEP
sqlpostgresqlcommon-table-expression
NoobException
NoobException

Reputation: 666

Postgres - update CTE with incrementing values

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

Upvotes: 1

Views: 723

Answers (1)

Adam
Adam

Reputation: 5599

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.

Upvotes: 3

Related Questions