Nick Nick - 1 year ago 202
SQL Question

Split array by portions in PostgreSQL

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?

Answer Source
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}
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download