Query in postgresql similar to insert all in oracle

Question

There is an "insert all" request in Oracle.

INSERT ALL

  INTO mytable1 (empId, name, last_name) VALUES (expr1, expr2, expr_n)

  INTO mytable2 (empId, address) VALUES (o_expr1, o_expr2, o_expr_n)


SELECT * FROM dual;

How can I similarly insert records into postgres in a single request? I don't have any return values.

Why do you need it to be a single request? – Bergi Mar 16 '23 at 18:58 — Bergi, Mar 16 '23 at 18:58

score 2 · Accepted Answer · answered Mar 16 '23 at 19:02

You'd use a common table expression:

WITH _a AS (
  INSERT INTO mytable1 (empId, name, last_name)
  VALUES (expr1, expr2, expr_n)
), _b AS (
  INSERT INTO mytable2 (empId, address)
  VALUES (o_expr1, o_expr2, o_expr_n)
)
SELECT 'X' AS dummy;

(The final SELECT attempts to mirror the Oracle idiom but you can omit it)

score 1 · Answer 2 · answered Mar 17 '23 at 01:36

There is no reason, in this case, even in Oracle to use Insert All. The values are defined variables in a procedural block (pl/sql), they are not derived from the select following. So there is no reason you need a single statement in Postgres. 2 Inserts within a procedural block (plpgsql) will accomplish exactly the same. So

declare ... 
begin
    ...
    insert into mytable1 (empid, name, last_name)
         values (expr1, expr2, expr_n);

    insert into mytable2 (empid, address) 
         values (o_expr1, o_expr2, o_expr_n);
    ...
end;

Would do exactly the same. And IMHO easier to understand than DML within a CTE.

NOTE:

As written these will not run in Oracle nor in Posrgres. The insert for mytable2 names 2 columns to be inserted, but contains 3 values. Those must match. I am guessing this is a copy/paste error.

Yes, this is a mistake. Unfortunately, I will be executing the query from the application, so I can't use procedural blocks — Inessa, Mar 17 '23 at 17:21

Query in postgresql similar to insert all in oracle

2 Answers2