Postgresql - select column based on condition-CodePudding

In this query the 'Daily' in the case will be replaced by a variable. I am not able to make this query work. I want to have the date column being either a day, a week a month or a year based on the value of the variable. but it is giving me various errors..

CASE types date and double precison cannot be matched
syntax error near "as"

what am I doing wrong?

select 
case 'Daily' 
    when 'Daily' then DATE(to_timestamp(e.startts)) as "Date",
    when 'Weekly' then DATE_PART('week',to_timestamp(e.startts)) as "Date",
    when 'Monthly' then to_char(to_timestamp(e.startts), 'mm/yyyy') as "Date",
    when 'Yearly' then to_char(to_timestamp(e.startts), 'yyyy') as "Date",
end
sum(e.checked)
from entries e
WHERE
e.startts >= date_part('epoch', '2020-10-01T15:01:50.859Z'::timestamp)::int8
and e.stopts <  date_part('epoch', '2021-11-08T15:01:50.859Z'::timestamp)::int8
group by "Date"

CodePudding user response：

CASE ... END is an expression. An expression must have a well-defined data type, so PostgreSQL makes sure that the expressions in the THEN clause have the same data type (or at least compatible ones).

You would need a type cast, probably to text, in the first two branches:

... THEN CAST (date(to_timestamp(e.startts)) AS text)

But it would be much better to use to_char in all branches – there are format codes for everything you need.

An expression can have no alias, only an entry in the SELECT or FROM list can. So you need to append AS "Date" at the end of the CASE ... END expression, not somewhere in the middle.