I am really new to grafana and trying to develop a dashboard with postgres. But getting the following error in grafana.
SQL Query:
SELECT Count(*) as count,
tm.category::VARCHAR ,
Time_bucket(‘1 weeks’, ts.updation_time) AS TIME
FROM tasks ts
INNER JOIN tasks_metadata tm
ON ts.task_id = tm.task_id
AND ts.project_id = tm.project_id
AND ts.status = ‘open’
GROUP BY tm.category, TIME;
Issue :
Column metric must be of type UNKNOWN, TEXT, VARCHAR, CHAR. metric column name: category type: VARCHAR but datatype is
Grafana version : 6.3.3
Can somebody please help me with this ??
"maxDataPoints": 960,
"datasourceId": 10,
"rawSql": "SELECT Count(*) as count, \n tm.category ,\n\t Time_bucket('4 weeks', ts.updation_time) AS TIME \nFROM tasks ts \n INNER JOIN tasks_metadata tm \n ON ts.task_id = tm.task_id \n AND ts.project_id = tm.project_id \n AND ts.status = 'open' \n AND $__timeFilter(ts.updation_time)\nGROUP BY tm.category, TIME\nORDER BY TIME;\n\n",
"format": "time_series"
"response": {
"results": {
"A": {
"error": "Column metric must be of type UNKNOWN, TEXT, VARCHAR, CHAR. metric column name: category type: VARCHAR but datatype is <nil>",
"refId": "A",
"meta": {
"sql": "SELECT Count(*) as count, \n tm.category ,\n\t Time_bucket('4 weeks', ts.updation_time) AS TIME \nFROM tasks ts \n INNER JOIN tasks_metadata tm \n ON ts.task_id = tm.task_id \n AND ts.project_id = tm.project_id \n AND ts.status = 'open' \n AND ts.updation_time BETWEEN '2019-07-03T09:32:49.269Z' AND '2019-10-01T09:32:49.269Z'\nGROUP BY tm.category, TIME\nORDER BY TIME;\n\n"
"series": null,
"tables": null
"message": "Column metric must be of type UNKNOWN, TEXT, VARCHAR, CHAR. metric column name: category type: VARCHAR but datatype is <nil>"
I have resolved the problem by rewriting the query with null check. I think the null values are creating some problem. The updated query is :
SELECT Count(*) as count,
tm.category ,
Time_bucket('4 weeks', ts.updation_time) AS TIME
FROM tasks ts
INNER JOIN tasks_metadata tm
ON ts.task_id = tm.task_id
AND ts.project_id = tm.project_id
AND ts.status = 'open'
AND $__timeFilter(ts.updation_time)
AND tm.category is not null
GROUP BY tm.category, TIME
ORDER BY TIME;
Is it an expected behavior ? Table schema is given below :
CREATE TABLE public.tasks_metadata
task_id character varying(25) COLLATE pg_catalog."default" NOT NULL,
project_id character varying(25) COLLATE pg_catalog."default" NOT NULL,
planned_finish_date timestamp with time zone NOT NULL,
priority integer,
task_name character varying(500) COLLATE pg_catalog."default" NOT NULL,
category character varying(50) COLLATE pg_catalog."default",
sub_category character varying(50) COLLATE pg_catalog."default",
CONSTRAINT tasks_metadata_pkey PRIMARY KEY (task_id, project_id)
Regards,
Joseph
