Your Location is: Home > Postgresql-9.5

ERROR: syntax error at or near “dec” LINE 22: … WHEN to_char(t.work_date,'mm')=12 THEN t.work_day END.) dec

From: South Korea View: 1645 Anirban Sengupta 

Question

select  sum(CASE WHEN to_char(t.work_date,'mm')=12 THEN t.work_day END ) dec
FROM employee t;

Best answer

The immediate problem is that dec is a reserved keyword so it can't be used as an identifier. You either need to use double quotes as "dec" or use a different alias.

But you have another error in your code:

to_char() returns a character value, which can't be compare to an integer, so you need to compare it with '12'

select sum(case when to_char(t.work_date,'mm') = '12' then t.work_day end ) as "dec"
from employee t;

Another way of writing this in Postgres would be to use the filter() clause for an aggregate. I prefer this, as it makes things more readable (at least in my opinion).

select sum(t.work_day) filter (where to_char(t.work_date,'mm') = '12') as "dec"
from employee t;