PostgreSQL: How to return a subarray dynamically using array slices in postgresql

Question

I need to sum a subarray from an array using postgresql.

I need to create a postgresql query that will dynamically do this as the upper and lower indexes will be different for each array.

These indexes will come from two other columns within the same table.

I had the below query that will get the subarray:

SELECT 
    SUM(t) AS summed_index_values
FROM 
    (SELECT UNNEST(int_array_column[34:100]) AS t 
     FROM array_table
     WHERE id = 1) AS t;

...but I then realised I couldn't use variables or SELECT statements when using array slices to make the query dynamic:


int_array_column[SELECT array_index_lower FROM array_table WHERE id = 1; : SELECT array_index_upper FROM array_table WHERE id = 1;]

...does anyone know how I can achieve this query dynamically?

user330315 · Accepted Answer · 2019-11-05 14:42:02Z

1

No need for sub-selects, just use the column names:

SELECT SUM(t) AS summed_index_values
FROM (
    SELECT UNNEST(int_array_column[tb.array_index_lower:tb.array_index_upper])  AS t 
    FROM array_table tb
    WHERE id = 1
) AS t;

Note that it's not recommended to use set-returning functions (unnest) in the SELECT list. It's better to put that into the FROM clause:

SELECT sum(t.val)
FROM (
    SELECT t.val
    FROM array_table tb
       cross join UNNEST(int_array_column[tb.array_idx_lower:array_idx_upper])  AS t(val)
    WHERE id = 1
) AS t;

answered Nov 5, 2019 at 14:42

user330315

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

2 Comments

suspense_hey Over a year ago

Thank you very much. Can you the second query to me a bit? I'm new to sql in general and don't understand the cross join or why it's not recommended to use set-returning functions (UNNEST) in the SELECT list or why It's better to put that into the FROM clause.

user330315 Over a year ago

@JoshQuinn: see the manual: postgresql.org/docs/current/…

Collectives™ on Stack Overflow

PostgreSQL: How to return a subarray dynamically using array slices in postgresql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related