如何为含多UUID列的PostgreSQL表生成10-20K测试数据

核心思路 #

利用PostgreSQL内置函数直接生成符合要求的各类数据，重点解决UUID类型的生成和重复控制问题，无需额外编写复杂脚本。

完整SQL脚本（PostgreSQL 13+） #

INSERT INTO student_services (id, mobile_type, migration_id, service_type, s_config, subs_id, expire_at)
SELECT
  gen_random_uuid() AS id, -- 生成唯一主键UUID
  CASE floor(random() * 3)
    WHEN 0 THEN 'Android'
    WHEN 1 THEN 'iOS'
    WHEN 2 THEN 'HarmonyOS'
  END AS mobile_type,
  -- 按随机分组复用migration_id，控制重复率（分组数越小，重复率越高）
  (SELECT gen_random_uuid() FROM generate_series(1,1) LIMIT 1) AS migration_id
  OVER (PARTITION BY floor(random() * 1000)),
  CASE floor(random() * 3)
    WHEN 0 THEN 'cloud_storage'
    WHEN 1 THEN 'online_course'
    WHEN 2 THEN 'exam_service'
  END AS service_type,
  jsonb_build_object(
    'quota', floor(random() * 10000),
    'auto_renew', random() > 0.5,
    'max_devices', floor(random() * 5) + 1
  ) AS s_config,
  -- 30%概率生成subs_id，其余为NULL
  CASE WHEN random() < 0.3 THEN gen_random_uuid() ELSE NULL END AS subs_id,
  -- 生成当前时间前后1年范围内的随机时间戳
  CURRENT_TIMESTAMP + (random() * INTERVAL '2 years') - INTERVAL '1 year' AS expire_at
FROM generate_series(1, 15000); -- 此处可替换为10000-20000之间的任意数字

适配旧版本PostgreSQL（12及以下） #

如果使用PostgreSQL 12或更早版本，需先启用UUID扩展再替换生成函数：

-- 启用UUID扩展
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- 适配后的插入脚本
INSERT INTO student_services (id, mobile_type, migration_id, service_type, s_config, subs_id, expire_at)
SELECT
  uuid_generate_v4() AS id,
  CASE floor(random() * 3)
    WHEN 0 THEN 'Android'
    WHEN 1 THEN 'iOS'
    WHEN 2 THEN 'HarmonyOS'
  END AS mobile_type,
  (SELECT uuid_generate_v4() FROM generate_series(1,1) LIMIT 1) AS migration_id
  OVER (PARTITION BY floor(random() * 1000)),
  CASE floor(random() * 3)
    WHEN 0 THEN 'cloud_storage'
    WHEN 1 THEN 'online_course'
    WHEN 2 THEN 'exam_service'
  END AS service_type,
  jsonb_build_object(
    'quota', floor(random() * 10000),
    'auto_renew', random() > 0.5,
    'max_devices', floor(random() * 5) + 1
  ) AS s_config,
  CASE WHEN random() < 0.3 THEN uuid_generate_v4() ELSE NULL END AS subs_id,
  CURRENT_TIMESTAMP + (random() * INTERVAL '2 years') - INTERVAL '1 year' AS expire_at
FROM generate_series(1, 15000);

自定义调整说明 #

• 行数控制：修改generate_series(1, N)中的N为10000-20000之间的目标行数。
• migration_id重复率：调整PARTITION BY floor(random() * X)中的X，X越小，同一migration_id对应的行数越多，重复率越高。
• 字段可选值：可修改mobile_type、service_type的CASE分支内容，匹配实际业务枚举值。
• s_config结构：调整jsonb_build_object的参数，生成符合业务需求的JSONB配置内容。

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