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

  • 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

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

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

In the above I had set cream as the variable value

click here and see what the query looks like
image

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

Check this out