Обсуждение: 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.
Hello
Simple plpgsql function can helpscreate or replace function unnest_rownum(anyarray)
returns table (id int, element anyelement) as $$
begin id := 1;
foreach element in array $1 loop
return next;
id := id + 1;
end loop;
return;
end $$ language plpgsql;
postgres=# select * from unnest_rownum(ARRAY[1,1,2,3,4,1,2,7]);
id | element
----+---------
1 | 1
2 | 1
3 | 2
4 | 3
5 | 4
6 | 1
7 | 2
8 | 7
(8 rows)
postgres=# select * from unnest_rownum(ARRAY['A','B','C']);
id | element
----+---------
1 | A
2 | B
3 | C
(3 rows)
Pavel
2014-02-19 20:57 GMT+01:00 AlexK <alkuzo@gmail.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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
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.
2014-02-19 21:55 GMT+01:00 AlexK <alkuzo@gmail.com>:
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.
I cannot to say, what will be in next years - but it is less probable -- plans for parallelism are related for SQL executor internals - like parallel sort - or parallel hashing.
we have no plans do plpgsql parallel - now works well - and it is relative simple to maintain it.
Regards
Pavel
--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-unnest-an-array-with-element-indexes-tp5792770p5792787.htmlSent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql