PostgreSQL：如何为随机表SQL新增主键ID和自动插入的时间戳列

已有表新增字段方案 #

如果你已经执行完原建表语句生成了带10条数据的foo表，按以下两步执行即可：

1. 新增col0主键唯一键 #

• PostgreSQL 10及以上版本（推荐）：

ALTER TABLE foo ADD COLUMN col0 INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY;

执行后会自动为已存在的10行数据填充连续的唯一整数值，自带非空、唯一约束。

• PostgreSQL 10以下版本：

ALTER TABLE foo ADD COLUMN col0 SERIAL PRIMARY KEY;

效果和上面的标识列一致，底层会自动创建关联序列完成自增赋值。

2. 新增col5自动填充创建时间字段 #

ALTER TABLE foo ADD COLUMN col5 TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

• 已存在的10行数据的col5会自动填充执行该ALTER语句时的系统时间
• 后续新增行时如果不主动指定col5的值，会自动插入当前行的创建时间，更新其他字段时col5的值不会改变

效果验证 #

执行以下测试插入语句，可验证功能是否正常：

-- 插入新行时无需指定col0和col5
INSERT INTO foo (col1, col2, col3, col4)
VALUES (random(), left(md5(random()::text), 10), md5(random()::text), left(md5(random()::text), 4));
-- 查询全表数据，确认新行的col0自动生成唯一值、col5自动填充插入时间
SELECT * FROM foo;

（可选）建表时直接包含目标字段方案 #

如果还没执行原建表语句，可以直接用以下语句一次性创建符合要求的表，无需后续ALTER修改：

CREATE TABLE foo (
    col0 INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    col1 NUMERIC,
    col2 TEXT,
    col3 TEXT,
    col4 TEXT,
    col5 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) AS 
SELECT 
    random() as col1, 
    left(md5(i::text), 10) as col2,
    md5(random()::text) as col3,
    left(md5(random()::text), 4) as col4
FROM generate_series(1,10) s(i);

内容的提问来源于stack exchange，提问作者Santhosh