Postgresql - select column based on condition

Question

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"

Laurenz Albe · Accepted Answer · 2021-11-10 07:36:16Z

1

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.

answered Nov 10, 2021 at 7:36

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql - select column based on condition

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related