Issue with Varchar datatype in postgres

Grafana PostgreSQL
1

Hi,

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 ??

2

Hi Jospeh, your query looks good. Could you please share your table schema as well? Also, could you share the full error message that you get - Column metric must be of type UNKNOWN, TEXT, VARCHAR, CHAR. metric column name: category type: VARCHAR but datatype is… <- what is the data type.

3

Hi,

Thanks for the fast response. The complete error report is :

{
  "xhrStatus": "complete",
  "request": {
    "method": "POST",
    "url": "api/tsdb/query",
    "data": {
      "from": "1562146369269",
      "to": "1569922369269",
      "queries": [
        {
          "refId": "A",
          "intervalMs": 7200000,
          "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

2 Likes
4

Awesome! I am very glad that you were able to solve the issue Joseph! :tada:

5

very very very good! I was trying solve that … 1 week trying! now I’ve write the query with null check, and everything it’s working! thanks a lot :grinning: :grinning: :grinning: :grinning: :grinning: :grinning: :grinning: :grinning: