agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: David Johnston <polobo@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: Re: How to unnest an array with element indexes
Date: Wed, 19 Feb 2014 11:32:23 -0800 (PST)
Message-ID: <1392838343105-5792771.post@n5.nabble.com> (raw)
In-Reply-To: <1392837956043-5792770.post@n5.nabble.com>
References: <1392837956043-5792770.post@n5.nabble.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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

view thread (8+ messages)  latest in thread

Message-ID: <1392838343105-5792771.post@n5.nabble.com>
Permalink:  ../1392838343105-5792771.post@n5.nabble.com/
Also on:    postgresql.org/message-id/1392838343105-5792771.post@n5.nabble.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: polobo@yahoo.com
  Subject: Re: How to unnest an array with element indexes
  In-Reply-To: <1392838343105-5792771.post@n5.nabble.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox