PostgreSQL – Multi-Row Insert in Many-to-Many Relationship

postgresqlquery

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.

Best Answer

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

Related Question