Generate several rows with test data for item-CodePudding

I use this SQL query to generate test data for table 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);

In the second query I generate test data using id from first table 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;

Demo

Right now I generate only 1 row in table onboarding_task_item. How I can generate 4 table rows with test data per one row from onboarding_tasks?

CodePudding user response：

Just CROSS JOIN the result set with a geneate_series() call, which means that all records of the result set will be joined with all records from generate_series(), e.g.

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);

Or perhaps like this if you need the generated value:

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);

Demo: db<>fiddle