Combining Insert Statements in a Data-Modifying Cte with a Case Expression

Combining INSERT statements in a data-modifying CTE with a CASE expression

You cannot nest INSERT statements in a CASE expression. Deriving from what I see, this completely different approach should do it:

Assumptions

• You don't actually need the outer SELECT.

• dm_name / rm_name are defined unique in dm / rm and not empty (<> ''). You should have a CHECK constraint to make sure.

• Column default for both d_id and r_id in z are NULL (default).

dm_name and rm_name mutually exclusive

If both are never present at the same time.

WITH d1 AS (
   INSERT INTO d (dm_id)
   SELECT dm.dm_id 
   FROM   import
   JOIN   dm USING (dm_name)
   RETURNING d_id
   )
, r1 AS (
   INSERT INTO r (rm_id)
   SELECT rm.rm_id 
   FROM   import
   JOIN   rm USING (rm_name)
   RETURNING r_id
   )
, z1 AS (
   INSERT INTO z (d_id, r_id)
   SELECT d_id, r_id
   FROM d1 FULL JOIN r1 ON FALSE
   RETURNING z_id
   )
INSERT INTO port (z_id)
SELECT z_id
FROM   z1;

The FULL JOIN .. ON FALSE produces a derived table with all rows from d1 and r1 appended with NULL for the respective other column (no overlap between the two). So we just need one INSERT instead of two. Minor optimization.

dm_name and rm_name can coexist

WITH i AS (
   SELECT dm.dm_id, rm.rm_id
   FROM   import
   LEFT   JOIN dm USING (dm_name)
   LEFT   JOIN rm USING (rm_name)
   )
, d1 AS (
   INSERT INTO d (dm_id)
   SELECT dm_id FROM i WHERE dm_id IS NOT NULL
   RETURNING dm_id, d_id
   )
, r1 AS (
   INSERT INTO r (rm_id)
   SELECT rm_id FROM i WHERE rm_id IS NOT NULL
   RETURNING rm_id, r_id
   )
, z1 AS (
   INSERT INTO z (d_id, r_id)
   SELECT d1.d_id, r1.r_id
   FROM   i
   LEFT   JOIN d1 USING (dm_id)
   LEFT   JOIN r1 USING (rm_id)
   WHERE  d1.dm_id IS NOT NULL OR
          r1.rm_id IS NOT NULL
   RETURNING z_id
   )
INSERT INTO port (z_id)
SELECT z_id FROM z1;

Notes

Both versions also work if neither exists.

INSERT inserts nothing if the SELECT does not returns row(s).

If you have to deal with concurrent write access that could conflict with this operation the quick fix would be to lock involved tables before you run this statement in the same transaction.

Combining INSERT INTO and WITH/CTE

You need to put the CTE first and then combine the INSERT INTO with your select statement. Also, the "AS" keyword following the CTE's name is not optional:

WITH tab AS (
    bla bla
)
INSERT INTO dbo.prf_BatchItemAdditionalAPartyNos (
BatchID,
AccountNo,
APartyNo,
SourceRowID
)  
SELECT * FROM tab

Please note that the code assumes that the CTE will return exactly four fields and that those fields are matching in order and type with those specified in the INSERT statement.
If that is not the case, just replace the "SELECT *" with a specific select of the fields that you require.

As for your question on using a function, I would say "it depends". If you are putting the data in a table just because of performance reasons, and the speed is acceptable when using it through a function, then I'd consider function to be an option.
On the other hand, if you need to use the result of the CTE in several different queries, and speed is already an issue, I'd go for a table (either regular, or temp).

WITH common_table_expression (Transact-SQL)

writeable common table expression and multiple insert statements

You can use CTEs, if you want this all in one statement:

with foo as (
      select * from ...
     ),
     b as (
      insert into bar
          select * from foo
          returning *
     )
insert into baz
    select * from foo;

Notes:

