使用唯一整数更新现有记录

Question

我有一个包含记录的现有表，我刚刚添加了一个新的列ver我希望它是唯一的。

create table foo (
  bar text,
  ver integer
);
select * from foo;
bar   ver
---   ---
one   null
two   null
three null

出于某种原因，我正在为如何做到这一点而苦苦挣扎。

我想做这样的事情：

update foo set ver = ( select generate_series(1, 1000) );

或者也许

update foo set ver = v from (select generate_series(1, 1000) as v );

。但当然，这些都不起作用。 谁能为我指出显而易见的事情？

Answer 1

您需要一个主键（或唯一列）来单独更新表的行。在缺少此类列的情况下，您可以使用隐藏列ctid，根据定义，该列是唯一的，例如：

update foo f
set ver = rn
from (
    select ctid, row_number() over (order by ctid) as rn
    from foo
    ) s
where f.ctid = s.ctid

Answer 2

使用一些 PL/pgSQL 代码：

DO
$$DECLARE
   c CURSOR FOR SELECT * FROM foo;
   x foo;
   i integer := 1;
BEGIN
   OPEN c;
   LOOP
      FETCH c INTO x;
      EXIT WHEN NOT FOUND;
      UPDATE foo SET ver = i WHERE CURRENT OF c;
      i := i + 1;
   END LOOP;
END;$$;

然后，您可以添加唯一约束：

ALTER TABLE foo ADD UNIQUE(ver);

Answer 3

首先，创建一个序列。

 create sequence foo_ver;

然后添加具有序列中下一个数字的默认值的列。

 alter table foo add ver integer default nextval('foo_ver') not null;

数据会自动填充，新记录将自动获得新的唯一编号。

可选：创建唯一索引以确保。

 create unique index on foo(ver);