Postgres Throws Error When using Two select statement in One query

Question

I have created a Table with two Fields Date and name. I have Written an insert statement to insert values with two select statements from different tables.my query is as follows :

insert into temptable with date as (select date from generate_series('2014-04-01', 
  '2014-04-30', '1 day'::interval) as date),name as (select name from table12 where id=1912)

But Query returns an error like :

ERROR:  syntax error at end of input
LINE 3: ... date),name as (select name from table12 where id=1912)
                                                                  ^

Is there any problem with the query? Is it the right way to use 'with' in a query?

Answer 1

You need to select from the common table expressions:

to put the common table expression (the with part) before the insert:

insert into temptable
with date as (
   select date 
   from generate_series('2014-04-01','2014-04-30', '1 day'::interval) as date
),
name as (
   select name 
   from table12 
   where id=1912
)
select * 
from date 
  cross join name;

As an alternative, you can put the CTE in front of the insert (which I personally prefer):

with date as (
   select date 
   from generate_series('2014-04-01','2014-04-30', '1 day'::interval) as date
),
name as (
   select name 
   from table12 
   where id=1912
)
insert into temptable
select * 
from date 
  cross join name;

Not sure how you want the two parts of the CTE to be joined though.