Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qoE94-000Ysy-JI for pgsql-general@arkaria.postgresql.org; Thu, 05 Oct 2023 02:31:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qoE91-0080Gx-NK for pgsql-general@arkaria.postgresql.org; Thu, 05 Oct 2023 02:31:20 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qoE91-0080Go-3z for pgsql-general@lists.postgresql.org; Thu, 05 Oct 2023 02:31:20 +0000 Received: from mail-yw1-x112e.google.com ([2607:f8b0:4864:20::112e]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qoE8y-0004p7-T4 for pgsql-general@lists.postgresql.org; Thu, 05 Oct 2023 02:31:18 +0000 Received: by mail-yw1-x112e.google.com with SMTP id 00721157ae682-59f4f80d084so4691597b3.1 for ; Wed, 04 Oct 2023 19:31:16 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1696473076; x=1697077876; darn=lists.postgresql.org; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=yeEbW4osgzASRtYuqYvQeAvYeCA5XgcGnwBv0J2IZ8A=; b=WAAsbqP+dYHvcI2vdOPevWIds44KEB2hFVE6u1KCLZ/tTayVcd1U0DVFvak8H124Ym +dCACKy8JeDWk0Orz7lfo+uT02Xj1vZN/UskgkiNiFbuxqN+vn5d88A4hx3YQ98HpXOa sN2wGtYYNGbb5ArB1U4oVPNBsfjFLogXj53dB645deGUTRlq216FJwqf7H5KaQnZ2B7N bcEcXowOoX39QFv2vD2A5zw1efBUdssY9QCaPJfhwyIJ1xR0EhsU0HSfxi6S4JcyjE5b BZHJvWlPXlqil66pvdSIYseioG517/EujlObCjNvspq8gKAM3Lmnuyrz74qadug9Bapd 1oow== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1696473076; x=1697077876; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=yeEbW4osgzASRtYuqYvQeAvYeCA5XgcGnwBv0J2IZ8A=; b=OmCSeGIoo9Qf7pwb8hJ8Jsk17Y6LSFO9F2Wsmv5vN2cR8JcWUlskryOOKOVkGE6Za+ 4nOLHZpU4vwcwSaA4NZxvhqUIbcp6EHOX9GjSSRHmgfSu3TuUFQjxk2yxVKduUvr6v/w U6FdnQfICS8V8RPgmEufW/ZhA+lKgCM8qePOqvj7NQ2TwsmWvO9XAFNrRclq25kNBnOo r2e045RpUFldghUDq5QCDvJ5SCFuBuBFpcxNiEof4/03zvEbwTqIOrr/IhbOTgXqp8/t Z+xVt507UXRkGzayNVxsyD4igD27s1mKZT/W8wPnQJX5cC4vSEc0b65R2mXbfisd7t80 YIyA== X-Gm-Message-State: AOJu0Yy737HJ1dW7w8LHER/43lehVJEot/Su9q28sufZrxXuU03yRcyI Ci15ZORv9v/SAv0dKZ82rezu1/gaHOE= X-Google-Smtp-Source: AGHT+IH7jZOz1142n+72CW+N8WOMY14MheD+IbG0LCgAuUjp8dY6qVYlADOwPwGx2MMBxFFHDEg9iA== X-Received: by 2002:a81:6c0a:0:b0:5a0:e86e:fd5c with SMTP id h10-20020a816c0a000000b005a0e86efd5cmr4396237ywc.0.1696473075783; Wed, 04 Oct 2023 19:31:15 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:25a5:fc13:c829:adda? ([2600:1700:bd2c:b810:25a5:fc13:c829:adda]) by smtp.googlemail.com with ESMTPSA id w130-20020a814988000000b0059c0629d59csm177986ywa.115.2023.10.04.19.31.15 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 04 Oct 2023 19:31:15 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------MEBCeOIJUHbLJSndi6IzxcAv" Message-ID: <01d36161-6e20-6bef-41c0-9bdb11e50218@gmail.com> Date: Wed, 4 Oct 2023 21:31:15 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.11.0 Subject: Re: Multiple inserts with two levels of foreign keys Content-Language: en-US To: pgsql-general@lists.postgresql.org References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------MEBCeOIJUHbLJSndi6IzxcAv Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Have you considered writing a stored procedure to process records that have been written to temporary tables? 0. Create temporary tables tmp_farms, tmp_crops and tmp_deliveries, which don't have id columns. 1. Truncate the three temporary tables 2. Insert into the temp tables a "set" of prod data. 3. Call a stored procedure in the dev database that does INSERT INTO ..., using RETURNING to get the relevant id values for the subsequent tables. 4. goto 1. On 10/4/23 21:15, Dow Drake wrote: > 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 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 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. > -- Born in Arizona, moved to Babylonia. --------------MEBCeOIJUHbLJSndi6IzxcAv Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit Have you considered writing a stored procedure to process records that have been written to temporary tables?

0. Create temporary tables tmp_farms, tmp_crops and tmp_deliveries, which don't have id columns.
1. Truncate the three temporary tables
2. Insert into the temp tables a "set" of prod data.
3. Call a stored procedure in the dev database that does INSERT INTO ..., using RETURNING to get the relevant id values for the subsequent tables.
4. goto 1.


On 10/4/23 21:15, Dow Drake wrote:
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@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 <ronljohnsonjr@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.

--
Born in Arizona, moved to Babylonia.
--------------MEBCeOIJUHbLJSndi6IzxcAv--