filter (where ...) that works with Postgres but not with Redshift

Question

What is the alternative for Redshift to the filter (where...) that is possible in Postgres? I get the following error and cannot figure out whether this is not possible in Redshift or I have a syntax error: The database reported a syntax error: Amazon Invalid operation: syntax error at or near "(" Position: 1521;

select count(distinct "agent.id")  as "agent_cnt"
, count(distinct "agent.id") filter (where "agent.status" = 'active' and ("agent.date" between '2020-01-01' and current_date)) as "active_agent_cnt"
from "agent"

Redshift forked off from PostgreSQL before filter(where) was added. If Redshift had added this feature into their fork, it seems like they would have used the same syntax, so it probably does not exist. — jjanes

Gordon Linoff Gordon Linoff · Accepted Answer · 2020-04-22T15:57:50

Use a case expression:

count(distinct case when "agent.status" = 'active' and ("agent.date" between '2020-01-01' and current_date then "agent.id" end) as "active_agent_cnt"

Having periods in column names seems highly suspicious. I would strongly discourage such a naming convention.

filter (where ...) that works with Postgres but not with Redshift

2 Answers