How to insert multiple values ​​into a postgres table at once?

Question

I have a table in which I try to update several values ​​at once. Here is the table layout:

Column | Type | Modifiers ---------------+---------+----------- user_id | integer | subservice_id | integer | 

I have user_id and want to insert several subservice_id . Is there any syntax in Postgres that will allow me to do something like this

 insert into user_subservices(user_id, subservice_id) values(1, [1, 2, 3]); 

How should I do it?

Answer 1

Try:

 INSERT INTO user_subservices(user_id, subservice_id) SELECT 1 id, x FROM unnest(ARRAY[1,2,3,4,5,6,7,8,22,33]) x 

Demo: http://www.sqlfiddle.com/#!15/9a006/1

Answer 2

The syntax for adding multiple values ​​is:

 insert into table values (1,1), (1,2), (1,3), (2,1); 

But crocodile's answer is much more complex.

Answer 3

A slightly related answer, because I constantly find this question every time I try to remember this solution. Insert multiple rows with multiple columns :

 insert into user_subservices (user_id, subservice_id) select * from unnest(array[1, 2], array[3, 4]); 

Answer 4

The shorter version of krokodilko answers:

 insert into user_subservices(user_id, subservice_id) values(1, unnest(array[1, 2, 3])); 

Answer 5

A more reliable example is when you need to insert multiple rows into some table for each row in another table:

 INSERT INTO user_subservices (user_id, subservice_id) SELECT users.id AS user_id, subservice_id FROM users CROSS JOIN unnest(ARRAY[1,2,3]) subservice_id; 

`` ``