Re: Multiple inserts with two levels of foreign keys

Dow Drake <dowdrake@xxxxxxxxx> · Wed, 4 Oct 2023 18:50:09 -0700

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