Insert data in 3 tables at a time using Postgres

by

Use data-modifying CTEs:

WITH ins1 AS (
   INSERT INTO sample(firstname, lastname)
   VALUES ('fai55', 'shaggk')
-- ON     CONFLICT DO NOTHING         -- optional addition in Postgres 9.5+
   RETURNING id AS sample_id
   )
, ins2 AS (
   INSERT INTO sample1 (sample_id, adddetails)
   SELECT sample_id, 'ss' FROM ins1
   RETURNING user_id
   )
INSERT INTO sample2 (user_id, value)
SELECT user_id, 'ss2' FROM ins2;

Each INSERT depends on the one before. SELECT instead of VALUES makes sure nothing is inserted in subsidiary tables if no row is returned from a previous INSERT. (Since Postgres 9.5+ you might add an ON CONFLICT.)
It’s also a bit shorter and faster this way.

Typically, it’s more convenient to provide complete data rows in one place:

WITH data(firstname, lastname, adddetails, value) AS (
   VALUES                              -- provide data here
      ('fai55', 'shaggk', 'ss', 'ss2') -- see below
    , ('fai56', 'XXaggk', 'xx', 'xx2') -- works for multiple input rows
       --  more?                      
   )
, ins1 AS (
   INSERT INTO sample (firstname, lastname)
   SELECT firstname, lastname          -- DISTINCT? see below
   FROM   data
   -- ON     CONFLICT DO NOTHING       -- UNIQUE constraint? see below
   RETURNING firstname, lastname, id AS sample_id
   )
, ins2 AS (
   INSERT INTO sample1 (sample_id, adddetails)
   SELECT ins1.sample_id, d.adddetails
   FROM   data d
   JOIN   ins1 USING (firstname, lastname)
   RETURNING sample_id, user_id
   )
INSERT INTO sample2 (user_id, value)
SELECT ins2.user_id, d.value
FROM   data d
JOIN   ins1 USING (firstname, lastname)
JOIN   ins2 USING (sample_id);

db<>fiddle here

You may need explicit type casts in a stand-alone VALUES expression – as opposed to a VALUES expression attached to an INSERT where data types are derived from the target table. See:

If multiple rows can come with identical (firstname, lastname), you may need to fold duplicates for the first INSERT:

...
INSERT INTO sample (firstname, lastname)
SELECT DISTINCT firstname, lastname FROM data
...

You could use a (temporary) table as data source instead of the CTE data.

It would probably make sense to combine this with a UNIQUE constraint on (firstname, lastname) in the table and an ON CONFLICT clause in the query.

Related:

More Related Contents:

  1. How to use RETURNING with ON CONFLICT in PostgreSQL?
  2. How to speed up insertion performance in PostgreSQL
  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. INSERT COMMAND :: ERROR: column “value” does not exist
  9. How to use multiple CTEs in a single SQL query?
  10. Generate DEFAULT values in a CTE UPSERT using PostgreSQL 9.3
  11. How to include excluded rows in RETURNING from INSERT … ON CONFLICT
  12. Store and reuse value returned by INSERT … RETURNING
  13. How to concatenate strings of a string field in a PostgreSQL ‘group by’ query?
  14. PostgreSQL DISTINCT ON with different ORDER BY
  15. PostgreSQL: running count of rows for a query ‘by minute’
  16. Split comma separated column data into additional columns
  17. How to delete duplicate entries?
  18. How to include “zero” / “0” results in COUNT aggregate?
  19. delete “column does not exist”
  20. How to create a temporary function in PostgreSQL?
  21. Polymorphism in SQL database tables?
  22. How to round an average to 2 decimal places in PostgreSQL?
  23. Explain JOIN vs. LEFT JOIN and WHERE condition performance suggestion in more detail
  24. Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
  25. postgresql: INSERT INTO … (SELECT * …)
  26. Return multiple columns and rows from a function PostgreSQL instead of record
  27. Postgres error updating column data
  28. SQL LIKE condition to check for integer?
  29. How to detect query which holds the lock in Postgres?
  30. Use one CTE many times

Leave a Comment