pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Dow Drake <dowdrake@gmail.com>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Multiple inserts with two levels of foreign keys
Date: Thu, 5 Oct 2023 07:33:41 -0700
Message-ID: <C50FF2D7-8114-4656-8027-D07D48524DC7@gmail.com> (raw)
In-Reply-To: <202310050939.okkrgeyg4yrs@alvherre.pgsql>
References: <202310050939.okkrgeyg4yrs@alvherre.pgsql>

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@alvh.no-ip.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





view thread (11+ messages)  latest in thread

Message-ID: <C50FF2D7-8114-4656-8027-D07D48524DC7@gmail.com>
Permalink:  ../C50FF2D7-8114-4656-8027-D07D48524DC7@gmail.com/
Also on:    postgresql.org/message-id/C50FF2D7-8114-4656-8027-D07D48524DC7@gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: dowdrake@gmail.com, alvherre@alvh.no-ip.org
  Subject: Re: Multiple inserts with two levels of foreign keys
  In-Reply-To: <C50FF2D7-8114-4656-8027-D07D48524DC7@gmail.com>
  References: <202310050939.okkrgeyg4yrs@alvherre.pgsql> <C50FF2D7-8114-4656-8027-D07D48524DC7@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox