Split array by portions in PostgreSQL

Question

I need split array by 2-pair portions, only nearby values.
For example I have following array:

select array[1,2,3,4,5]

And I want to get 4 rows with following values:

{1,2}

{2,3}

{3,4}

{4,5}

Can I do it by SQL query?

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

select a
from (
    select array[e, lead(e) over()] as a
    from unnest(array[1,2,3,4,5]) u(e)
) a
where not exists (
    select 1
    from unnest(a) u (e)
    where e is null
);
   a   
-------
 {1,2}
 {2,3}
 {3,4}
 {4,5}

Split array by portions in PostgreSQL

Sign up