如何使用递增值进行更新

I have a table in PostgreSQL that has an ID column that is supposed to be unique. However, a large number of the rows (around 3 million) currently have an ID of "1".

我在PostgreSQL中有一个表,它有一个应该是唯一的ID列。但是,大量行(约300万)目前的ID为“1”。

What I know:

我知道的:

The total number of rows

总行数

The current maximum value for the ID column

ID列的当前最大值

The number of rows with an (incorrect) ID of "1"

(错误)ID为“1”的行数

What I need is a query that will pull all the rows with an ID of "1" and assign them a new ID that increments automatically so that every row in the table will have a unique ID. I'd like it to start at the currentMaxId + 1 and assign each row the subsequent ID.

我需要的是一个查询,它将提取ID为“1”的所有行,并为它们分配一个自动递增的新ID,以便表中的每一行都有一个唯一的ID。我希望它从currentMaxId + 1开始,并为每一行分配后续ID。

This is the closest I've gotten with a query:

这是我最接近查询的:

UPDATE table_name
SET id = (
    SELECT max(id) FROM table_name
) + 1
WHERE id = '1'

The problem with this is that the inner SELECT only runs the first time, thus setting the ID of the rows in question to the original max(id) + 1, not the new max(id) + 1 every time, giving me the same problem I'm trying to solve.
Any suggestions on how to tweak this query to achieve my desired result or an alternative method would be greatly appreciated!

这个问题是内部SELECT只运行第一次,因此每次都将相关行的ID设置为原始max(id)+ 1,而不是新的max(id)+ 1,给我相同的问题我正在努力解决。任何关于如何调整此查询以实现我想要的结果或替代方法的建议将不胜感激!

2 个解决方案

#1

You may do it step by step with a temporary sequence.

您可以使用临时序列逐步执行此操作。

1) creation

create temporary sequence seq_upd;

2) set it to the proper initial value

2)将其设置为正确的初始值

select setval('seq_upd', (select max(id) from table_name));

3) update

update table_name set id=nextval('seq_upd') where id=1;

#2

If you are going to work with a SEQUENCE, consider the serial pseudo data type for you id. Then you can just draw nextval() from the "owned" (not temporary) sequence, which will then be up to date automatically.

如果您打算使用SEQUENCE,请考虑您的序列伪数据类型。然后你可以从“拥有”(非临时)序列中绘制nextval(),然后自动更新。

If you don't want that, you can fall back to using the ctid and row_number() for a one-time numbering:

如果您不想这样,您可以回退使用ctid和row_number()进行一次性编号:

UPDATE tbl t
SET    id = x.max_id + u.rn
FROM  (SELECT max(id) AS max_id FROM tbl) x
    , (SELECT ctid, row_number() OVER (ORDER BY ctid) AS rn
       FROM   tbl WHERE id = 1) u
WHERE  t.ctid = u.ctid;

Related answer on dba.SE:
numbering rows consecutively for a number of tables

关于dba.SE的相关答案:连续为多个表编号行

#1