Question 89361 in PostgreSQL Question posted in
The official documentation can be found here.

Query in postgresql similar to insert all in oracle

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.

Tags:

2

Answers


  1. 0

    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)

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search