Обсуждение: How to unnest an array with element indexes

Given an array such as ARRAY[1.1,1.2], I need to select both values and
indexes, as follows:

1;1.1
2;1.2

The following query does what I want for a simple example:

with pivoted_array AS(
select unnest(ARRAY[1.1,1.2])
)
select ROW_NUMBER() OVER() AS element_index, unnest as element_value
from pivoted_array

Is ROW_NUMBER() OVER() guaranteed to always return array's index? If not,
how should I predictably/deterministically do it?



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/How-to-unnest-an-array-with-element-indexes-tp5792770.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

AlexK wrote
> Given an array such as ARRAY[1.1,1.2], I need to select both values and
> indexes, as follows:
> 
> 1;1.1
> 2;1.2
> 
> The following query does what I want for a simple example:
> 
> with pivoted_array AS(
> select unnest(ARRAY[1.1,1.2])
> )
> select ROW_NUMBER() OVER() AS element_index, unnest as element_value
> from pivoted_array
> 
> Is ROW_NUMBER() OVER() guaranteed to always return array's index? If not,
> how should I predictably/deterministically do it?

9.4 will provide for this capability directly.  For earlier releases as long
as the next and only thing you do after unnesting the array is apply the
window function the order will be consistent - the rows will be seen by the
window in array order.  You must not perform any other joins until the row
numbers have been assigned.  It is best to use a pair of CTE/WITH queries to
accomplish this and then use the result of the second CTE in the main query.

If your need is much more complicated than the simple example provided you
may wish to give something more close to your actual need for some to opine
on.

David J.







--
View this message in context:
http://postgresql.1045698.n5.nabble.com/How-to-unnest-an-array-with-element-indexes-tp5792770p5792771.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

David,

The array stores a time series of values for consecutive days. All I need is
take an array such as ARRAY[1.1,1.2] and return to the client the following

series_start_date + (array_index-1), array_value

Based on what you are saying, the following should do it:

with pivoted_array AS(
select unnest(ARRAY[1.1,1.2])
),
indexed_array AS(
select        row_number()OVER() AS element_index,        unnest as element_value
from pivoted_array)
SELECT        (DATE '2014-02-19' + INTERVAL '1d'*(element_index-1)) AS
series_date,        element_value AS series_value
FROM indexed_array

Can you confirm that this behavior is guaranteed and documented. I could not
find it in the docs.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/How-to-unnest-an-array-with-element-indexes-tp5792770p5792774.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

AlexK <alkuzo@gmail.com> writes:
> David,
> The array stores a time series of values for consecutive days. All I need is
> take an array such as ARRAY[1.1,1.2] and return to the client the following

> series_start_date + (array_index-1), array_value

> Based on what you are saying, the following should do it:

> with pivoted_array AS(
> select unnest(ARRAY[1.1,1.2])
> ),
> indexed_array AS(
> select 
>         row_number()OVER() AS element_index, 
>         unnest as element_value
> from pivoted_array)
> SELECT 
>         (DATE '2014-02-19' + INTERVAL '1d'*(element_index-1)) AS
> series_date, 
>         element_value AS series_value
> FROM indexed_array

That sure seems like the hard way, as well as uncertainly correct.
I'd use something involving generate_subscripts(), for example if
tab.arraycol is the source of the array data:

select i, arraycol[i] from tab, lateral generate_subscripts(arraycol, 1) as i;

If you're using a version of Postgres that doesn't have LATERAL, it's
a bit more complicated but you can do it with a subquery.
        regards, tom lane

Pavel,

This works for me, thank you!

Presumably foreach is guaranteed to iterate array elements in order: "The
elements are visited in storage order, regardless of the number of array
dimensions", quoted from this:

http://www.postgresql.org/docs/9.2/static/plpgsql-control-structures.html

Since parallelism is coming up, will this behavior stay as documented? My
arrays are as big as 20K-30K elements, maybe more.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/How-to-unnest-an-array-with-element-indexes-tp5792770p5792787.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

Tom,

I am using 9.3, and this thing worked for me. It seems to be the simplest,
so I will go for it. Thanks!



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/How-to-unnest-an-array-with-element-indexes-tp5792770p5792808.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.