Can Postgresql access ARRAY slice with step size ie. return a downsampled array?

Question

I've searched all over and read through the Postgresql array docs. Perhaps this is supported in postgres. I can get a down-sampled version of a numpy array like this:

numpy.array([1,2,3,4,5,6,7,8,9,0])[0:9:2] # the 2 here is the step size for the slice
out: array([1, 3, 5, 7, 9])

Can one do something similar for a postgresql array?

SELECT (ARRAY[1,2,3,4,5,6,7,8,9,10])[1:10:2]; // Don't forget 1 based indexing!

The above doesn't work. I'd expect this operation could be very fast in an array with elements of a fixed size.

Jeremy · Accepted Answer · 2020-10-30 03:04:14Z

2

No, postgres does not support syntax like you would find in python for skipping elements of an array. You would have to unnest it, to split it into rows, and then aggregate it again. Using WITH ORDINALITY in conjunction with unnest allows you to get the index of each element of the array.

SELECT array_agg(x order by n) 
FROM unnest(ARRAY[1,2,3,4,5,6,7,8,9,10]) with ordinality as o(x, n) 
WHERE (n-1) % 2 = 0;
  array_agg
-------------
 {1,3,5,7,9}

You could use (n-1) % 3 for every third element, etc.

You could even create a simple function:

CREATE OR REPLACE FUNCTION array_step(p_array anyarray, p_step int) 
RETURNS anyarray AS $$
    SELECT array_agg(x order by n) FROM unnest(p_array) WITH ORDINALITY AS o(x, n) WHERE (n-1) % p_step = 0;
$$
LANGUAGE SQL
IMMUTABLE
PARALLEL SAFE;

select array_step(ARRAY[1,2,3,4,5,6,7,8,9,10], 3);
 array_step
------------
 {1,4,7,10}

select array_step((ARRAY[1,2,3,4,5,6,7,8,9,10])[2:8], 3);
 array_step
------------
 {2,5,8}

answered Oct 30, 2020 at 3:04

Jeremy

6,77322 silver badges21 bronze badges

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

1 Comment

Anthony Killick Over a year ago

Thanks- very nice solution. This seems like it could work well although a little less convenient than I was hoping. I'll give it a try. It seems like it might have to check every value of the ordinality rather than just skip a known step size to obtain a downsampled like I was hoping but maybe it has some 'under the hood' optimization that I don't anticipate. I clearly am no expert about how postgres works.

Collectives™ on Stack Overflow

Can Postgresql access ARRAY slice with step size ie. return a downsampled array?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related