syntax error at or near "'select to_char(application_date::timestamp, '"

Question

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

The above PostgreSQL EXECUTE statement is giving the below error:

ERROR: syntax error at or near "'select to_char(application_date::timestamp, '" LINE 1: EXECUTE 'select to_char(application_date::timestamp, 'Mon-YY... ^

********** Error **********

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

Any suggestions will be helpful.

Changed to below statement

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

But giving new error:

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

Expected Output: - Counts by month in Mon-YY format

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

If I do the below query:

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;

Generated output: (Note: Sorted by text, not by date)

"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

Problem: to_char() returns text and it sorts by text and not by date. So the output is jumbled rather than sorted by Mon-YY.

Answer 1

Do the aggregation in a derived table (aka "sub-query") that preserves the data type, then do the sorting in the outer query:

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('month', application_date) will normalize the application_date to the start of the month, but will retain the date data type, so that the sorting in the outer query works correctly.

I have no idea what the dynamic SQL in your question is supposed to do, but if you need to use that query for whatever reasons as dynamic SQL, you need to escape the single quotes by doubling them.

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

But using Postgres' dollar quoting would be easier:

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

Note that you can nest dollar quoted strings, so if that query is used inside a function, just use a different delimiter than you use for the function body (see the example in the manual)