PostgreSQL multi INSERT...RETURNING with multiple columns
common-table-expression, insert, postgresql, sql
Solution
Use data-modifying CTEs to chain your three INSERTs. Something like this:
WITH ins1 AS (
INSERT INTO table1 (username, name, surname)
VALUES ('johnee','john','smith')
RETURNING user_id
)
, ins2 AS (
INSERT INTO table2 (user_id, password)
SELECT ins1.user_id, 'secret'
FROM ins1 -- nothing to return here
)
INSERT INTO table3 (user_id, adress, city, phone)
SELECT ins1.user_id, ...
FROM ins1
RETURNING user_id;
It's typically best to add a column definition list for `INSERT`s (except for special cases). Else, if the table structure changes, your code might break in surprising ways.
I omitted columns where you would just enter `DEFAULT`. Defaults are inserted automatically. Shorter, same result.
The final, optional `RETURNING` returns the resulting `user_id` - obviously from a sequence or some other default. It's actually the `user_id` from `table3`, but that's the same unless you have some triggers or other magic interfering.
More about data-modifying (a.k.a. "writable") CTEs:
- Are SELECT type queries the only type that can be nested?
Problem
I`m building a database with Postgres 9.3 as a backend, having 3 tables: ``` table1 (user_id, username, name, surname, emp_date) table2 (pass_id, user_id, password) table3 (user_dt_id, user_id, adress, city, phone) ``` As can be seen `table2` and `table3` are child tables of `table1`. I can extract the `user_id` of a newly inserted row in `table1` (parent): ``` INSERT INTO "table1" (default,'johnee','john','smith',default) RETURNING userid; ``` I need to insert the newly extracted id (from `table1`) into `user_id` columns of `table2` and `table3` along with other data unique for those tables. Basically 3 X INSERT ... How do I do that?