PostgreSQL – How to Update Column with Ascending Values

postgresql

I would like to add an order column wich will be position of my record. Positions are connected with name. If the name is S1 – order should be 1, for S2 – order should equal 2 etc. How can I do this?

Real state: 

id       name     position
-------  -------  -------
1        S4       null
2        S2       null
3        S3       null
4        S1       null

Expected: 

id       name     position
-------  -------  -------
1        S4       4
2        S2       2
3        S3       3
4        S1       1

I tried with update:

WITH x AS
(
  SELECT
      id,
      name,
      ROW_NUMBER() OVER (ORDER BY name) position
  FROM
      stage
)
UPDATE
    stage
SET
    position = x.position
FROM
    x
WHERE
    x.id = stage.id;

Stage is the name of the table.

Best Answer

You can use ROW_NUMBER() window function:

WITH x AS
(
  SELECT
      id,
      name,
      ROW_NUMBER() OVER (ORDER BY name) ord
  FROM
      tbl
)
UPDATE
    tbl
SET
    ord = x.ord
FROM
    x
WHERE
    x.id = tbl.id;

SELECT * FROM tbl;

id | name | ord
-: | :--- | --:
 1 | S4   |   4
 2 | S2   |   2
 3 | S3   |   3
 4 | S1   |   1

db<>fiddle here

If there isn't duplicated names you can remove 'S' from name:

UPDATE
    tbl
SET
    position = right(name, length(name) - 1)::int

db<>fiddle here

Related Question