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 1qoD7p-000VuI-B8 for pgsql-general@arkaria.postgresql.org; Thu, 05 Oct 2023 01:26:01 +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 1qoD7n-007LZr-8m for pgsql-general@arkaria.postgresql.org; Thu, 05 Oct 2023 01:26:00 +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 1qoD7m-007LZe-PP for pgsql-general@lists.postgresql.org; Thu, 05 Oct 2023 01:25:59 +0000 Received: from mail-yw1-x1130.google.com ([2607:f8b0:4864:20::1130]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qoD7k-0004QS-U2 for pgsql-general@lists.postgresql.org; Thu, 05 Oct 2023 01:25:58 +0000 Received: by mail-yw1-x1130.google.com with SMTP id 00721157ae682-59f82ad1e09so4781297b3.0 for ; Wed, 04 Oct 2023 18:25:56 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1696469156; x=1697073956; 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=MAMEJF/Z+W5qKiH0ukCOPZDoMrT6+r+wg2DuZwdwdFU=; b=OjRrcpdAPPeUnGz8Gowo6Lc/kGIfZQVPo1MTkhIhtRGeXpXrsM/pbfcWCo9Qbqp0OB mxIyAbCXvtXRW76+R8ptCvXuJrOf8vXof0sqFQ7Y+kzGnmfmprCkWYUfg5Oy0uUQxubU s14Yw42C8vju6EWcVGv1wbXO7zaC/1caXhP4ax/2pWOwT3r6Tth0jzc/WwupzEC+65ZE ot9L5SkJfNMqbJJJUzc6sQGw7oqO2i163g7BQAuVoNsPEIvRwFxx1tdLzby1xGMdgfSQ aI+za6sYzdPmnhwvArpsyE9F/C9iDaJ3a8um4GyTmx9NUP9vfzF8e6UZdHue14LF3tiR SbgQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1696469156; x=1697073956; 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=MAMEJF/Z+W5qKiH0ukCOPZDoMrT6+r+wg2DuZwdwdFU=; b=HlGefCUL31mYOpzEen8lxITIfx20McnFu9F2xBCfLQLg90752bgR0lMa2WGdxHRorS cIybKPYe9W492CMwW+mSrpT3fU3D2Ceaa63v+xNbtcqX3RU+Ru65KvC4oAEC3E2eQgLu KEiXe9T56HvCzumLrFT8M3O5VNaOwJVTTOMQ1joP7Z9J/gvLQQOhDtHpmVolyxvVTFOm +l+k8jBRHIS0hncb1bHsX0+r6IANFty/saf9CneqFrQcXsaxMTYFRIWsXHa6/L7TepnF eT2CMcf2caam/06WkiJTW7AxDOXqNl3+GZuEwzaTtWWRx/xY0eyb5Qh90sL24hw2Vzak klUA== X-Gm-Message-State: AOJu0YyaqxAW9+fV0Js/sWN+eHdFrQfk3feHeBG1OeVGNdmUDnJoeYoh xfwU88GJwRTzLI8ECh1KlULFFEPi+RU= X-Google-Smtp-Source: AGHT+IHsktwrgTtBfdTAjJmPg+izoBksO4hhK+4o3JLSKPQAfyvO5orEmvR4kiUiY92C/Veon4elLw== X-Received: by 2002:a0d:f985:0:b0:59f:21c1:30b6 with SMTP id j127-20020a0df985000000b0059f21c130b6mr3676095ywf.51.1696469155769; Wed, 04 Oct 2023 18:25:55 -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 x132-20020a817c8a000000b005925c896bc3sm153571ywc.53.2023.10.04.18.25.55 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 04 Oct 2023 18:25:55 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------kWxPoWuYRhWDOHsEwVo2u37W" Message-ID: Date: Wed, 4 Oct 2023 20:25:54 -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. --------------kWxPoWuYRhWDOHsEwVo2u37W Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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. --------------kWxPoWuYRhWDOHsEwVo2u37W Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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.
--------------kWxPoWuYRhWDOHsEwVo2u37W--