Looking for a little help understanding what the Where clause of this SQL query is doing. This is used to define a query layer in an ArcPro map. DBMS is postgresql
a.id, a.valid_date, a.id, a.name, a.net, a.elevation, a.mph, a.local_time, a.shape
obs.gusts AS a
I understand that the last line is a cast function, but I do not understand what the "r" represents and how it's filtering the data.
Thanks in advance
Most of the SQL statement is straightforward, but there are a couple of things about the WHERE clause that are odd to me. How are you extracting this query? Are you copying and pasting it or typing it from what you seen on a screen?
I am stumped, then. You are correct that double colons are historical Postgres syntax for type casting, and type casting can be chained using the old syntax:
Postgres isn't my primary DBMS, but 2 things are throwing me: 1) type casting requires an expression to the left of the double colon, which appears to not exist; and 2) single quotes are usually used for slicing arrays but that doesn't make sense given the syntax.
This may be some clever use of Postgres operators, but clever is the death of readability and maintainability.
Does the query layer give the same results if you remove the WHERE clause?
I share all of these thoughts and feelings with you!
I did try removing the WHERE clause and it DOES change the results.
I would expect that it is filtering a time field, but I haven't been able to find a pattern yet.
The range parameters are a powerful feature of a query layer as per this thread which goes to the same link as you:
This might be of use to you if you want to use range parameters in a web application.