PostgreSQL multi INSERT…RETURNING with multiple columns

by

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 INSERTs (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:

More Related Contents:

  1. INSERT rows into multiple tables in a single query, selecting from an involved table
  2. Insert data in 3 tables at a time using Postgres
  3. Multiple CTE in single query
  4. Delete parent if it’s not referenced by any other child
  5. Window Functions or Common Table Expressions: count previous rows within range
  6. Export specific rows from a PostgreSQL table as INSERT SQL script
  7. postgresql: INSERT INTO … (SELECT * …)
  8. How to use multiple CTEs in a single SQL query?
  9. Bulk/batch update/upsert in PostgreSQL
  10. Store and reuse value returned by INSERT … RETURNING
  11. How to bulk insert only new rows in PostreSQL
  12. SQL Populate table with random data
  13. How to concatenate strings of a string field in a PostgreSQL ‘group by’ query?
  14. Does PostgreSQL support “accent insensitive” collations?
  15. PostgreSQL DISTINCT ON with different ORDER BY
  16. PostgreSQL: running count of rows for a query ‘by minute’
  17. Split comma separated column data into additional columns
  18. How to delete duplicate entries?
  19. Transpose latest rows per user to columns
  20. subquery in FROM must have an alias
  21. Postgres Case Sensitivity
  22. Polymorphism in SQL database tables?
  23. How to round an average to 2 decimal places in PostgreSQL?
  24. Explain JOIN vs. LEFT JOIN and WHERE condition performance suggestion in more detail
  25. Return multiple columns and rows from a function PostgreSQL instead of record
  26. Postgres error updating column data
  27. SQL LIKE condition to check for integer?
  28. Return rows from INSERT with ON CONFLICT without needing to update
  29. PostgreSQL nested INSERTs / WITHs for foreign key insertions
  30. Fetch records that are non zero after the decimal point in PostgreSQL

Leave a Comment