From: | Dow Drake <dowdrake(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Multiple inserts with two levels of foreign keys |
Date: | 2023-10-04 23:59:25 |
Message-ID: | CACM7_5Zjm=hdPv9F+74bwocgABTLi_sFDeVgG2H0HFPYD3LR4w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
I'm trying to write a postgresql script to replicate a hierarchical
structure in a live database into my development database, where I can
debug and test more easily. I can extract the data from the live database
that needs to be inserted, but I'm having trouble writing the insertion
script
Here's a simplified version of the problem I'm trying to solve:
There are three tables: farms, crops and deliveries where a farm has many
crops and a crop has many deliveries.
create table farms (
id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
name character varying(30)
);
create table crops (
id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
farm_id bigint not null
name character varying(30)
);
create table deliveries (
id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
crop_id bigint not null
ticket character varying(30)
);
I want to insert a farm record, then insert two crops associated with that
farm, then insert two deliveries for each of the the two crops so that in
the end, my tables look like this:
farms
id name
1 'Happy Valley Farm'
crops
id farm_id name
1 1 'corn'
2 1 'wheat'
delvieries
id crop_id ticket
1 1 '3124'
2 2 '3127'
3 1 '3133'
4 2 '3140'
It's important that the deliveries get assigned to the right crops. I
think this post: https://dba.stackexchange.com/questions/199916
gets close to what I need, but I haven't been able to figure out how to
adapt it to multiple records.
Thanks for any help on this!
From | Date | Subject | |
---|---|---|---|
Next Message | Ron | 2023-10-05 01:25:54 | Re: Multiple inserts with two levels of foreign keys |
Previous Message | Alban Hertroys | 2023-10-04 21:10:38 | Re: Strategies for converting UTC data to local windows for arbitrary resolutions and timezones |