在Postgres中插入查询别名的语法错误

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.

我试过把cid放在括号里，但没有成功。

How should I use the alias?

如何使用别名?

3 个解决方案

#1

Try to change to select instead of values as below

尝试更改为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=(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

我认为您应该用变量id列替换cit(从mongoid= 4美元的活动中选择id)

#2

One fix is to change the values to a select:

一种方法是将值更改为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.

我更喜欢用insert…选择而不是插入…值一般。

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

如果Postgres支持values语句中的子查询，那么您可以删除as cid。

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:

上述方法解决了as问题，但没有解决总体问题。让我们使用select并将大部分子查询移动到from子句:

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;

#3

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

如果你真的不想使用变量，你想在查询中使用cid，你可以用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

#1