Re: Multiple inserts with two levels of foreign keys

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 <ronljohnsonjr(at)gmail(dot)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.