CODEWITHSUNDEEP
postgresql
Nanna
Nanna

Reputation: 575

Replace content in 'order' column with sequential numbers

I have an 'order' column in a table in a postgres database that has a lot of missing numbers in the sequence. I am having a problem figuring out how to replace the numbers currently in the column, with new ones that are incremental (see examples).

What I have:

 id  order  name
 ---------------
  1    50   Anna
  2    13   John
  3     2   Bruce
  4     5   David

What I want:

 id  order  name
 ---------------
  1     4   Anna
  2     3   John
  3     1   Bruce
  4     2   David

The row containing the lowest order number in the old version of the column should get the new order number '1', the next after that should get '2' etc.

Upvotes: 0

Views: 298

Answers (1)

user330315
user330315

Reputation:

You can use the window function row_number() to calculate the new numbers. The result of that can be used in an update statement: 

update the_table
   set "order" = t.rn
from (
  select id, row_number() over (order by "order") as rn
  from the_table
) t 
where t.id = the_table.id;

This assumes that id is the primary key of that table.

Upvotes: 1

Related Questions