将SERIAL列添加到现有的PostgreSQL表中，其值基于ORDER BY排序。

Question

我有一个大表格（超过600万行），我想添加一个自增整数列sid，其中sid在现有行上基于inserted_at ASC排序设置。换句话说，基于inserted_at的最旧记录将设置为1，而最新的记录将是总记录数。您有什么建议可以帮助我解决这个问题吗？

- seanomlor

---

这个表有没有“主键（PRIMARY KEY）”？ - unutbu

是的，在我的特定情况下，主键 id 是一个 UUID。 - seanomlor

Answer 1

添加一个 sid 列并使用 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

请注意，这取决于有一个主键（primary key），即id。（如果您的表没有主键，则必须先创建一个。）

---

例如，

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

产量

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

最后，将sid设置为SERIAL（或者更好的方式是使用IDENTITY列）：

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;

- unutbu

---

我正在使用这个来创建一个新的 id 列。但是这会导致新记录从 id = 1 开始创建，从而在 id 列中创建重复项。如何防止这种情况发生？ - Highmastdon