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

Please elaborate. It is unclear what you are trying to do. Sample data and desired results can be a big help. — Gordon Linoff, Jun 22 '15 at 11:17
Why should the value L get number 1 and value M the number 2? — Frank Heikens, Jun 22 '15 at 11:26
possible duplicate of [PostgreSQL Autoincrement](http://stackoverflow.com/questions/787722/postgresql-autoincrement) — Stavr00, Jun 22 '15 at 14:53

score 6 · Answer 1 · answered Jun 22 '15 at 11:36

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

score 2 · Answer 2 · answered Jun 22 '15 at 11:26

2

How about:

update mytable set
newid = id + 1000000

answered Jun 22 '15 at 11:26

Bohemian

412,405
93
575
722

Generate sequence numbers on PostgreSQL integer column

2 Answers2