In this answer, Erwin Brandstetter says:
count(step OR NULL) OVER (ORDER BY date)
is the shortest syntax that also works in Postgres 9.3 or older.count()
only counts non-null values. In modern Postgres, the cleaner, equivalent syntax would be:count(step) FILTER (WHERE step) OVER (ORDER BY date)
I'm unsure of why count(step OR NULL)
is preferred though. In my query, I do the following. I renamed my variables to match his while maintaining the syntax.
CASE WHEN lag(id_type) OVER (ORDER BY date) <> id_type THEN 1 END AS step
We're counting the values returned by that. Note, that the case can only return 1, or null.
- if the two ARE NOT equal, 1 is returned.
- if they are equal, it returns null which isn't counted.
Erwin's answer has:
This assumes involved columns are
NOT NULL
. Else you need to do more.
So I'm even more confused. What is the point of adding count(step OR NULL)
what is this protecting our query against?
Could anyone break this down and perhaps show two examples with data wherein only one of them - the one with - count(x OR NULL)
works?