PostgreSQL combinations as distinct from permutations

Question

Can't figure out how to make combinations of arrays without repetitions.

   INPUT is an array of n variables LIKE '{A,B,C,D,E}'
   Variable A is always single array LIKE '{"A"}' or '{"D,C"}' or '{"D,A,B"}' etc..

   What I need is to combine INPUT with A(i)

 EXAMPLE:
     1. A = '{"B"}'       --> att='{"B,A","B,C","B,D","B,E"}'
     2. A = '{"B,C"}'     --> att='{"B,C,A","B,C,D","B,C,E"}'
     3. A = '{"B,C,A"}'   --> att='{"B,C,A,D","B,C,A,E"}'
     4. A = '{"B,C,A,D"}' --> att='{"B,C,A,D,E"}'

As far I have this:

WITH A(i) AS (SELECT * FROM unnest(ARRAY['A,B'])),
     B(j) AS (SELECT * FROM unnest(ARRAY['A','B','C','D'])),

     cte AS ( SELECT A.i ||','|| B.j
              FROM A
              CROSS JOIN   B 
            )
     SELECT ARRAY ( SELECT * FROM cte) INTO att;

But it makes duplicity:

"{"A,B,A","A,B,B","A,B,C","A,B,D"}"

Since you frankly label this as a school project, you should really solve this yourself. Hint: since you are working with strings, have a look at the string functions. — Patrick
– Patrick, Commented May 27, 2015 at 2:52
What you're looking for is exactly "combinations" as distinct from "permutations". Algorithms to do this are well documented. — Craig Ringer
– Craig Ringer, Commented May 27, 2015 at 7:20
... also there are numerous existing answers if you search for "postgresql combinations" on Stack Overflow. What makes this different? (BTW, please don't build arrays using string concatenation like this; use unnest and array_agg or the ARRAY[...] constructor — Craig Ringer
– Craig Ringer, Commented May 27, 2015 at 7:53
As @CraigRinger said (and the OP in the question): use the unnest() function. To avoid multiple self-joins, and multiple a.x <> b.y clauses, you could use a recursive CTE (using candidate_element <> ALL( cte_array_expression_here) And restrict the maximum number of elements by limiting the recursion depth. — joop
– joop, Commented May 28, 2015 at 11:37

Adam · Accepted Answer · 2015-05-29 19:13:32Z

0

WITH  RECURSIVE 

     A(i) AS (SELECT * FROM unnest(ARRAY['A,B'])),
     B(j) AS (SELECT * FROM unnest(ARRAY['A','B','C','D'])),

     cte AS (  SELECT j AS combo, j, 1 AS ct 
               FROM B 
             UNION ALL 
               SELECT cte.combo ||','||B.j, B.j, ct + 1 
               FROM cte, B
               WHERE ct <= 4
                AND position(B.j in cte.combo) = 0
            )
    , com AS ( SELECT A.i ||','|| B.j AS cmb
               FROM A
               CROSS JOIN B)

     SELECT ARRAY(SELECT cmb FROM cte,com where cte.combo = com.cmb)

answered May 29, 2015 at 19:13

Adam

1051 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL combinations as distinct from permutations

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related