Syntax error for insert query alias in Postgres

Question

I have this query:

INSERT INTO emailevents (shopid, userid, emailid, campaignid, variationid, type, createdon) 
VALUES ($1,                 
(SELECT id FROM users WHERE mongoid=$2), 
(SELECT id FROM emails WHERE mongoid=$3), 
(SELECT id FROM campaigns WHERE mongoid=$4) AS cid, 
(SELECT id FROM campaignvariations WHERE templateid=(SELECT id FROM templates WHERE mongoid=$5) AND campaignid=cid), 
$6, 
to_timestamp($7))

and I'm getting this error:Query failed: ERROR: syntax error at or near "AS"

I've tried putting cid inside the bracket, without success.

How should I use the alias?

Robert · Accepted Answer · 2013-09-13 11:33:36Z

Try to change to select instead of values as below

INSERT INTO emailevents (shopid, userid, emailid, campaignid, variationid, type, createdon)      
select $1,                 
       (SELECT id FROM users WHERE mongoid=$2), 
       (SELECT id FROM emails WHERE mongoid=$3), 
       (SELECT id FROM campaigns WHERE mongoid=$4) AS cid, 
       (SELECT id FROM campaignvariations WHERE templateid=(SELECT id FROM templates WHERE mongoid=$5) AND campaignid=(SELECT id FROM campaigns WHERE mongoid=$4)), 
       $6, 
       to_timestamp($7)

I think you should replace cit with (SELECT id FROM campaigns WHERE mongoid=$4) for variationid column

Gordon Linoff · Accepted Answer · 2013-09-13 12:50:11Z

One fix is to change the values to a select:

INSERT INTO emailevents (shopid, userid, emailid, campaignid, variationid, type, createdon) 
    select $1,                 
           (SELECT id FROM users WHERE mongoid=$2), 
           (SELECT id FROM emails WHERE mongoid=$3), 
           (SELECT id FROM campaigns WHERE mongoid=$4) AS cid, 
           (SELECT id FROM campaignvariations WHERE templateid=(SELECT id FROM templates WHERE mongoid=$5) AND campaignid=cid), 
            $6, 
            to_timestamp($7);

I prefer using insert . . . select rather than insert . . . values in general.

You might be able to just remove the as cid if Postgres supports subqueries in the values statement.

EDIT:

The above fixes the as problem, but not the overall problem. Let's use select and move most of the subqueries to the from clause:

INSERT INTO emailevents (shopid, userid, emailid, campaignid, variationid, type, createdon) 
    select const.shopid,  u.id, e.id, c.id                
           (SELECT id
            FROM campaignvariations
            WHERE templateid=(SELECT id FROM templates WHERE mongoid=$5) AND campaignid=c.id
           ), 
           const.type, 
           const.createdon
    from (select $1 as shopid, $6 as type, to_timestamp($7) as createdon) const left outer join
         (SELECT id FROM users WHERE mongoid=$2) u cross join
         (SELECT id FROM emails WHERE mongoid=$3) e cross join
         (SELECT id FROM campaigns WHERE mongoid=$4) c;

but I'm using cid for the next subquery, that's why I need it there. — Alexandru R
– Alexandru R, Commented Sep 13, 2013 at 12:14

roman · Accepted Answer · 2013-09-13 12:50:49Z

If you really don't want to use variables and you want to use cid in the query, you can precalculate it with cte:

with cte_cid as (
    select (select id from campaigns where mongoid=$4) as cid -- need 2 selects in case you don't have record for mongoid=$4
)
insert into emailevents (shopid, userid, emailid, campaignid, variationid, type, createdon)      
select $1 as shopid,              
   (select t.id from users as t where t.mongoid=$2), 
   (select t.id from emails as t where t.mongoid=$3), 
   cid,
   (select t.id from campaignvariations as t where t.templateid=(select tt.id from templates as tt where tt.mongoid=$5) and t.campaignid=c.cid), 
$6,
   to_timestamp($7)
from cte_cid as c

Collectives™ on Stack Overflow

Syntax error for insert query alias in Postgres

3 Answers 3

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

Related