Postgresql - INSERT INTO based on multiple SELECT

Question

I intend to write a INSERT INTO request in Postgresql based on several SELECT but didn't succeed. I have one table containing data I select (srctab), and another one where I insert data (dsttab). Here is what I run :

INSERT INTO dsttab (dstfld1, dstfld2) WITH
  t1 AS (
    SELECT srcfld1 
    FROM srctab 
    WHERE srcfld3 ='foo'
  ),
  t2 AS (
    SELECT srcfld5
    FROM srctab 
    WHERE srcfld6 ='bar'
  ) select srcfld1, srcfld5 from srctab;

Could you please help to make this work ? Thank you !

Answer 1

Note: I'm guessing about what you want to do here. My guess is that you want to insert a single row with the values from the CTEs (That's the WITH block.). Your query as written would insert a row into dsttab for every row in srctab, if it were valid syntax.

---

You don't really need a CTE here. CTEs should really only be used when you need to reference the same subquery more than once; that's what they exist for. (Occasionally, you can somewhat abuse them to control certain performance aspects in PostgreSQL, but that isn't the case in other DBs and is something to be avoided when possible anyway.)

Just put your queries in line:

INSERT INTO dsttab (dstfld1, dstfld2)
VALUES (
    (SELECT srcfld1 
    FROM srctab 
    WHERE srcfld3 ='foo'),
    (SELECT srcfld5
    FROM srctab 
    WHERE srcfld6 ='bar')
);

The key point here is to surround the subqueries with parentheses.