Re: Multiple inserts with two levels of foreign keys

Dow Drake <dowdrake@xxxxxxxxx> · Wed, 4 Oct 2023 19:15:58 -0700

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

    -- 

      Born in Arizona, moved to Babylonia.