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 1qoPQN-0019RH-N4 for pgsql-general@arkaria.postgresql.org; Thu, 05 Oct 2023 14:33:59 +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 1qoPQL-00E4sv-A4 for pgsql-general@arkaria.postgresql.org; Thu, 05 Oct 2023 14:33:58 +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 1qoPQK-00E4qL-Ug for pgsql-general@lists.postgresql.org; Thu, 05 Oct 2023 14:33:57 +0000 Received: from mail-pl1-x636.google.com ([2607:f8b0:4864:20::636]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qoPQI-000A03-PB for pgsql-general@lists.postgresql.org; Thu, 05 Oct 2023 14:33:56 +0000 Received: by mail-pl1-x636.google.com with SMTP id d9443c01a7336-1c60a514f3aso8096705ad.3 for ; Thu, 05 Oct 2023 07:33:54 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1696516433; x=1697121233; darn=lists.postgresql.org; h=to:in-reply-to:cc:references:message-id:date:subject:mime-version :from:content-transfer-encoding:from:to:cc:subject:date:message-id :reply-to; bh=MGvaMzcAMs1GwipPTBobeaoXfOKETfj185nAvi2WBG0=; b=Iy6vlAPYbLhM7u06l/FdGs+NK/na7wqGGmjC61qAEm35Lx2X0X7OtZ7K0fogR0h5G2 6YV46jWPqaS0LPuCTpgn5jOmUNURCm3kOBnTmPj7vmIU4iVlYiKEZMU5Ii0HkEMA1GOk Hds4RoP7y6dOo9al2dSBIrNl+qIN6+fu29tnXORDneS4UsBt+9Q64gcpCrYi2YdMxmzI HCwO+jGrTHPWAubvRDRgfw/KMMatrmqesm+VoS47BpHohbTnP54FOrxl1xXpDs2486vL mG1jQVyN9wfrXLDoQ4S7VjIVOCX+4NiSmbS8HibsRVw4HWaYmLPDM2Uap14HyRv4plk2 z6sw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1696516433; x=1697121233; h=to:in-reply-to:cc:references:message-id:date:subject:mime-version :from:content-transfer-encoding:x-gm-message-state:from:to:cc :subject:date:message-id:reply-to; bh=MGvaMzcAMs1GwipPTBobeaoXfOKETfj185nAvi2WBG0=; b=j2xnTg66vrGDyR/57DOACOj9riTnJiLqnrrP04G/B7aN6KokxGC1FNZZ7kS+PGP60X WHqE4joB57h11Nr2YN/Cwgoo1jSxf8Ztj6+kvIj3SwovLcjBlhhElgKVuZ6YrcQlSXI1 Wdc25XLvkBkXIvwJdip9zviR2ZeZmIxrQTFIgP0fIzVgTABfhGyCWA0qEJuRdnhvbvv1 16L0qRvPoWudcFq5O4DRnFnV8wa29R6+Ty2ddhiUEm3Lhg/JF/7XXCpd8jmBnljGptV9 Sz9zB+ZAWetdE4+0BjDvD0UCR6elSe7l3jReCgkZE2zvyTPJuFNCuz4HPvKi5GwN0Xkw YJxA== X-Gm-Message-State: AOJu0YzmHkk0CYqlzxrTHd49/p3dzcRfF+V6aFCRW3a3xlHd9peLAvbV 05qnr7YyxJWNAScT5GD8k10Z758RGcs= X-Google-Smtp-Source: AGHT+IEggr7VKkGQN+lCB30h908oTAWSsa6vs6rRMogeQnIgCWJN/0m5ERMapLguvIe9XZN8QHWztg== X-Received: by 2002:a17:902:7207:b0:1c1:dbd6:9bf6 with SMTP id ba7-20020a170902720700b001c1dbd69bf6mr5014090plb.41.1696516432871; Thu, 05 Oct 2023 07:33:52 -0700 (PDT) Received: from smtpclient.apple (75-164-143-149.ptld.qwest.net. [75.164.143.149]) by smtp.gmail.com with ESMTPSA id ix4-20020a170902f80400b001b1a2c14a4asm1752989plb.38.2023.10.05.07.33.51 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 05 Oct 2023 07:33:52 -0700 (PDT) Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable From: Dow Drake Mime-Version: 1.0 (1.0) Subject: Re: Multiple inserts with two levels of foreign keys Date: Thu, 5 Oct 2023 07:33:41 -0700 Message-Id: References: <202310050939.okkrgeyg4yrs@alvherre.pgsql> Cc: pgsql-general@lists.postgresql.org In-Reply-To: <202310050939.okkrgeyg4yrs@alvherre.pgsql> To: Alvaro Herrera X-Mailer: iPad Mail (20H19) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Yes! Thanks, Alvaro! This is exactly the pattern I was trying to work out!= This community is awesome! > On Oct 5, 2023, at 2:39 AM, Alvaro Herrera wrote= : >=20 > =EF=BB=BFOn 2023-Oct-04, Dow Drake wrote: >=20 >> I want to insert a farm record, then insert two crops associated with tha= t >> farm, then insert two deliveries for each of the the two crops so that in= >> the end, my tables look like this: >=20 > If I understand you correctly, for each table you want one CTE with the > data you want to insert, and another CTE with the data actually > inserted, that can be matched later. Something like this should work: >=20 > with newfarms (name) as (values ('Happy Valley Farm')), > insertedfarms (id, name) as (insert into farms (name) > select newfarms.name > from newfarms > returning id, name), > newcrops (farm, name) as (values ('Happy Valley Farm', 'corn'), > ('Happy Valley Farm', 'wheat')), > insertedcrops as (insert into crops (farm_id, name) > select (select insertedfarms.id > from insertedfarms > where insertedfarms.name =3D newcrop= s.farm), > newcrops.name > from newcrops > returning id, farm_id, name), > newdeliveries (farm, name, ticket) as (values ('Happy Valley Farm', 'c= orn', '3124'), > ('Happy Valley Farm', 'w= heat', '3127'), > ('Happy Valley Farm', 'c= orn', '3133'), > ('Happy Valley Farm', 'w= heat', '3140')), > inserteddeliveries as (insert into deliveries (crop_id, ticket) > select (select ics.id > from insertedfarms ifs join in= sertedcrops ics on (ifs.id =3D ics.farm_id) > where ifs.name =3D newdeliverie= s.farm and > ics.name =3D newdeliverie= s.name), > ticket > from newdeliveries > returning *) > select * from inserteddeliveries; >=20 >=20 > --=20 > =C3=81lvaro Herrera 48=C2=B001'N 7=C2=B057'E =E2=80=94 htt= ps://www.EnterpriseDB.com/ > Are you not unsure you want to delete Firefox? > [Not unsure] [Not not unsure] [Cancel] > http://smylers.hates-software.com/2008/01/03/566e45b2.ht= ml