Multi-row insert in many to many relationship in PostgreSQL

Question

I have got three below tables:

create table r_users(id int primary key, name varchar(30));
create table r_codes(id int primary key, name varchar(10));
create table user_code
(
user_id int, 
code_id int,
constraint pk_user_code primary key(user_id, code_id),
constraint fk_user foreign key(user_id) references r_users(id),
constraint fk_code foreign key(code_id) references r_codes(id)
)

Now I have some users and codes in the tables:

insert into r_users values(1, 'John'), (2, 'Roy'), (3, 'Sam'), (4, 'Lewis');
insert into r_codes values(1, 'A'), (2, 'B'), (3, 'C'), (4, 'D'), (5, 'K');

Now I want to insert the codes A, B, C, K to users John, Roy and Lewis such that the mentioned codes are inserted for each of the mentioned users as below:
The desired result is:

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 

Any help is appreciated in advance.

Answer 1

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<>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<>fiddle here

• McNets: @Lennart basically a cross join
• McNets: @Lennart yes, you're right

Answer 2

You are looking for the cartesian product. In SQL lingo this is a cross join:

select u.id, c.y
from r_users u
cross join (values (1), (2), (3), (5)) c (y) 
where u.id in (1,2,4);

Since you know that users 1,2,4 exists you can simplify it a bit:

select a.x, b.y
from (values (1), (2), (4)) a(x)
cross join (values (1), (2), (3), (5)) b(y);

All and all:

insert into user_code (user_id, code_id)
select a.user_id, b.code_id
from (values (1), (2), (4)) a(user_id)
cross join (values (1), (2), (3), (5)) b(code_id);