Search code examples
postgresqlgenerate-series

Generate table data from other table


I am generating mock data for a table "patients" using the following script:

WITH vars (tenantId, patientCount) as (
  values (5, 30)
)
INSERT INTO patient (tenant_id, id, name)
SELECT 
  tenantId AS tenant_id, 
  gs AS id, 
  CONCAT('Patient_', row_number() OVER ()) AS name 
FROM vars, generate_series(0, 0 + patientCount) AS gs;

Result with:

pk  | tenant_id | id |   name    
------+-----------+----+-----------
 3811 |         5 | 0  | Patient_1
 3812 |         5 | 1  | Patient_2
 3813 |         5 | 2  | Patient_3
 3814 |         5 | 3  | Patient_4
 3815 |         5 | 4  | Patient_5

Next I want to generate data into "treatment" table with these columns:

pk (autogenerated)
id (unique)
patient_pk (from the patient table above)

What approach should I take for this task ?


Solution

  • You could do something like this, to generate data into treatment table

    insert into treatment (id, patient_pk)
    select id::int8, 3811+round(random() * 31)::int8 as patient_pk
    from generate_series(1, 50) as id
    where 3811+round(random() * 31)::int8 in (select pk from patient);
    

    Fiddle to test