Multi-row insert in many to many relationship in PostgreSQL
You can obtain a Cartesian product using a LATERAL JOIN.

Have a look at Postgres docs.




select u.id, c.id
from   r_users u
left join lateral (select id from r_codes where id in (1, 2, 3, 5)) c on true
where  u.id in (1,2,4);



id | id
-: | -:
 1 |  1
 1 |  2
 1 |  3
 1 |  5
 2 |  1
 2 |  2
 2 |  3
 2 |  5
 4 |  1
 4 |  2
 4 |  3
 4 |  5






insert into user_code
select u.id, c.id
from   r_users u
left join lateral (select id from r_codes where id in (1, 2, 3, 5)) c on true
where  u.id in (1,2,4);



12 rows affected






select * from user_code;



user_id | code_id
------: | ------:
      1 |       1
      1 |       2
      1 |       3
      1 |       5
      2 |       1
      2 |       2
      2 |       3
      2 |       5
      4 |       1
      4 |       2
      4 |       3
      4 |       5



db&lt;>fiddle here

As @Lennart has pointed out, in this case you can get the same result by using left join.




insert into user_code
select    u.id, c.id
from      r_users u
left join r_codes c
on        c.id in (1, 2, 3, 5)
where     u.id in (1,2,4);



12 rows affected






select * from user_code;



user_id | code_id
------: | ------:
      1 |       1
      1 |       2
      1 |       3
      1 |       5
      2 |       1
      2 |       2
      2 |       3
      2 |       5
      4 |       1
      4 |       2
      4 |       3
      4 |       5



db&lt;>fiddle here