“' select to _ char(application _ date::timestamp，'”处或附近出现语法错误

Question

EXECUTE 'select to_char(application_date::timestamp, 'Mon-YY') as appl_month from my_schema.my_table;';

上面的PostgreSQL EXECUTE语句给出了以下错误：

错误：语法错误在“'选择to_char（application_date：：时间戳，'”行1：执行'选择to_char（application_date：：时间戳，'Mon-YY... ^

错误 **********

错误：“select to_char（application_date：：timestamp）”处或附近的语法错误SQL状态：42601字符：9

任何建议都会有所帮助。

改为以下语句

EXECUTE 'select to_char(application_date::timestamp, ' || quote_literal(Mon-YY) || ') from standard.npo_weekly_export;';

但是给出了新的错误:

ERROR:  syntax error at or near "'select to_char(application_date::timestamp, '"
LINE 1: EXECUTE 'select to_char(application_date::timestamp, ' || qu...
                ^
********** Error **********

ERROR: syntax error at or near "'select to_char(application_date::timestamp, '"
SQL state: 42601
Character: 9

预期产出：-Mon-YY格式的按月计数

Application month   Application #   Final Approval #    
Jan-17              1,000           800
Feb-17              1,010           808
Mar-17              1,020           816
Apr-17              1,030           824

如果我执行下面的查询:

select to_char(application_date, 'Mon-YY') as appl_month, 
    count(distinct application_id) as appl_count, 
    sum(final_approval_ind) as fa_count,
from my_schema.my_table 
group by appl_month 
order by appl_month;

生成的输出：（注：按文本排序，而不是按日期排序）

"Apr-17";94374;19953
"Apr-18";87446;20903
"Aug-17";102043;21536
"Aug-18";91107;20386
"Dec-17";63263;13755
"Dec-18";21358;74
"Feb-17";89447;18084
"Feb-18";75426;16144
"Jan-17";86103;16394
"Jan-18";79403;17766
"Jul-17";90380;18929
"Jul-18";85439;20186
"Jun-17";95596;20403
"Jun-18";85764;18707
"Mar-17";112929;23323
"Mar-18";91179;21841
"May-17";101907;22349
"May-18";90885;21550
"Nov-17";78284;16791
"Nov-18";80472;7656
"Oct-17";87955;18524
"Oct-18";82821;17056
"Sep-17";80740;17788
"Sep-18";75785;18009

问题:< code>to_char()返回文本，它按文本而不是按日期排序。所以输出是混乱的，而不是按蒙-YY分类的。

Answer 1

在保留数据类型的派生表（又名“子查询”）中进行聚合，然后在外部查询中进行排序：

select to_char(ap_month, 'Mon-YY') as appl_month
       appl_count, 
       fa_count
from (       
  select date_trunc('month', application_date) as ap_month, 
         count(distinct application_id) as appl_count, 
         sum(final_approval_ind) as fa_count,
  from my_schema.my_table 
  group by ap_month
) t
order by ap_month;

date_trunc（'月'，application_date）将把application-date，标准化为月初，但将保留日期数据类型，以便外部查询中的排序正确。

我不知道您的问题中的动态SQL应该做什么，但是如果出于任何原因需要使用该查询作为动态SQL，则需要通过将单引号加倍来转义单引号。

execute '
  select to_char(ap_month, ''Mon-YY'') as appl_month
         appl_count, 
         fa_count
  from (       
    select date_trunc(''month'', application_date) as ap_month, 
           count(distinct application_id) as appl_count, 
           sum(final_approval_ind) as fa_count,
    from my_schema.my_table 
    group by ap_month
  ) t
  order by ap_month;
'; -- end of dynamic SQL

但是使用Postgres的美元报价会更容易：

execute $dyn$
  select to_char(ap_month, 'Mon-YY') as appl_month
         appl_count, 
         fa_count
  from (       
    select date_trunc('month', application_date) as ap_month, 
           count(distinct application_id) as appl_count, 
           sum(final_approval_ind) as fa_count,
    from my_schema.my_table 
    group by ap_month
  ) t
  order by ap_month;
$dyn$; -- end of dynamic SQL

请注意，您可以嵌套美元引号字符串，因此如果在函数中使用该查询，只需使用与函数体不同的分隔符即可(参见手册中的示例)