用唯一整数更新现有记录 [英] updating existing records with a unique integer

问题描述

我有一个现有表，其中有记录，我刚刚添加了一个新列ver，我希望它是唯一的.

I have an existing table with records in it and I've just added a new column ver which I would like to be unique.

create table foo (
  bar text,
  ver integer
);

select * from foo;

bar   ver
---   ---
one   null
two   null
three null

由于某种原因，我正在努力做到这一点.

I'm struggling with how to do this for some reason.

我想做类似的事情:

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

也许

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

...但是当然这些都不起作用.谁能为我指出显而易见的地方?

...but of course neither of those work. Can anyone point out the obvious for me?

推荐答案

您需要主键(或唯一列)来单独更新表的行.如果没有这样的列，您可以使用隐藏的列ctid，该列在定义上是唯一的，例如:

You need a primary key (or a unique column) to individually update rows of a table. In the lack of such a column you can use the hidden column ctid which is unique by definition, example:

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

这篇关于用唯一整数更新现有记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！