sqlpostgresqlintegerauto-populate

Updating postgres column with sequence of integers

I have a postgres table bar that contains 130 rows. I would like to auto-populate the id column with a sequence of incrementing integers from 1 to 130. When I try the following code:

update bar
set id = t.num FROM (
SELECT  *
FROM    generate_series(1, 130) num) t

The column is updated but every row contains 1. What I am doing wrong here and what is the correct syntax for this procedure?

Solution

  • You need a primary key to identify each row. Then you can use:

    update bar b
   set id = b2.new_id
   from (select b.*, row_number() over (order by id) as new_id
         from bar
        ) b2;
   where b.pk = b2.pk;

    Your version is attempting to update each row 130 times. Only one update is kept -- seemingly the first one but you cannot depend on that.