如何在PostgreSQL或CockroachDB中用常规SQL批量插入测试数据？

一、批量插入测试数据的常规方案 #

1. 用INSERT ... SELECT结合内置生成函数 #

PostgreSQL和CockroachDB都支持通过内置函数批量生成数据，完全不需要手动编写冗长的VALUES列表或者外部循环。

假设你有一张测试表：

CREATE TABLE test_data (
    id INT PRIMARY KEY,
    username TEXT,
    signup_time TIMESTAMP DEFAULT NOW()
);

PostgreSQL 实现

利用generate_series生成连续数值，搭配字符串拼接、随机函数生成测试数据：

-- 生成1000条带递增ID和用户名的基础数据
INSERT INTO test_data (id, username)
SELECT 
    generate_series(1, 1000),
    'test_user_' || generate_series(1, 1000);

如果需要更贴近真实场景的随机数据：

-- 生成带随机注册时间的测试数据
INSERT INTO test_data (id, username, signup_time)
SELECT
    gs.id,
    'test_user_' || gs.id,
    NOW() - (random() * INTERVAL '365 days') -- 随机生成过去一年的时间
FROM generate_series(1, 1000) AS gs(id);

CockroachDB 实现

CockroachDB兼容PostgreSQL的generate_series，同时也支持原生range函数（左闭右开特性）：

-- 用range生成1-1000的递增数据
INSERT INTO test_data (id, username)
SELECT
    range,
    'test_user_' || range
FROM range(1, 1001); -- 需写1001才能覆盖到1000

同样可以结合random()生成随机字段：

INSERT INTO test_data (id, username, signup_time)
SELECT
    r.range,
    'test_user_' || r.range,
    NOW() - (random() * INTERVAL '1 year')
FROM range(1, 1001) AS r(range);

2. 从现有表复制测试数据 #

如果已有数据样本，可直接复制生成批量测试数据：

-- 复制现有表的前1000条数据到测试表
INSERT INTO test_data (username, signup_time)
SELECT username, signup_time FROM existing_user_table LIMIT 1000;

二、不用过程化SQL实现递增数据插入（替代循环） #

你提到的“带递增变量的循环”完全可以用常规SQL替代——上面的generate_series/range方法本质是通过集合运算一次性生成所有需要的行，全程不需要过程化逻辑或外部循环。

如果需要更复杂的递增规则，还可以结合子查询、窗口函数实现：

-- 生成间隔为2的递增ID（1、3、5...共500条）
INSERT INTO test_data (id, username)
SELECT
    1 + (gs.id * 2),
    'test_user_' || (1 + (gs.id * 2))
FROM generate_series(0, 499) AS gs(id);

如果需要基于现有表的最大ID继续生成递增数据：

-- 从现有表的最大ID开始，生成1000条新数据
INSERT INTO test_data (id, username)
SELECT
    max_id + gs.id,
    'test_user_' || (max_id + gs.id)
FROM (SELECT COALESCE(MAX(id), 0) AS max_id FROM test_data) AS t,
     generate_series(1, 1000) AS gs(id);

三、实用注意事项 #

• 批量插入时尽量一次性插入足够多的行，减少事务开销，提升性能。
• 如果表的主键是自增类型（PostgreSQL的SERIAL/IDENTITY、CockroachDB的SERIAL/IDENTITY），可以不用手动指定ID，直接生成其他字段：

-- id为自增主键时，只需生成用户名即可
INSERT INTO test_data (username)
SELECT 'test_user_' || generate_series(1, 1000);

• CockroachDB处理超大规模测试数据时，可考虑使用IMPORT语句，但普通测试场景用上述SQL方法足够。

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