input values into multiple tables where the index of one table creates an entry into the secondary t-CodePudding

I'm trying to write a SQL query that input data into multiple tables.

One of the tables has relationship with a separate table, that creates a new entry (I think). I want some of the data to go to this new column in that other table.

The first table is like this,

  substancedetails
    - id
     - etc

The second table is like

Substance Timing
 - substance id
 - time

Usually I would insert like this,

INSERT INTO substancedetails (xxx,...) 
VALUES(
'xxx',
...)
INSERT INTO SubstanceTiming (x,...) 
VALUES(
'xxx',
...)
;

How can i add it against the proper id?

CodePudding user response：

You can use the returning clause:

WITH i AS (
      INSERT INTO substancedetails (xxx, ...) 
          VALUES('xxx', ...)
          RETURNING *
     )
INSERT INTO SubstanceTiming (substanceid, x, ...) 
    SELECT id, 'xxx', ...
    FROM i;

The use of a CTE with an insert/update/delete is a convenient Postgres enhancement of SQL that allows multiple changes in a single statement.