Insert into multiple tables with one query

Question

I would like to add data to at least three tables with one query. I thought of something like this:

WITH ins AS (
  INSERT INTO core.adr
    (street, "number", postal_code, city)
  VALUES
    ('test 1', '25a', '00912', 'villageman')
  RETURNING id)
INSERT INTO core.adr_information
  (idref, info)
SELECT id, 'test data'
FROM ins;

which works perfectly for exactly two tables.

Main problem here is, that all additional queries require the id value from the first INSERT query, which seems not manageable this way. I think it could be easily done with a stored procedure or transaction, but I would like the solution to be a simple and solid query.

Am I overlooking something? Is it possible that way or another (no stored procedures* or transactions)?

Note

_{* Altough, strictly speaking, Postgres does not have stored procedures, only functions, as @Erwin Brandstetter rightly pointed out via comments. See this series of posts for relevant information.}

score 11 · Accepted Answer · answered May 30 '16 at 20:54

I think you can easily pile the CTEs on top of each other, like so:

WITH ins AS (
  INSERT INTO core.adr
    (street, "number", postal_code, city)
  VALUES
    ('test 1', '25a', '00912', 'villageman')
  RETURNING id),
ins2 AS (
  INSERT INTO someothertable
    (id, something)
  SELECT id, 'something' FROM ins
  RETURNING id -- this is necessary for CTE, but not used
)
INSERT INTO core.adr_information
  (idref, info)
SELECT id, 'test data'
FROM ins;

Insert into multiple tables with one query

1 Answers1