How to filter via variable for substring in postgresql data source?

Grafana Dashboards
1

  • What Grafana version and what operating system are you using?
    Grafana - 9.3.6 | OS - MacOS

  • What are you trying to achieve?
    I have a postgresql table which contains a name string column. I have defined a variable in the dashboard which takes a keyword. I want to filter all the names which contain that substring variable.

  • How are you trying to achieve it?
    I tried 3 approaches which failed. Not sure, what I am doing wrong:

  1. Using LIKE:
    SELECT * FROM students WHERE name LIKE CONCAT(‘%’, $keyword, ‘%’)

  2. Using POSITION:
    SELECT * FROM students WHERE POSITION($keyword IN name) > 0;

  3. Using STRPOS:
    SELECT * FROM students WHERE STRPOS($keyword, name) > 0;

  • What happened?
    Unfortunately I am getting error in all the three cases for some reason.
  • What did you expect to happen?
  • I expected the output where the results are filtered by the keyword.
  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.
    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 ","

Great if someone could help me out. Thanks

2

What do you see when you click on Query Inspector. Please post results svreen grab from that

3

Now getting a new error where psql is considering the search variable as a column for some reason:

Screenshot 2024-02-06 at 5.32.37 AM
Screenshot 2024-02-06 at 5.32.37 AM2138×516 19.3 KB

In the above I had set cream as the variable value

4

click here and see what the query looks like
image

1 Like
5

Got it. The output there is it shows the expanded query as:
SELECT * FROM students WHERE name LIKE CONCAT('%', cream, '%')
I got the reason I think, it is not converting the value into a valid string. How can that be done then?

1 Like
6

Check this out