From: | Andreas Kretschmer <akretschmer(at)spamfence(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: A unique pairs version of UNNEST() ? |
Date: | 2016-01-05 06:46:50 |
Message-ID: | 20160105064650.GB29256@tux |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Andy Colson <andy(at)squeakycode(dot)net> wrote:
>> So in an example where list_of_ids is {1,2,3,4,5} I would essentially get:
>>
>> {1, 2}
>> {1, 3}
>> {1, 4}
>> {1, 5}
>> {2, 3}
>> {2, 4}
>> {2, 5}
>> {3, 4}
>> {3, 5}
>> {4, 5}
>>
>>
>> Any tips? Thanks!
>>
>> --
>> Wells Oliver
>> wells(dot)oliver(at)gmail(dot)com <mailto:wellsoliver(at)gmail(dot)com>
>
> if you could convert the array to a table then cross join it. Something
> like:
>
> select a.*, b.*
> from unnest( {1,2,3,4,5} ) a
> cross join unnest( {1,2,3,4,5} ) b
>
> -Andy
not exactly the expectet result (and syntactically wrong), better
solution:
test=*# select (a.*, b.*) from unnest( array[1,2,3,4,5] ) a cross join unnest( array[1,2,3,4,5] ) b where a < b;
row
-------
(1,2)
(1,3)
(1,4)
(1,5)
(2,3)
(2,4)
(2,5)
(3,4)
(3,5)
(4,5)
(10 rows)
or
test=*# select array[a.*, b.*] from unnest( array[1,2,3,4,5] ) a cross join unnest( array[1,2,3,4,5] ) b where a < b;
array
-------
{1,2}
{1,3}
{1,4}
{1,5}
{2,3}
{2,4}
{2,5}
{3,4}
{3,5}
{4,5}
(10 rows)
(matches the excpected result)
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Munro | 2016-01-05 06:57:36 | Re: planner does not detect same-as-default collation. |
Previous Message | Michael Paquier | 2016-01-05 06:35:27 | Re: Streaming replication stacked. |