Using multi-value variable with "All" option with PostgresSQL

[ctron]

I am using Grafana 8.0.6 in combination with PostgreSQL.

I created a variable, with the options: “multi-value” and “include all option”. The recommended way 1 is to use an “IN” clause to filter for this variable, like so:

SELECT hostname FROM host  WHERE region IN($region)

Under the hood however, “all” actually lists all elements. Assuming the example from above, having regions a,b,c, the SQL statement would expand to:

SELECT hostname FROM host  WHERE region IN('a', 'b', 'c')

That may actually be inefficient, because “all” could simply be represented as:

SELECT hostname FROM host

The author of the SQL query knows that, Grafana most likely not.

Still, this works great, if you already have data in your table. However, there are scenarios where the keys are dynamic, and so the initial key set might be empty.

What happens then, is that Grafana expands this to an empty array:

SELECT hostname FROM host  WHERE region IN()

Creating an invalid SQL statement in the process.

I would expect that there is some out-of-the-box way around this.

[ctron]

After searching a bit for ways to deal with this on a PostgreSQL level, I found the following alternative:

SELECT hostname FROM host  WHERE region = ANY(ARRAY[$region]::varchar[])

I think this solution should at least be mentioned in the documentation as a way to deal with this issue.