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.

user330315
– user330315

2013-12-06 15:02:32 +00:00
Commented Dec 6, 2013 at 15:02 — user330315
– user330315, Commented Dec 6, 2013 at 15:02

foibs · Accepted Answer · 2013-12-06 16:17:54Z

11

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

edited Dec 6, 2013 at 16:17

answered Dec 6, 2013 at 16:12

foibs

3,4141 gold badge21 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Super_user_one Over a year ago

Yeah, I meant to mention that the row counts will be even between queries. Thanks for the answer!

Dan G · Accepted Answer · 2013-12-06 15:01:03Z

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 6, 2013 at 15:01

Dan G

1,1189 silver badges14 bronze badges

3 Comments

Super_user_one Over a year ago

I get ERROR: each UNION query must have the same number of columns

foibs Over a year ago

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

Dan G Over a year ago

Ah - gotcha - totally misread the original question - union = fail here... :)

Collectives™ on Stack Overflow

Adding two select statements into one insert into statement in postgresql

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related