Re: A unique pairs version of UNNEST() ?

On Tue, Jan 5, 2016 at 1:59 AM, Alban Hertroys <haramrae@gmail.com> wrote:

> with list_of_ids as (
>   select unnest(list_of_ids) as id from table
> )
> select a.id, b.id
>   from list_of_ids a, list_of_ids b
>  where b.id > a.id;

Or, to morph this to array output (which the OP seemed to want):

test=# with list_of_ids as (
test(#   select unnest('{1,2,3,4,5}'::int[]) as id
test(# )
test-# select array [a.id, b.id]
test-#   from list_of_ids a, list_of_ids b
test-#  where b.id > a.id;
 array
-------
 {1,2}
 {1,3}
 {1,4}
 {1,5}
 {2,3}
 {2,4}
 {2,5}
 {3,4}
 {3,5}
 {4,5}
(10 rows)

Nothing in that not already mentioned; just putting it all
together.

The OP mentioned wanting a count, but that wasn't too clear to me;
using a window function to number the rows, changing the comparison
from > to >= while excluding self-matches should make that pretty
easy.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company