PostgreSQL: generate all possible strings of arbitrary length from a set of characters

Question

I'm trying to generate all possible strings of length x using a fixed set of characters in PostgreSQL. For the simple case of x = 2 I can use the query below, but I cannot figure out how to do it for an arbitrary length (I'm assuming this will involve recursion):

with characters (c) as (
  select unnest(array['a', 'b', 'c', 'd'])
) 
select concat(c1.c, c2.c)
from characters c1
cross join characters c2

This generates aa, ab, ac, ad, ba, bb, bc, bd, etc.

score 2 · Accepted Answer · answered Nov 06 '20 at 15:39

Using recursive CTE:

with recursive characters (c) as (
  select unnest(array['a', 'b', 'c', 'd'])
), param(val) AS (
   VALUES (4)   -- here goes param value
), cte AS (
  select concat(c1.c, c2.c) AS c, 2 AS l
  from characters c1
  cross join characters c2
  UNION ALL
  SELECT CONCAT(c1.c, c2.c), l + 1
  FROM cte c1
  CROSS JOIN characters c2
  WHERE l <= (SELECT val FROM param)
)
SELECT c
FROM cte
WHERE l = (SELECT val FROM param)
ORDER BY c;

db<>fiddle demo

PostgreSQL: generate all possible strings of arbitrary length from a set of characters

1 Answers1