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 1qoPp4-001AvT-L3 for pgsql-general@arkaria.postgresql.org; Thu, 05 Oct 2023 14:59:30 +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 1qoPp2-00EZ5k-G4 for pgsql-general@arkaria.postgresql.org; Thu, 05 Oct 2023 14:59:29 +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 1qoPp2-00EZ3n-4h for pgsql-general@lists.postgresql.org; Thu, 05 Oct 2023 14:59:29 +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 1qoPp0-000AEs-0M for pgsql-general@lists.postgresql.org; Thu, 05 Oct 2023 14:59:27 +0000 Received: by mail-yw1-x1130.google.com with SMTP id 00721157ae682-59bf1dde73fso12159887b3.3 for ; Thu, 05 Oct 2023 07:59:25 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1696517965; x=1697122765; darn=lists.postgresql.org; h=content-transfer-encoding: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=eC0bH0UOvjlfPo94x62JQ/NqmGwwdrzBCONyCsbrZjI=; b=mAChE8mQ8emvRQZb6iGEpZSNKlEZy4b9A+TQhpA4if3iq9G2Kpn+fUG/BliHGB4eBc nlSqZONCiAmhbxYAUdy9rYAY8xfRTcQp3IVGg2hmL/Y8O5ocEvSOkmVD7xNbspdxRSFO l39XyFznbsxMLsGv/SArTXveqImbBJqHHk3fLVwxTf2Jpu6e50ahvdhD8qd52G4Ql3Mv rWaDp7nEQvd2Uyzo1j2DF00Jg6vsWv/f3GMyydTx80FMIfwiL6kVkz9nVoRPF0WFJwiY aSfl2qoNhUpwmRIiLtKu38ngAHjBrK4IWj6rzle9SVDtJcA5RD3CRnV9eU52IR/52Ley TFCg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1696517965; x=1697122765; h=content-transfer-encoding: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=eC0bH0UOvjlfPo94x62JQ/NqmGwwdrzBCONyCsbrZjI=; b=b0TBKDTgU6LIF/oJjr/+5zuLn3+blaBXtiHYJHxyiDwi63uyNqPB/AvdmMnf3s6/RK N11UQjplVPmT4cUhReDBbxJtHbdrKQVonnkmYvXD/Hcd2kmtxuo6mO6xVbjsK5dogGLb 4n+jBXe97AHW0sZGb9iQ29b5V/MPh0KZEN7i1xRgpww0W2L44NyRF+hDL3kZuJyA+iIw yk+hJSse5Lfh2hz+0VLNd1HH1ItonmLU/1C6JJOAUbhurKKlAk/jNZKRTugSW8ufQ5YQ Y58h4X+aELAkw8pmKqSgSI4qWcukHK7dOSjpqRSx5BuJKsLLOQpLBOYpfE64J9FVGLFB zXTw== X-Gm-Message-State: AOJu0YwLyLC5V7921P9gBsSR8IXIOrHeSBkprXkFA1nf2IGB64hRCAxA HeTqm9+6Qoef/HIwJvBAk30Ve5/lnik= X-Google-Smtp-Source: AGHT+IEs8xM7C4nbhqMfD7HyBI2QS7cRL82allSPxQyXMki4x9Wjwrsj8+A5bJ0V5HtmFm1h8O2K0g== X-Received: by 2002:a81:5251:0:b0:586:a170:7dbe with SMTP id g78-20020a815251000000b00586a1707dbemr6081330ywb.13.1696517964868; Thu, 05 Oct 2023 07:59:24 -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 i8-20020a819108000000b0059f802fad40sm554353ywg.22.2023.10.05.07.59.24 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 05 Oct 2023 07:59:24 -0700 (PDT) Message-ID: Date: Thu, 5 Oct 2023 09:59:24 -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: <202310050939.okkrgeyg4yrs@alvherre.pgsql> From: Ron In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk But honestly, the amount of text duplication hurts my "inner programmer".  And it would have to be generated dynamically, since you don't know how many crops were delivered.  #shudder On 10/5/23 09:33, Dow Drake wrote: > 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: >> >> 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 > -- Born in Arizona, moved to Babylonia.