• You should include column lists with insert.
• You should specify the column names explicitly for the select *. This is important because the columns may not match in the two tables.
• I always use returning with update/insert/delete in CTEs. This is the normal use case -- so you can get serial ids back from an insert, for instance.

Is it safe to insert data from inside of a CTE expression?

The manual page on WITH queries states that your use case is legitimate and supported:

You can use data-modifying statements (INSERT, UPDATE, or DELETE) in WITH.

and

... data-modifying statements are only allowed in WITH clauses that are attached to the top-level statement. However, normal WITH visibility rules apply, so it is possible to refer to the WITH statement's output from the sub-SELECT.

Further:

If a data-modifying statement in WITH lacks a RETURNING clause, then it forms no temporary table and cannot be referred to in the rest of the query. Such a statement will be executed nonetheless.

how to rewrite query to put data-modifying CTE at top level

demo:db<>fiddle (because of random things, you may reload several times if the random uuid equals 1; instead of type uuid I used int because the fiddle engine currently does not support the pgcrypto extension. I simulated the function with an own one.)

WITH input (b_uuid, b_name) AS (
    VALUES (
        gen_random_uuid(), $1
    )
), ins_b AS (
    INSERT INTO b (
        b_uuid, b_name
    )
    TABLE input
    ON CONFLICT DO NOTHING
    RETURNING b_uuid
), new_b AS (
    TABLE ins_b
    UNION ALL
    SELECT b.b_uuid FROM input
    JOIN b USING (b_uuid)
)
INSERT INTO a (a_uuid, b_uuid)
VALUES (
    gen_random_uuid(), (SELECT b_uuid FROM new_b)
);

Your solution is not far away:

1. Manipulating statements (like INSERT) cannot be inside nested WITH clauses (At this point: Thank you, I didn't even know about this nested CTE feature :D)
2. The main point of @ErwinBrandstetter's incredible fantastic solution (How to use RETURNING with ON CONFLICT in PostgreSQL?) is the JOIN that your solution is missing:
   The new_b part works as follows: If there is a conflict, ins_b returns nothing. So, TABLE ins_b is empty. In that case the already existing b_uuid needs to be called directly from TABLE b. Taking the generated UUID, joining it against b gives out the existing b_uuid (and if you wish, every other column of this record). But if there is no conflict - the b_uuid does not exist yet -, then ins_b returns the new data set, TABLE ins_b is not empty, but the join on the original table fails because there is still no record persisted which can be used to join.
   This, of course, works for more than one record to be inserted.

Insert into table from CTE

Try this syntax:

INSERT INTO tmp( tmp_id )
  WITH cte AS (
    SELECT 1 AS tmp_id FROM dual
  )
  SELECT tmp_id
  FROM cte;

https://dev.mysql.com/doc/refman/8.0/en/with.html

One INSERT with multiple SELECT

Assumptions

• You want to link the newly inserted row in main_phrase to the row(s) in main_groupecategories with the same description.
• main_phrase.id is a serial column.

Explanation for Error

You cannot refer to any tables (including CTE) in a free-standing VALUES expression, you would have to use SELECT with a FROM clause. But there is a better solution. See below.

Better Query

Use a data-modifying CTE instead to make the whole operation shorter, safer and faster:

WITH p AS (
   INSERT INTO main_phrase (description)
   VALUES ('Mot commun féminin pluriel animaux')  -- provide description once
   RETURNING id, description  -- and reuse it further down
   )
INSERT INTO main_phrasegroupecategories (phrase_id, groupe_categories_id)
SELECT p.id, g.id
FROM   p
JOIN   main_groupecategories g USING (description);

If you want to use any values of the new rows, have them returned immediately with another RETURNING clause to the second INSERT.

Why would you have the same description redundantly in both tables of your (presumed) many-to-many relationship? Might be a problem in your database design.

Related:

• PostgreSQL multi INSERT...RETURNING with multiple columns
• SELECT * FROM NEW TABLE equivalent in Postgres
• Combining INSERT statements in a data-modifying CTE with a CASE expression

---

Related Topics