PostgreSQL add SERIAL column to existing table with values based on ORDER BY

Add a sid column and UPDATE SET ... FROM ... WHERE:

UPDATE test
SET sid = t.rownum
FROM (SELECT id, row_number() OVER (ORDER BY inserted_at ASC) as rownum
    FROM test) t
WHERE test.id = t.id

Note that this relies on there being a primary key, id. (If your table did not already have a primary key, you would have to make one first.)

For example,

-- create test table
DROP TABLE IF EXISTS test;
CREATE TABLE test (
    id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
    , foo text
    , inserted_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO test (foo, inserted_at) VALUES
('XYZ', '2019-02-14 00:00:00-00')
, ('DEF', '2010-02-14 00:00:00-00')
, ('ABC', '2000-02-14 00:00:00-00');

-- +----+-----+------------------------+
-- | id | foo |      inserted_at       |
-- +----+-----+------------------------+
-- |  1 | XYZ | 2019-02-13 19:00:00-05 |
-- |  2 | DEF | 2010-02-13 19:00:00-05 |
-- |  3 | ABC | 2000-02-13 19:00:00-05 |
-- +----+-----+------------------------+

ALTER TABLE test ADD COLUMN sid INT;

UPDATE test
SET sid = t.rownum
FROM (SELECT id, row_number() OVER (ORDER BY inserted_at ASC) as rownum
    FROM test) t
WHERE test.id = t.id

yields

+----+-----+------------------------+-----+
| id | foo |      inserted_at       | sid |
+----+-----+------------------------+-----+
|  3 | ABC | 2000-02-13 19:00:00-05 |   1 |
|  2 | DEF | 2010-02-13 19:00:00-05 |   2 |
|  1 | XYZ | 2019-02-13 19:00:00-05 |   3 |
+----+-----+------------------------+-----+

Finally, make sid SERIAL (or, better, an IDENTITY column):

ALTER TABLE test ALTER COLUMN sid SET NOT NULL;
-- IDENTITY fixes certain issue which may arise with SERIAL
ALTER TABLE test ALTER COLUMN sid ADD GENERATED BY DEFAULT AS IDENTITY;
-- ALTER TABLE test ALTER COLUMN sid SERIAL;