agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedHow to unnest an array with element indexes
8+ messages / 4 participants
[nested] [flat]
* How to unnest an array with element indexes
@ 2014-02-19 19:25 AlexK <alkuzo@gmail.com>
2014-02-19 19:32 ` Re: How to unnest an array with element indexes David Johnston <polobo@yahoo.com>
0 siblings, 1 reply; 8+ messages in thread
From: AlexK @ 2014-02-19 19:25 UTC (permalink / raw)
To: pgsql-sql
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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to unnest an array with element indexes
2014-02-19 19:25 How to unnest an array with element indexes AlexK <alkuzo@gmail.com>
@ 2014-02-19 19:32 ` David Johnston <polobo@yahoo.com>
2014-02-19 19:57 ` Re: How to unnest an array with element indexes AlexK <alkuzo@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: David Johnston @ 2014-02-19 19:32 UTC (permalink / raw)
To: pgsql-sql
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-tp5792770p579277...
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to unnest an array with element indexes
2014-02-19 19:25 How to unnest an array with element indexes AlexK <alkuzo@gmail.com>
2014-02-19 19:32 ` Re: How to unnest an array with element indexes David Johnston <polobo@yahoo.com>
@ 2014-02-19 19:57 ` AlexK <alkuzo@gmail.com>
2014-02-19 20:08 ` Re: How to unnest an array with element indexes Pavel Stehule <pavel.stehule@gmail.com>
2014-02-19 20:16 ` Re: How to unnest an array with element indexes Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 8+ messages in thread
From: AlexK @ 2014-02-19 19:57 UTC (permalink / raw)
To: pgsql-sql
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-tp5792770p579277...
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to unnest an array with element indexes
2014-02-19 19:25 How to unnest an array with element indexes AlexK <alkuzo@gmail.com>
2014-02-19 19:32 ` Re: How to unnest an array with element indexes David Johnston <polobo@yahoo.com>
2014-02-19 19:57 ` Re: How to unnest an array with element indexes AlexK <alkuzo@gmail.com>
@ 2014-02-19 20:08 ` Pavel Stehule <pavel.stehule@gmail.com>
2014-02-19 20:55 ` Re: How to unnest an array with element indexes AlexK <alkuzo@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Pavel Stehule @ 2014-02-19 20:08 UTC (permalink / raw)
To: AlexK <alkuzo@gmail.com>; +Cc: pgsql-sql
Hello
Simple plpgsql function can helps
create 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)
Regards
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-tp5792770p579277...
> 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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to unnest an array with element indexes
2014-02-19 19:25 How to unnest an array with element indexes AlexK <alkuzo@gmail.com>
2014-02-19 19:32 ` Re: How to unnest an array with element indexes David Johnston <polobo@yahoo.com>
2014-02-19 19:57 ` Re: How to unnest an array with element indexes AlexK <alkuzo@gmail.com>
2014-02-19 20:08 ` Re: How to unnest an array with element indexes Pavel Stehule <pavel.stehule@gmail.com>
@ 2014-02-19 20:55 ` AlexK <alkuzo@gmail.com>
2014-02-20 04:17 ` Re: How to unnest an array with element indexes Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: AlexK @ 2014-02-19 20:55 UTC (permalink / raw)
To: pgsql-sql
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-tp5792770p579278...
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to unnest an array with element indexes
2014-02-19 19:25 How to unnest an array with element indexes AlexK <alkuzo@gmail.com>
2014-02-19 19:32 ` Re: How to unnest an array with element indexes David Johnston <polobo@yahoo.com>
2014-02-19 19:57 ` Re: How to unnest an array with element indexes AlexK <alkuzo@gmail.com>
2014-02-19 20:08 ` Re: How to unnest an array with element indexes Pavel Stehule <pavel.stehule@gmail.com>
2014-02-19 20:55 ` Re: How to unnest an array with element indexes AlexK <alkuzo@gmail.com>
@ 2014-02-20 04:17 ` Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Pavel Stehule @ 2014-02-20 04:17 UTC (permalink / raw)
To: AlexK <alkuzo@gmail.com>; +Cc: pgsql-sql
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-tp5792770p579278...
> 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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to unnest an array with element indexes
2014-02-19 19:25 How to unnest an array with element indexes AlexK <alkuzo@gmail.com>
2014-02-19 19:32 ` Re: How to unnest an array with element indexes David Johnston <polobo@yahoo.com>
2014-02-19 19:57 ` Re: How to unnest an array with element indexes AlexK <alkuzo@gmail.com>
@ 2014-02-19 20:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-02-19 21:53 ` Re: How to unnest an array with element indexes AlexK <alkuzo@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Tom Lane @ 2014-02-19 20:16 UTC (permalink / raw)
To: AlexK <alkuzo@gmail.com>; +Cc: 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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to unnest an array with element indexes
2014-02-19 19:25 How to unnest an array with element indexes AlexK <alkuzo@gmail.com>
2014-02-19 19:32 ` Re: How to unnest an array with element indexes David Johnston <polobo@yahoo.com>
2014-02-19 19:57 ` Re: How to unnest an array with element indexes AlexK <alkuzo@gmail.com>
2014-02-19 20:16 ` Re: How to unnest an array with element indexes Tom Lane <tgl@sss.pgh.pa.us>
@ 2014-02-19 21:53 ` AlexK <alkuzo@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: AlexK @ 2014-02-19 21:53 UTC (permalink / raw)
To: pgsql-sql
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-tp5792770p579280...
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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2014-02-20 04:17 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-02-19 19:25 How to unnest an array with element indexes AlexK <alkuzo@gmail.com>
2014-02-19 19:32 ` David Johnston <polobo@yahoo.com>
2014-02-19 19:57 ` AlexK <alkuzo@gmail.com>
2014-02-19 20:08 ` Pavel Stehule <pavel.stehule@gmail.com>
2014-02-19 20:55 ` AlexK <alkuzo@gmail.com>
2014-02-20 04:17 ` Pavel Stehule <pavel.stehule@gmail.com>
2014-02-19 20:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-02-19 21:53 ` AlexK <alkuzo@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox