postgresql 如何一次将多个值插入到 postgres 表中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20815028/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I insert multiple values into a postgres table at once?
提问by jhamm
I have a table that I am trying to update multiple values at once. Here is the table schema:
我有一个表，我试图一次更新多个值。这是表架构：
Column | Type | Modifiers
---------------+---------+-----------
user_id | integer |
subservice_id | integer |
I have the user_idand want to insert multiple subservice_id's at once. Is there a syntax in Postgresthat will let me do something like this
我有user_id并且想subservice_id一次插入多个's。是否有语法Postgres可以让我做这样的事情
insert into user_subservices(user_id, subservice_id) values(1, [1, 2, 3]);
How would I do this?
我该怎么做？
采纳答案by krokodilko
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
回答by Scott Marlowe
Multi-value insert syntax is:
多值插入语法是：
insert into table values (1,1), (1,2), (1,3), (2,1);
But krokodilko's answer is much slicker.
但是 krokodilko 的回答要巧妙得多。
回答by yallie
A shorter version of krokodilko's answer:
krokodilko 答案的简短版本：
insert into user_subservices(user_id, subservice_id)
values(1, unnest(array[1, 2, 3]));
回答by Andreas Hultgren
A slightly related answer because I keep finding 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]);
回答by Envek
More robust example, for when you need to insert multiple rows into some table for every 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;
