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