CODEWITHSUNDEEP
sqlpostgresql
barteloma
barteloma

Reputation: 6875

Generate sequence numbers on PostgreSQL integer column

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

Upvotes: 2

Views: 6833

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

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

Upvotes: 8

Bohemian
Bohemian

Reputation: 425328

How about:

update mytable set
newid = id + 1000000

Upvotes: 2

Related Questions