Postgres - update CTE with incrementing values

debugcn 投稿 Dev

NoobException

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

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.

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]