SQL: Update with all possible combinations

Question

I have a relation

+-----+----+
| seq | id |
+-----+----+
|   1 | A1 |
|   2 | B1 |
|   3 | C1 |
|   4 | D1 |
+-----+----+

and want to join it in PostgreSQL with

+----+-------+
| id | alter |
+----+-------+
| B1 | B2    |
| D1 | D2    |
+----+-------+

so I get all possible combinations of replacement (i.e. the Cartesian product of replacing more or less). So group 1 has no update,group 2 only B2, group 3 only D2 and group 4 both B2 and D2.

The end should look like this, but should be open to more (like an extra D3 for D1)

+-------+-----+----+
| group | seq | id |
+-------+-----+----+
|     1 |   1 | A1 |
|     1 |   2 | B1 |
|     1 |   3 | C1 |
|     1 |   4 | D1 |
|     2 |   1 | A1 |
|     2 |   2 | B2 |
|     2 |   3 | C1 |
|     2 |   4 | D1 |
|     3 |   1 | A1 |
|     3 |   2 | B1 |
|     3 |   3 | C1 |
|     3 |   4 | D2 |
|     4 |   1 | A1 |
|     4 |   2 | B2 |
|     4 |   3 | C1 |
|     4 |   4 | D2 |
+-------+-----+----+

EDIT:

Another possible replacement table could be

+----+-------+
| id | alter |
+----+-------+
| B1 | B2    |
| D1 | D2    |
| D1 | D3    |
+----+-------+

