updating existing records with a unique integer

问题:

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.

I want to do something like:

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

or maybe

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?

回答1:

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

回答2:

Use some PL/pgSQL code:

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;$$;

Then you can add a unique constraint:

ALTER TABLE foo ADD UNIQUE(ver);

回答3:

First, create a sequence.

 create sequence foo_ver;

then add the column with a default value of the next number in the sequence.

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

data is auto-populated, and new records will automatically get a new, unique number.

Optional: create unique index to be sure.

 create unique index on foo(ver);