Update each row with incremental value Postgres

Question

I want to update every row in a table in Postgres and set each row to a different value; this value is gonna be an incremental value with a start value.

For instance, suppose I have table tab_a with the following data:

|attr_a|attr_b|
|1     |null  |
|2     |null  |
|3     |null  |
|4     |null  |

The output I might want is:

|attr_a|attr_b|
|1     |5     |
|2     |6     |
|3     |7     |
|4     |8     |

Here is my script:

UPDATE tab_a
SET attr_b = gen.id
FROM generate_series(5,8) AS gen(id);

However is not working as expected...

Answer 1

You could do

UPDATE tab_a upd
SET attr_b = row_number + 4 -- or something like row_number + (select max(attr_a) from tab_a)
FROM (
  SELECT attr_a, row_number() over ()
  FROM tab_a
  ORDER BY 1
  ) a
WHERE upd.attr_a = a.attr_a;

Answer 2

Do something like this

UPDATE pr_conf_item upd
SET item_order = a.row_number
FROM (
       SELECT id, row_number() over ()
       FROM pr_conf_item
       ORDER BY 1
     ) a
WHERE upd.id = a.id;