Store and reuse value returned by INSERT … RETURNING

by

… that can be used to insert values into other tables?

You can even do that in a single SQL statement using a data-modifying CTE:

WITH ins1 AS (
   INSERT INTO tbl1(txt)
   VALUES ('foo')
   RETURNING tbl1_id
   )
INSERT INTO tbl2(tbl1_id)
SELECT * FROM ins1

Requires PostgreSQL 9.1 or later.

db<>fiddle here (Postgres 13)
Old sqlfiddle (Postgres 9.6)

Reply to question update

You can also insert into multiple tables in a single query:

WITH ins1 AS (
   INSERT INTO tbl1(txt)
   VALUES ('bar')
   RETURNING tbl1_id
   )
 , ins2 AS (
   INSERT INTO tbl2(tbl1_id)
   SELECT tbl1_id FROM ins1
   )
INSERT INTO tbl3(tbl1_id)
SELECT tbl1_id FROM ins1;

More Related Contents:

  1. How do you use script variables in psql?
  2. Insert data in 3 tables at a time using Postgres
  3. PostgreSQL multi INSERT…RETURNING with multiple columns
  4. Multiple CTE in single query
  5. INSERT rows into multiple tables in a single query, selecting from an involved table
  6. Delete parent if it’s not referenced by any other child
  7. Window Functions or Common Table Expressions: count previous rows within range
  8. How to use multiple CTEs in a single SQL query?
  9. How do I (or can I) SELECT DISTINCT on multiple columns?
  10. How to speed up insertion performance in PostgreSQL
  11. PostgreSQL GROUP BY different from MySQL?
  12. How can I import a JSON file into PostgreSQL?
  13. Return multiple columns of the same row as JSON array of objects
  14. Normalize array subscripts so they start with 1
  15. Is there a way to define a named constant in a PostgreSQL query?
  16. The maximum recursion 100 has been exhausted before statement completion
  17. Fastest check if row exists in PostgreSQL
  18. Slow simple update query on PostgreSQL database with 3 million rows
  19. Discard millisecond part from timestamp
  20. Return setof record (virtual table) from function
  21. SQL column reference “id” is ambiguous
  22. postgres – where in (list) – column does not exist
  23. incomplete information from query on pg_views [duplicate]
  24. PostgreSQL: Give all permissions to a user on a PostgreSQL database
  25. How to compare dates in datetime fields in Postgresql?
  26. What is the fastest way to truncate timestamps to 5 minutes in Postgres?
  27. Postgres trigger after insert accessing NEW
  28. Cannot SELECT from UPDATE RETURNING clause in postgres
  29. Use one CTE many times
  30. PostgreSQL: top n entries per item in same table

Leave a Comment