使用项的测试数据生成多行

Question

我使用这个SQL查询为表onboarding_tasks生成测试数据。

INSERT into onboarding_tasks (business_name, meta_title, status, task_type)
SELECT
    'Business name ' || id AS business_name,
    left (md5(random()::text), 10) AS meta_title,
    (ARRAY['NEW','IN_PROGRESS','COMPLETED'])[floor(random()*3)+1] AS status,
    (ARRAY['CHECK', 'TEST'])[floor(random()*3)+1] AS task_type
FROM generate_series(1,25) as g(id);

在第二个查询中，我使用id从第一个表onboarding_tasks生成测试数据。

INSERT into onboarding_task_item (title, task_id, onboarding_tasks)
SELECT
    left (md5(random()::text), 10) AS title,
    ot.id,
    generate_series(1,50) AS onboarding_tasks
FROM onboarding_tasks ot;

演示

现在，我只在表onboarding_task_item中生成1行。如何从onboarding_tasks生成每一行测试数据的4行表

Answer 1

只需使用CROSS JOIN调用结果集，这意味着结果集的所有记录都将与来自generate_series()的所有记录连接。

SELECT     
  left (md5(random()::text), 10) AS title,     
  ot.id,       
  generate_series(1,5) AS onboarding_tasks 
FROM onboarding_tasks ot 
CROSS JOIN generate_series(1,4);

如果需要生成的值，也可以这样做：

SELECT
    left (md5(random()::text), 10) AS title,
    ot.id,
    j.id AS gen_id
FROM onboarding_tasks ot
CROSS JOIN generate_series(1,4) j(id);

演示：db<>fiddle