PostgreSQL中如何向带检查约束的列批量插入指定枚举值

现有一张名为ar的表，operation列通过检查约束限制只能插入特定值：('C', 'R', 'RE', 'M', 'P')。需要向表中插入100万条测试数据，但当前使用generate_series()生成随机值的插入语句违反了约束，导致报错。需要调整插入逻辑，确保operation列仅插入合法值。

建表语句 #

CREATE TABLE ar (
  mappingId TEXT,
  actionRequestId integer,
  operation text,
  CONSTRAINT chk_operation CHECK (operation IN ('C', 'R', 'RE', 'M', 'P'))
);

原插入语句 #

INSERT INTO ar (mappingId, actionRequestId, operation)
SELECT substr(md5(random()::text), 1, 10),
       (random() * 70 + 10)::integer,
       substr(md5(random()::text), 1, 10)
FROM generate_series(1, 1000000);

报错信息 #

ERROR: new row for relation "ar" violates check constraint "chk_operation"

核心思路是从合法值集合中随机选取元素，而非生成无意义的随机字符串。以下是几种高效的实现方式：

方法1：数组随机索引取值 #

将合法值存入数组，通过random()生成对应索引来随机选取元素，这是最直观高效的方式：

INSERT INTO ar (mappingId, actionRequestId, operation)
SELECT 
  substr(md5(random()::text), 1, 10),
  (random() * 70 + 10)::integer,
  (ARRAY['C', 'R', 'RE', 'M', 'P'])[floor(random() * 5 + 1)::integer]
FROM generate_series(1, 1000000);

• 原理：数组索引从1开始，random()*5生成0~4的随机数，加1后转为整数，刚好匹配数组的5个元素索引。

方法2：CASE语句映射随机区间 #

通过random()生成的数值区间，映射到对应的合法值，还能灵活控制各值的出现概率：

INSERT INTO ar (mappingId, actionRequestId, operation)
SELECT 
  substr(md5(random()::text), 1, 10),
  (random() * 70 + 10)::integer,
  CASE 
    WHEN random() < 0.2 THEN 'C'
    WHEN random() < 0.4 THEN 'R'
    WHEN random() < 0.6 THEN 'RE'
    WHEN random() < 0.8 THEN 'M'
    ELSE 'P'
  END
FROM generate_series(1, 1000000);

• 调整提示：如果需要某值出现概率更高，可修改区间阈值（比如想让'C'占30%，就把第一个条件改为random() < 0.3）。

方法3：TABLE子查询随机选取 #

适合合法值较多或需要动态调整的场景，通过构造合法值集合随机取值：

INSERT INTO ar (mappingId, actionRequestId, operation)
SELECT 
  substr(md5(random()::text), 1, 10),
  (random() * 70 + 10)::integer,
  op.operation
FROM generate_series(1, 1000000)
CROSS JOIN LATERAL (
  SELECT operation FROM (VALUES ('C'), ('R'), ('RE'), ('M'), ('P')) AS op(operation)
  ORDER BY random() LIMIT 1
);

• 性能说明：该方式灵活性更高，但性能略低于前两种，不过插入100万条数据依然可以快速完成。

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