I have a postgresql table which contains a name string column. I have defined a variable in the grafana dashboard which takes a search term keyword. I want to filter all the names which contain that substring variable.
I tried 3 approaches which failed. Not sure, what I am doing wrong:
Using LIKE:
SELECT * FROM students WHERE name LIKE CONCAT(‘%’, $keyword, ‘%’)
Using POSITION:
SELECT * FROM students WHERE POSITION($keyword IN name) > 0;
Using STRPOS:
SELECT * FROM students WHERE STRPOS($keyword, name) > 0;
These are the errors that I am receiving:
Case 1:
db query error: pq: syntax error at or near ","
Case 2:
db query error: pq: syntax error at or near "IN"
Case 3:
db query error: pq: syntax error at or near ","
What can be the right way to implement this in a panel in grafana?
Your problem is caused by lack of quotes around variable usage. As official documentation states, they are added only in some limited cases:
From Grafana 4.3.0 to 4.6.0, template variables are always quoted automatically. If your template variables are strings, do not wrap them in quotes in where clauses.
From Grafana 4.7.0, template variable values are only quoted when the template variable is a
multi-value.
I doubt that you are using ancient version of Grafana, or that you have multi-select, since you search by substring.
So use '$keyword' instead of $keyword in any of your queries, and everything should be fine.
To ease investigation of such issues, Grafana allows to see final query after all substitutions took place, before it is sent to data source. To do this, go to the menu of the panel: Inspect > Query, and the click Refresh on the shown page.