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
– Gordon Linoff, Commented Jun 22, 2015 at 11:17
Why should the value L get number 1 and value M the number 2? — Frank Heikens
– Frank Heikens, Commented Jun 22, 2015 at 11:26

Gordon Linoff · Accepted Answer · 2015-06-22 11:36:03Z

8

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

answered Jun 22, 2015 at 11:36

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Bohemian · Accepted Answer · 2015-06-22 11:26:03Z

2

How about:

update mytable set
newid = id + 1000000

answered Jun 22, 2015 at 11:26

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Collectives™ on Stack Overflow

Generate sequence numbers on PostgreSQL integer column

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related