sql - Postgres - 用递增的值更新 CTE

Question

我有一个包含 organization_id 和 name 列的表。我需要为这些列添加一个复合唯一索引，但我们有一些重复的名称需要更新。我可以用这个查询找到有问题的记录:

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 )

我想通过在名称上附加一些递增的值来更新记录(这不是生产数据，不要评判我 :D)。

有点像

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

Answer 1

尝试顺序:

CREATE SEQUENCE public.blahblah;

然后

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

这是安全的，因为

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

和

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.

参见 documentation .