Have you considered writing a stored procedure to process records that have
been written to temporary tables?
0. Create temporary tables tmp_farms, tmp_crops and tmp_deliveries, which
don't have id columns.
1. Truncate the three temporary tables
2. Insert into the temp tables a "set" of prod data.
3. Call a stored procedure in the dev database that does INSERT INTO ...,
using RETURNING to get the relevant id values for the subsequent tables.
4. goto 1.
On 10/4/23 21:15, Dow Drake wrote:
I see. That would definitely work, but part of this for me is to get a
better understanding of PostgreSQL's capabilities. I'm going to keep
working on a minimal solution that deletes no records from the dev
database, and only inserts the required records.
On Wed, Oct 4, 2023 at 6:58 PM Ron <ronljohnso...@gmail.com> wrote:
Ah. We'd truncate all of the dev tables, then load a "slice" (for
example, accounts 10000 to 19999, and all associated records from
downstream tables; lots and lots of views!!) from the prod database.
On 10/4/23 20:50, Dow Drake wrote:
Thanks for the reply, Ron!
I'm not sure I see how to make your suggestion work, though. Suppose
I dump the three tables to CSV as you suggest (and write a script to
extract the relevant records from those CSV dumps in the correct
order). It might be that in the dev database, the next generated key
values are 199 for farm's id, 2145 for crop's id and 10242 for
deliveries' id. The databases are independent.
Just inserting the records in the same order doesn't take care of
setting the foreign key values correctly -- does it? I think I'm
really looking for a solution more along the lines of the link in my
original post.
Best,
Dow
On Wed, Oct 4, 2023 at 6:26 PM Ron <ronljohnso...@gmail.com> wrote:
Frame challenge: why can't you just "\copy to" the dev database
tables in the correct order, to satisfy foreign key requirements?
On 10/4/23 18:59, Dow Drake wrote:
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!
--
Born in Arizona, moved to Babylonia.
--
Born in Arizona, moved to Babylonia.
--
Born in Arizona, moved to Babylonia.
