Re: Multiple inserts with two levels of foreign keys

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