How to iterate over an array in a Postgresql query?

Question

I have constructed a naive query that works for my application, which returns multiple values from a table that contains an array of reals.

SELECT 
  "timestamp" AS "time",
  type as metric,
  id,
  values[80],
  values[81],
  values[82],
  values[83],
  values[84],
  values[85],
  values[86],
  values[87],
  values[88],
  values[89],
  values[91],
  values[92],
  values[93],
  values[94],
  values[95],
  values[96],
  values[97],
  values[98],
  values[99],
  values[100],
  values[101]

FROM test_table
WHERE
  "timestamp" BETWEEN '2021-04-03T15:05:56.928Z' AND '2021-04-03T15:10:56.928Z'
ORDER BY 1

However, for obvious reasons, I want to avoid long lists of values[n] in my query, especially as in fact I need to get up to 1000 values.

I know that it is possible to iterate over an array but everything I try to replace the list of array subscripts along the lines of

  FOR i IN ARRAY values
  LOOP 
      values [i]
  END LOOP;

errors out.

As an obvious SQL noob, can someone suggest how this might query might be done more elegantly?

because the result is fed as bucketed timeseries data into Grafana which expects a set of values — Jonathan Peace, Apr 04 '21 at 03:00
You don't show a `set of values`. You show a row with many columns. What result do you need *exactly*? — Erwin Brandstetter, Apr 04 '21 at 03:38
I can imagine doing this in multiple steps: - turn the single row that you want to get from test_table into a table, expanding the arrai values into one value per row in this new table, using generate_subscripts(): https://www.postgresql.org/docs/9.1/functions-srf.html#FUNCTIONS-SRF-SUBSCRIPTS - use a pivot to turn this table back into a single row, using crosstab(): https://stackoverflow.com/questions/3002499/postgresql-crosstab-query/11751905#11751905 — moilejter, Apr 04 '21 at 04:14
@moilejter this sounds a bit beyond my very basic SQL skills, but I will have a go, thanks! — Jonathan Peace, Apr 04 '21 at 05:14
@ErwinBrandstetter, the result that I need is a row with n (eg512) columns, because that is what Grafana expects in order to display bucketed data. More precisely. I am trying to produce a waterfall/ heatmap plot of of an FFT result, which is stored as an array of n bins, and Grafana needs a timeseries with n columns to display a single Y bucket; passing an array does not work alas. - this works as shown above, but is clumsy so I was trying to find a better way, thanks — Jonathan Peace, Apr 04 '21 at 05:15
Follow up here if you need more help ... I have to confess that I haven't used postgresql myself - so it would be a learning experience for both of us :-). But maybe someone else will chime in :-p — moilejter, Apr 04 '21 at 06:12
If you need a 100 columns, you need to write a 100 expressions one way or another. There is no way around that. — , Apr 04 '21 at 06:31

score 0 · Answer 1 · answered Mar 17 '23 at 09:17

You can use PostgreSQL's unnest for this purpose. It's not gonna be exactly the same as your example. The values from the array won't be unpacked in more columns, they will be unpacked in more rows, instead.

Here are the docs.

Example

SELECT 
  "timestamp" AS "time",
  type as metric,
  id,
  unnest(values[80:101])
FROM test_table
WHERE
  "timestamp" BETWEEN '2021-04-03T15:05:56.928Z' AND '2021-04-03T15:10:56.928Z'
ORDER BY 1

The timestamp, type, and id will be duplicated across the rows.

Note that the slicing of the array is inclusive for both bounds (unlike the one in Python). See more in this answer by mu is too short.

How to iterate over an array in a Postgresql query?

1 Answers1

Example