Select odd or even values from text array

user_241673 0
Viewed 195

How to select odd or even values from a text array in Postgres?

You can select by index (starts at 1)

select
text_array[1] as first
from (
  select '{a,1,b,2,c,3}'::text[] as text_array
) as x

There is not a native function for this: https://www.postgresql.org/docs/13/functions-array.html. I see Postgres supports modulo math (https://www.postgresql.org/docs/13/functions-math.html) but I'm not sure how to apply that here as the below is invalid:

select
text_array[%2] as odd
from (
  select '{a,1,b,2,c,3}'::text[] as text_array
) as x

The goal is to get {a,1,b,2,c,3} -> {a,b,c}. Likewise for even, {a,1,b,2,c,3} -> {1,2,3}.

Any guidance would be greatly appreciated!

postgresql sql
1 Answers

Generate a list of subscripts (generate_series expression for the odd ones) then extract the array values and aggregate back into arrays. Null values by even subscripts need to be filtered if the array length is odd. Here is an illustration. t CTE is a "table" of sample data.

with t(arr) as 
(
 values 
 ('{a,1,b,2,c,3}'::text[]),
 ('{11,12,13,14,15,16,17,18,19,20}'), -- even number of elements
 ('{21,22,23,24,25,26,27,28,29}') -- odd number of elements
)
select arr, 
       array_agg(arr[odd]) arr_odd,
       array_agg(arr[odd + 1]) filter (where arr[odd + 1] is not null) arr_even 
from t 
cross join lateral generate_series(1, array_length(arr, 1), 2) odd 
group by arr;
arr arr_odd arr_even
{21,22,23,24,25,26,27,28,29} {21,23,25,27,29} {22,24,26,28}
{a,1,b,2,c,3} {a,b,c} {1,2,3}
{11,12,13,14,15,16,17,18,19,20} {11,13,15,17,19} {12,14,16,18,20}

Or use these functions:

create function textarray_odd(arr text[]) returns text[] language sql as 
$$
 select array_agg(arr[i]) from generate_series(1, array_length(arr,1), 2) i;
$$;

create function textarray_even(arr text[]) returns text[] language sql as 
$$
 select array_agg(arr[i]) from generate_series(2, array_length(arr,1), 2) i;
$$;

select textarray_odd('{a,1,b,2,c,3}'); -- {a,b,c}
select textarray_even('{a,1,b,2,c,3}'); -- {1,2,3}

A more generic alternative:

create function array_odd(arr anyarray) returns anyarray language sql as 
$$
 select array_agg(v order by i)
 from unnest(arr) with ordinality t(v, i)
 where i % 2 = 1;
$$;
user_23020320
Related