Plotting a data array from PostgresQL TimescaleDB array

Grafana
1

I am a total beginner to both Grafana and also postgresql. Please forgive my possible newbie question. I have searched in the community but could not find an answer.

I have some data in an 8 element array of temperatures stored in postgresql.

CREATE TABLE temparray (
time        TIMESTAMPTZ       NOT NULL,
temp       real[8]
)

INSERT INTO temparray(time, temp) VALUES(NOW(), ARRAY[100,200,350,400,500,200,600,450]);

SELECT * FROM temparray;

                                 time              |               temp                

                -------------------------------+-----------------------------------

 2020-03-27 16:31:41.119538-10 | {100,200,350,400,500,200,600,450}

When I try to plot in Grafana, it does not see this variable. It only shows “value”. When I type the name of the variable temp, I get the following error.

Value column must have numeric datatype, column: temp type: string value: {100,200,350,400,500,200,600,450}

I can edit the sql query and plot individual variables such as temp[1], temp[2], etc. one at a time. I can also put all of them but then it treats it all as one variable.

Is there a way to plot all 8 of variables on the same plot with different colors. I suppose I could save the data in the array as temp1, temp2, etc. instead of an array and then those could be picked. Unfortunately, doing that for somewhat large arrays may not be as practical.

Thanks.

Edit to add: I was able to get all 8 array elements to show on the same plot by using some SQL. e.g temp[1] as “temp1”, temp[2] as "temp2’, …, temp[8] as “temp8”. I wonder if this can be automated.

2

Came across the same issue whilst trying to display FFT results in a heatmap.

In my case I have a real[512] array of frequency bins and to get these plotted to a heatmap I have to use a query like

SELECT 
  "timestamp" AS "time",
  values[0],
  values[1],
  values[2],
  values[3],
       |
       |
       |
  values[511]

which is very cumbersome.
Per the OP , I originally tried returning the array, eg

SELECT 
  "timestamp" AS "time",
  values

but my real array is thrown out as being of type string by Grafana which seems like a bug?

Anyone know how I could go about fixing this?

3

It seems like you could use Postgresql’s unnest() for this.