From: | Ron <ronljohnsonjr(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Multiple inserts with two levels of foreign keys |
Date: | 2023-10-05 14:59:24 |
Message-ID: | b1c119de-e395-4592-26f7-7f6f39b7658a@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 <alvherre(at)alvh(dot)no-ip(dot)org> 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Laurenz Albe | 2023-10-05 19:43:28 | Re: Ask about Foreign Table Plug-in on Windows Server. |
Previous Message | jacktby jacktby | 2023-10-05 14:33:52 | How to make a map in pg kernel? |