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!

The parentheses around the `select` are not needed at all. – Dec 06 '13 at 15:02 — , Dec 06 '13 at 15:02

foibs · Accepted Answer · 2013-12-06T16:17:54.627

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

Yeah, I meant to mention that the row counts will be even between queries. Thanks for the answer! — Super_user_one, Dec 06 '13 at 16:53

score 0 · Answer 2 · answered Dec 06 '13 at 15:01

0

Don't have an instance handy to test this, but what if you put 'UNION' between your select statements, so it would be one result set coming back?

answered Dec 06 '13 at 15:01

Dan G

1,051
8
14

1

I get ERROR: each UNION query must have the same number of columns – Super_user_one Dec 06 '13 at 15:04
UNION is wrong. Even if you align the columns, it will return 2 rows instead of one, first row will have empty/predefined var1 to 4, second row will have empty var5 to 7 – foibs Dec 06 '13 at 16:15
Ah - gotcha - totally misread the original question - union = fail here... :) – Dan G Feb 05 '14 at 18:23

Adding two select statements into one insert into statement in postgresql

2 Answers2

Linked