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 1qoKpK-000udE-Rh for pgsql-general@arkaria.postgresql.org; Thu, 05 Oct 2023 09:39:27 +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 1qoKpI-00BQIy-CW for pgsql-general@arkaria.postgresql.org; Thu, 05 Oct 2023 09:39:25 +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 1qoKpH-00BQI7-Vt for pgsql-general@lists.postgresql.org; Thu, 05 Oct 2023 09:39:24 +0000 Received: from out1-smtp.messagingengine.com ([66.111.4.25]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qoKpG-0007qT-7r for pgsql-general@lists.postgresql.org; Thu, 05 Oct 2023 09:39:23 +0000 Received: from compute6.internal (compute6.nyi.internal [10.202.2.47]) by mailout.nyi.internal (Postfix) with ESMTP id 9B2245C04B7; Thu, 5 Oct 2023 05:39:21 -0400 (EDT) Received: from mailfrontend2 ([10.202.2.163]) by compute6.internal (MEProxy); Thu, 05 Oct 2023 05:39:21 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-transfer-encoding :content-type:content-type:date:date:feedback-id:feedback-id :from:from:in-reply-to:in-reply-to:message-id:mime-version :reply-to:sender:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm2; t=1696498761; x= 1696585161; bh=IgBk2Tv3Ak2Rw0mUc0QHPDG26yA+1sH4DY+A/aAf1/Y=; b=f T2j21OY1D9561xjfzU5PMoROF2qAfC2BARB5QUBwfULIlWxWMHaV+Rg8X2oQWdg9 OcvuOPjTjUtghrrr6hl3hCAM7Hu64Kyaf4ORsvM/aP8SqGZx0VA24i3KMffFSX3y jqeHAuh4NIwqS/dmonEIURNYw3ugRPMgLH0jXfpXkUShbE/QYEx4RJY/J3iLPfG1 FYRV6C7f/IAm1szgfvjCSP0/pLtXaX0UA4rc0aXT3rzV46vP4du+Qr4OMZsnBnzj SakEH7sD3/VTiH/zwtS537Zmi4/o993gtdpGiSaLkDDIXXCicuOjXrz9irZatzs9 DbrSVKABqF5mVmBg4rlRQ== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvkedrgeeggdduiecutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenuc fjughrpeffhffvvefukfggtggugfgjsehtkeertddttdejnecuhfhrohhmpeetlhhvrghr ohcujfgvrhhrvghrrgcuoegrlhhvhhgvrhhrvgesrghlvhhhrdhnohdqihhprdhorhhgqe enucggtffrrghtthgvrhhnpedtudekudetueefvedvueefleetieefieejkeetteekkeev tdduudeiteejgffgteenucffohhmrghinhepvghnthgvrhhprhhishgvuggsrdgtohhmpd hhrghtvghsqdhsohhfthifrghrvgdrtghomhenucevlhhushhtvghrufhiiigvpedtnecu rfgrrhgrmhepmhgrihhlfhhrohhmpegrlhhvhhgvrhhrvgesrghlvhhhrdhnohdqihhprd horhhg X-ME-Proxy: Feedback-ID: ia2694551:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Thu, 5 Oct 2023 05:39:21 -0400 (EDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=alvh.no-ip.org; s=schmee; t=1696498759; bh=x1G8QWJWGsX1+e1Ap4uZMFFtNZppPUCP8S4Cy82po4k=; h=Date:From:To:Cc:Subject:In-Reply-To:From; b=X6LBx+1x4guit1BOiwUMg6W3yKTaIZPWFBt8FU104EC0ROM+aAhhF9fq+V8oKqN7U gGSn8vegG0026St04QtkOurrluU3qA+vyEKwGvBtJhOTdqnkQ7LB9GLgSmHe1S5961 u7FZTsZlhRwsfjFwzCw/VKCjJrHpSzV/KN/ny93AsQxVEP3gwGBejnxrI4wtg4kjdI Ha+EmKDGMmlBUA/V95v2//UuQgOD5JmdNLz2AwzwqIkMGF9Nvhrlm78/sWF7UWGOWd GEUJ5uCddEmkZOVVv2rcDERusiP6BXmiI3VWvIguqsnEuc9Y8K+iA5MfGD4GSVNemx y3NxtuF9KcHzA== Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000) id 42B12128; Thu, 5 Oct 2023 11:39:19 +0200 (CEST) Date: Thu, 5 Oct 2023 11:39:19 +0200 From: Alvaro Herrera To: Dow Drake Cc: pgsql-general@lists.postgresql.org Subject: Re: Multiple inserts with two levels of foreign keys Message-ID: <202310050939.okkrgeyg4yrs@alvherre.pgsql> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 2023-Oct-04, Dow Drake wrote: > 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: 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: 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 = newcrops.farm), newcrops.name from newcrops returning id, farm_id, name), newdeliveries (farm, name, ticket) as (values ('Happy Valley Farm', 'corn', '3124'), ('Happy Valley Farm', 'wheat', '3127'), ('Happy Valley Farm', 'corn', '3133'), ('Happy Valley Farm', 'wheat', '3140')), inserteddeliveries as (insert into deliveries (crop_id, ticket) select (select ics.id from insertedfarms ifs join insertedcrops ics on (ifs.id = ics.farm_id) where ifs.name = newdeliveries.farm and ics.name = newdeliveries.name), ticket from newdeliveries returning *) select * from inserteddeliveries; -- Álvaro Herrera 48°01'N 7°57'E — https://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.html