Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tasks (id int, "order" text);
INSERT INTO tasks(id) VALUES (32), (21), (53);  -- arbitrary order
3 rows affected

TABLE tasks;


id
order


32
null

21
null

53
null


CREATE TEMP SEQUENCE foo_seq;

UPDATE tasks
SET    "order" = 'p/' || nextval('foo_seq')
RETURNING *;


id
order


32
p/1

21
p/2

53
p/3


ALTER TABLE tasks
  DROP COLUMN "order"
, ADD COLUMN  "order" serial;

TABLE tasks;


id
order


32
1

21
2

53
3


-- sort by id
UPDATE tasks t
SET    "order" = t1.rn
FROM  (SELECT id, row_number() OVER (ORDER BY id)  AS rn FROM tasks) t1
WHERE  t.id = t1.id
RETURNING t.*;


id
order


21
1

32
2

53
3

id	order
32	null
21	null
53	null