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
– Jonathan Peace, Commented Apr 4, 2021 at 3:00
You don't show a set of values. You show a row with many columns. What result do you need exactly? — Erwin Brandstetter
– Erwin Brandstetter, Commented Apr 4, 2021 at 3: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(): postgresql.org/docs/9.1/… - use a pivot to turn this table back into a single row, using crosstab(): stackoverflow.com/questions/3002499/postgresql-crosstab-query/… — moilejter
– moilejter, Commented Apr 4, 2021 at 4:14
@moilejter this sounds a bit beyond my very basic SQL skills, but I will have a go, thanks! — Jonathan Peace
– Jonathan Peace, Commented Apr 4, 2021 at 5:14

vinkomlacic · Accepted Answer · 2023-03-17 09:17:38Z

0

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.

answered Mar 17, 2023 at 9:17

vinkomlacic

1,8971 gold badge12 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to iterate over an array in a Postgresql query?

1 Answer 1

Example

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Example

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related