Re: A unique pairs version of UNNEST() ?

On Tue, Jan 5, 2016 at 1:59 AM, Alban Hertroys <haramrae(at)gmail(dot)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