Generate sequence numbers on PostgreSQL integer column

Question

I have a table on PostgreSQL and 1000 rows in it. I have an integer column in my table that is empty. I want to populate newid with sequential, unique numbers a bit like a primary key.

Product
-------------------------
id  name  newid  size
60   ....  null   L
72   ....  null   M
83   ....  null   xl
84   ....  null   S
85   ...

How can I write a query that update my newid column.

Product
-------------------------
id  name  newid  size
60   ....  1      L
72   ....  2      M
83   ....  3      xl
84   ....  4      S
85   ...

Answer 1

You can do this using JOIN or subquery. The idea is to calculate the new id using row_number() and then bring that value into each row:

with newvals (
      select p.*, row_number() over (order by id) as seqnum
      from product p
     )
update product p
    set newid = (select seqnum from newvals nv where nv.id = p.id);