CODEWITHSUNDEEP
sqlfilteramazon-redshiftwhere-clause
ColRow
ColRow

Reputation: 79

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

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"

Upvotes: 2

Views: 5993

Answers (2)

James Cox
James Cox

Reputation: 1

Not sure why having periods in names is suspicious. It's identifying a column by the table, and with redshift (and PG) it's also plausible it has a schema to use too, so it'd be realistic (but maybe unusual) to see schema.table.column identifiers.

Upvotes: -1

Gordon Linoff
Gordon Linoff

Reputation: 1269743

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.

Upvotes: 6

Related Questions