6

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.

  • 1
    other way - trigger on one table with insert into any number of other – a_vlad May 31 '16 at 1:29
  • 1
    What makes it seem not manageable this way? Because it is. – Erwin Brandstetter May 31 '16 at 1:33
4

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;
  • Correct. Except, the 2nd RETURNING is not necessary. Data-modifying CTEs are always executed to completion either way: stackoverflow.com/a/15810159/939860 – Erwin Brandstetter May 31 '16 at 1:31
  • @ErwinBrandstetter I guess that's true if you don't explicitly specify CTE columns. – mustaccio May 31 '16 at 1:33
  • 1
    What do you mean by explicitly specify CTE columns? Test your code without the 2nd RETURNING and you'll see that it works. Consider the quotes from the manual in the related answer I linked. – Erwin Brandstetter May 31 '16 at 1:38
  • with cte ( col1 ) as (select ...) explicitly specifies a column (col1) in which case you'll need returning. – mustaccio May 31 '16 at 2:08
  • 1
    That's correct, of course. You wouldn't declare columns to be returned if you are not going to return columns ... Nitpicking: Your example in the comment uses select, where this does not apply. On the contrary: Basic CTEs (not data-modifying) which are not referenced by other CTEs or the outer statement are never executed! dba.stackexchange.com/a/69650/3684 – Erwin Brandstetter May 31 '16 at 2:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.