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...

@MightyRearranger attr_a is my id auto incremental, and not always will start with 1 like that, I just gave an example. — Rogger Fernandes
– Rogger Fernandes, Commented May 10, 2016 at 16:16
I'm guessing there are other fields in the table and you want the new values to be in a certain order. If so then you can use 'Rank() Over (order by x, y, z) as RANKING' to get an integer value and use it to generate the series you want. — MightyRearranger
– MightyRearranger, Commented May 10, 2016 at 16:21

Ezequiel Tolnay · Accepted Answer · 2016-05-11 09:03:38Z

11

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;

answered May 11, 2016 at 9:03

Ezequiel Tolnay

4,5821 gold badge21 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Samuil Petrov · Accepted Answer · 2018-04-25 13:10:18Z

1

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;

edited Apr 25, 2018 at 13:10

Samuil Petrov

5631 gold badge14 silver badges24 bronze badges

answered Apr 25, 2018 at 12:26

Fatema Fairoz Naowar

292 bronze badges

Collectives™ on Stack Overflow

Update each row with incremental value Postgres

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related