I have the same question that is posted here, but am curious how this would be achieved in a PostgreSQL dialect - Presto SQL - How can i get all possible combination of an array?
Basically from an array that looks something like [1,2,3], the result I'm looking for is
[1]
[2]
[3]
[1,2]
[1,3]
[2,1]
[2,3]
[3,1]
[3,2]
How can I achieve this in Postgres dialect?
Thanks much in advance.
Here is an approach using a recursive cte:
with recursive
data as (select array[1, 2, 3] arr),
keys as (select generate_subscripts(d.arr, 1) as rn from data d),
cte as (
select d.arr initial_arr, array[d.arr[k.rn]] new_arr, array[k.rn] used_rn
from data d
cross join keys k
union all
select initial_arr, c.new_arr || c.initial_arr[k.rn], used_rn || k.rn
from cte c
inner join keys k on not (k.rn = any(c.used_rn))
)
select new_arr from cte
Starting from a given array (here defined by cte data), the idea is to extract all array indexes with generate_subscript() (cte keys), and then recursively walk the array. We need to keep track of the keys that we already used so we pick each value only once.
This produces all possible combinations. If you want only the 1-element and 2-elements combinations, you can add a where clause in the recursive cte, like where array_length(c.new_ar) < 2.
To me, all permutations means more than you are producing -- sizes of 0 and 3 as well.
The following accomplishes this assuming the array has no duplicates (as in your example):
with recursive ar as (
select array[1,2,3] as ar
),
cte as (
select array[el] as els, array_remove( ar, el) as ar
from ar cross join
unnest(ar) as el
union all
select els || el, array_remove(ar, el)
from cte cross join
unnest(ar) as el
)
select * from cte;
Here is a db<>fiddle. You can filter to remove the size 0 and 3, if that is what you really want.
If your array an have duplicates, you would need to specify what you want.
