CODEWITHSUNDEEP
postgresqlsql-insert
Tomáš Hübelbauer
Tomáš Hübelbauer

Reputation: 10617

Insert multiple rows with VALUES containing IDs inserted in the same query

INSERT INTO "public"."Contacts"
  (FirstName, LastName, AddressId)
VALUES
 ('John', 'Doe',
  (INSERT INTO "public"."Addresses" (streetName) VALUES ('1st') RETURNING id)
 ),
  ('Jane', 'Doe',
   (INSERT INTO "public"."Addresses" (streetName) VALUES ('2nd') RETURNING id)
 )

I'm looking for a way to insert explicit values using multiple rows in VALUES and have the returned ID be a part of those values. Currently as the query stands, it doesn't work, but if it did, it would make a separate INSERT for each address.

I know I need to first insert all addresses using multiple VALUES again and then somehow use that ID array with the VALUES for Contacts, but I have no idea how. Anyone can provide any pointers?

Upvotes: 2

Views: 1069

Answers (1)

user330315
user330315

Reputation:

If the street names that you insert are unique, you could do something like this:

with new_data (firstname, lastname, streetname) as (
  values 
    ('John', 'Doe', '1st'),
    ('Jane', 'Doe', '2nd')
)
, new_addresses as (
  insert into addresses (streetname)
  select streetname
  from new_data
  returning id, streetname
)
insert into contacts (firstname, lastname, addressid)
select t.firstname, 
       t.lastname,
       a.id
from new_data t
  join new_addresses a on a.streetname = t.streetname;

Again: this only works correctly if the streetnames to be inserted are unique (they don't need to be unique in the addresses table).

If you can't guarantee that, I don't have an idea how you could do that with a single bulk-insert.

Note that I used different table names than you have in your question - I just loath those quoted identifiers

Upvotes: 4

Related Questions