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. >
