Adding two select statements into one insert into statement in postgresql

Question

I have made a temporary table through:

create temporary table return_table 
(
   p1 BIGINT, 
   p2 VARCHAR(45), 
   p3 VARCHAR(45), 
   p4 VARCHAR(45), 
   p5 VARCHAR(45), 
   p6 float, 
   p7float
) on commit drop;

Im trying to take 2 select statements and insert data into that temporary table. For example, I have a table named t1 which provides the first four values, and then I want the next 3 values for the temporary table to come from another table.

So far I have:

insert into return_table 
(Select var1, var2, var3, var4 
 from t1 where var1 = 10)

That will successfully put 4 values into my temporary table and then leave the rest null. That's fine, so when I attempt to insert the last three variables from another table. e.g.

insert into return_table 
(Select var1, var2, var3, var4 
 from t1 where var1 = 10, Select var5, var6, var 7 
 from t2 where var6 = 25)

It throws a syntax error. I've tried a few other syntactical changes, but I can't figure out the right syntax for inserting both results of those select statements on the same row.

Any help would be great!

Answer 1

Two select commands separated by a comma is not valid SQL syntax. You can use join or with statements instead. Here's an example with with

insert into return_table 
WITH t1 AS (
    Select var1, var2, var3, var4 from t1 where var1 = 1
  ), t2 AS (
    Select var5, var6, var7 from t2 where var6 = 6
  )
select t1.var1, t1.var2, t1.var3, t1.var4, t2.var5, t2.var6, t2.var7 from t1,t2

One could make only one subquery with with but I put them both to demonstrate the flexibility of being able to add as many tables as required.

Please note that it is a very good practice to list all the columns of the table that you are inserting into,

e.g. `insert into return_table (p1, p2, p3, p4, p5, p6, p7) ...`

You will avoid a lot of potential trouble and headaches if you make a habit of it.

Also please note that the above example (and it's join equivalent) may produce funky results if any of the two subqueries returns a row count different than one