could should result in 6 groups (I hope I haven't forgot a case)

+-------+-----+----+
| group | seq | id |
+-------+-----+----+
|     1 |   1 | A1 |
|     1 |   2 | B1 |
|     1 |   3 | C1 |
|     1 |   4 | D1 |
|     2 |   1 | A1 |
|     2 |   2 | B2 |
|     2 |   3 | C1 |
|     2 |   4 | D1 |
|     3 |   1 | A1 |
|     3 |   2 | B2 |
|     3 |   3 | C1 |
|     3 |   4 | D2 |
|     4 |   1 | A1 |
|     4 |   2 | B2 |
|     4 |   3 | C1 |
|     4 |   4 | D3 |
|     5 |   1 | A1 |
|     5 |   2 | B1 |
|     5 |   3 | C1 |
|     5 |   4 | D2 |
|     6 |   1 | A1 |
|     6 |   2 | B1 |
|     6 |   3 | C1 |
|     6 |   4 | D3 |
+-------+-----+----+

If you have instead three replacements like

+----+-------+
| id | alter |
+----+-------+
| B1 | B2    |
| C1 | C2    |
| D1 | D3    |
+----+-------+

it'll result in 8 groups. What I tried so far was not really helpful:

WITH a as (SELECT * FROM (values (1,'A1'),(2,'B1'), (3,'C1'), (4,'D1')   ) as a1(seq, id) )
, b as (SELECT * FROM (values ('B1','B2'), ('D1','D2')) as b1(id,alter) )
---------
SELECT row_number() OVER (PARTITION BY a.id) as g, * FROM 
a
CROSS JOIN  b as b1
CROSS JOIN  b as b2
LEFT JOIN b as b3 ON a.id=b3.id
ORDER by g,seq;

and I am glad for better suggestions with the title.

Answer 1

So group 1 has no update,group 2 only B2, group 3 only D2 and group 4 both B2 and D2.

Since the logic of this statement is not in a table, I decided to add this logic to table c, which is adding 3 new columns to the existing table a, depending on which selection of field had to be considered.

WITH a as (SELECT * FROM (values (1,'A1'),(2,'B1'), (3,'C1'), (4,'D1')   ) as a1(seq, id) )
, b as (SELECT * FROM (values ('B1','B2'), ('D1','D2')) as b1(id,alter) )
, c as (
SELECT a.seq, a.id,
COALESCE(b1.alter,a.id) as id2,
COALESCE(b2.alter,a.id) as id3,
COALESCE(b3.alter,a.id) as id4
FROM a
LEFT JOIN (SELECT * FROM b WHERE b.alter='B2') b1 ON a.id = b1.id
LEFT JOIN (SELECT * FROM b WHERE b.alter='D2') b2 ON a.id = b2.id
LEFT JOIN (SELECT * FROM b WHERE b.alter IN ('B2','D2')) b3 ON a.id = b3.id)
, d as (SELECT * FROM (values (1),(2), (3), (4)   ) as d1(gr) )



SELECT d.gr,
CASE d.gr
   WHEN 1 THEN c.id
   WHEN 2 THEN c.id2
   WHEN 3 THEN c.id3
   WHEN 4 THEN c.id4 END as id

FROM d
CROSS JOIN  c
ORDER by d.gr, c.seq

Answer 2

What you need

After additional info from your comments, it seems like this is your case:

You have toll stations with a given number of booths:

CREATE TABLE station (
  station text PRIMARY KEY
, booths  int NOT NULL  -- number of cashiers in station
);
INSERT INTO station VALUES 
  ('A', 1)
, ('B', 2)
, ('C', 1)
, ('D', 3);

For a given route, say A --> B --> C --> D you want to generate all possible paths, taking booth numbers into account. I suggest an SQL function with a recursive CTE like:

CREATE OR REPLACE FUNCTION f_pathfinder(_route text[])
  RETURNS TABLE (grp int, path text[]) LANGUAGE sql STABLE PARALLEL SAFE AS
$func$
WITH RECURSIVE rcte AS (
   SELECT cardinality($1) AS hops, 1 AS hop, ARRAY[s.station || booth] AS path
   FROM   station s, generate_series(1, s.booths) booth
   WHERE  s.station = $1[1]

   UNION ALL
   SELECT r.hops, r.hop + 1, r.path || (s.station || booth)
   FROM   rcte  r
   JOIN   station s ON s.station = _route[r.hop + 1], generate_series(1, s.booths) booth
   WHERE  r.hop < r.hops
   )
SELECT row_number() OVER ()::int AS grp, path
FROM   rcte r
WHERE  r.hop = r.hops;
$func$;

Simple call:

SELECT * FROM f_pathfinder('{A,B,C,D}'::text[]);

Result:

 grp | path
---: | :--------
   1 | {1,1,1,1}
   2 | {1,1,1,2}
   3 | {1,1,1,3}
   4 | {1,2,1,1}
   5 | {1,2,1,2}
   6 | {1,2,1,3}

Or with unnested arrays (result like you show in question):

SELECT grp, seq, booth
FROM   f_pathfinder('{A,B,C,D}'::text[])
     , unnest(path) WITH ORDINALITY AS x(booth, seq);  -- ①

Result:

grp | seq | booth
--: | --: | :----
  1 |   1 | A1   
  1 |   2 | B1   
  1 |   3 | C1   
  1 |   4 | D1   
  2 |   1 | A1   
  2 |   2 | B1   
  2 |   3 | C1   
  2 |   4 | D2   
  3 |   1 | A1   
  3 |   2 | B1   
  3 |   3 | C1   
  3 |   4 | D3   
  4 |   1 | A1   
  4 |   2 | B2   
  4 |   3 | C1   
  4 |   4 | D1   
  5 |   1 | A1   
  5 |   2 | B2   
  5 |   3 | C1   
  5 |   4 | D2   
  6 |   1 | A1   
  6 |   2 | B2   
  6 |   3 | C1   
  6 |   4 | D3   

db<>fiddle here

The number of variants is growing quickly with the number of stops in your route. It's M1*M2* .. *Mn with Mn being the number of booths for the nth station.

① About ORDINALITY:

• PostgreSQL unnest() with element number

What you asked (originally)

Seems like you want to apply all possible combinations from the set of changes listed in the replacement table rpl to the target table tbl.

With just two rows, forming the 4 (2^n) possible combinations is simple. For a general solution, I suggest a basic combinatorics function to generate all combinations. There are innumerable ways. Here is a pure SQL function:

CREATE OR REPLACE FUNCTION f_allcombos(_len int)
  RETURNS SETOF bool[] LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
$func$
WITH RECURSIVE
   tf(b) AS (VALUES (false), (true))

 , rcte AS (
   SELECT 1 AS lvl, ARRAY[b] AS arr
   FROM   tf

   UNION ALL
   SELECT r.lvl + 1, r.arr || tf.b
   FROM   rcte r, tf 
   WHERE  lvl < _len
   )
SELECT arr
FROM   rcte
WHERE  lvl = _len;
$func$;

Similar to what's discussed here:

• Generate all combinations of given list of elements, sorted

Example for just 2 replacement rows:

SELECT * FROM f_allcombos(2);

{f,f}
{t,f}
{f,t}
{t,t}

Query

WITH effective_rpl AS (  -- ①
   SELECT *, count(alter) OVER (ORDER BY seq) AS idx  -- ②
   FROM   tbl LEFT JOIN rpl USING (id)
   )
SELECT c.grp, e.seq
     , CASE WHEN alter IS NOT NULL AND c.arr[e.idx] THEN e.alter  -- ③
            ELSE e.id END AS id
FROM   effective_rpl e
     , f_allcombos((SELECT count(alter)::int FROM effective_rpl))  -- ④
          WITH ORDINALITY AS c(arr, grp); -- ⑤

Produces your desired result exactly.

db<>fiddle here

① Some of the replacements may have no match in the target table; so determine effective replacements to begin with.

② count() only counts non-null values, so this can serve as index for the 1-based array returned from f_allcombos().

③ Only replace when a replacement is available, and the boolean array has true for the given index idx.

④ The CROSS JOIN multiplies the set of rows in the target table with the number of possible replacement combinations

⑤ I use WITH ORDINALITY to generate "group numbers". See:

• PostgreSQL unnest() with element number

We might wire that into the function directly, but I'd rather keep it generic.

Aside: "alter" is non-reserved in Postgres but a reserved word in standard SQL.