CODEWITHSUNDEEP
sqlpostgresqlgrafanamonitoringgrafana-variable
albdev
albdev

Reputation: 73

GRAFANA db query error: pq: column "name" does not exist

I am trying to get some graph about different entries in a column in Grafana, but Grafana tries to recognize the antry as a column instead of a column value.

I have the following setup:

A PostgreSQL Database (v11).

A table called People. This table contains 3 columns: id, name, age.

I want to get the average age for each name contained within the name column.

I have defined a query variable called firstname in Grafana, which is returned by the query:

SELECT DISTINCT(name) from People;

It shows then a dropdown list containing all the unique names.

To get the average age for each name, i write the following query in Grafana:

SELECT AVG(age), pit_date FROM People WHERE name = $firstname GROUP BY pit_date LIMIT 15;

But i get the error: db query error: pq: column "selena" does not exist

My understanding is that Grafana is trying to locate teh column "selena" instead of "selena" within column "name".

Do you have any idea where i am wrong and how to solve this problem? I would appreciate it very much.

Upvotes: 3

Views: 1195

Answers (3)

KamiSama
KamiSama

Reputation: 89

I think that in Grafana, you need to use '' to indicate template variables within your query. Try this sintax

SELECT AVG(age), pit_date FROM People WHERE name = '$firstname' GROUP BY pit_date LIMIT 15

I hope it helps bro

Upvotes: 1

albdev
albdev

Reputation: 73

What worked for me is the following query:

SELECT AVG(age), pit_date FROM People WHERE name in ( $firstname ) GROUP BY pit_date LIMIT 15;

It is also important to check the Option Include All in the variable settings, for the changes to appear.

Upvotes: 0

markalex
markalex

Reputation: 13431

Problem is caused by lack of quotes around you variable substitution.

After Grafana substituted firstname variable where clause of you query looks like this: name = selena. From SQL perspective selena is expected to be column name. To make it a string you need to wrap substitution with single quotes:

SELECT AVG(age), pit_date 
FROM People 
WHERE name = '$firstname' 
GROUP BY pit_date 
LIMIT 15

Upvotes: 4

Related Questions