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_id and want to insert multiple subservice_id's at once. Is there a syntax in Postgres that will let me do something like this
insert into user_subservices(user_id, subservice_id) values(1, [1, 2, 3]);
How would I do this?
Multi-value insert syntax is:
insert into table_name values (1,1), (1,2), (1,3), (2,1);
When you need to specify columns:
insert into table_name (user_id, subservice_id) values
(1, 1),
(1, 2),
(1, 3),
(2, 1);
When you need to get the inserted id for example:
insert into table_name (user_id, subservice_id) values
(1, 1),
(1, 2),
(1, 3),
(2, 1)
returning id;
' ' for the text that you're inserting. so something like, insert into orders_cancel_reasons_infos values (1,1,'Changed my mind',1), (2,2,'Quality not satisfactory',1) will work. You can check this out for more on single and double-quotes.
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
unnest should be the accepted answer, slick as it is. Default syntax is as mentioned by Scott. If you are on my team and use unnest I will ask you why and if you don't have a good answer, I will ask you to change it to the standard syntaxA shorter version of krokodilko's answer:
insert into user_subservices(user_id, subservice_id)
values(1, unnest(array[1, 2, 3]));
insert into user_subservices(user_id, subservice_id) select 1, column_value from table(sys.odcinumberlist(1,2,3)).
insert into user_subservices(user_id, subservice_id) values(1, unnest(ARRAY(select id from subservices where name like '%test%')));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]);
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;
A good way to import values once but avoid duplicates is as follows:
insert into schema.mytable (col1,col2)
select col1, col2 from schema.mytable
UNION
values
('row1-col1','row1-col2'),
('row2-col1','row2-col2'),
('row3-col1','row3-col2')
except
select col1, col2 from schema.mytable;
Suppose you want to add the same set of subservice_ids for each of your user, you could make use of CROSS JOIN:
INSERT INTO user_subservices(user_id, subservice_id)
SELECT
users.id,
subservices.id
FROM users
-- Or narrow down the users i.e. only for users with id 1 and 3
-- FROM (SELECT id FROM users WHERE id IN (1, 3)) AS users
CROSS JOIN (
-- subservice ids to be created for each user
VALUES
(1),
(2),
(3)
) AS subservices(id);
See sqlfiddle.
You can remove the INSERT INTO statement to view the rows that would be created.
To be safe with your queries, don't forget to use transactions BEGIN and ROLLBACK/COMMIT.
For multiple values, this function might be helpful.
This function generates multiple values
const _multiInsert = arrOfValues => {
// removes lastCharacter
const _remLastChar = str => str.slice(0, str.length - 1);
let foramttedQuery = '';
arrOfValues.forEach(row => {
let newRow = '';
for (const val of Object.values(row)) {
let newValue = '';
if (typeof val === 'string') newValue = `'${val}',`;
else newValue = `${val},`;
newRow = newRow.concat(newValue);
}
foramttedQuery = foramttedQuery.concat(`(${_remLastChar(newRow)}),`);
});
return _remLastChar(foramttedQuery);
};
const arr_Of_Values = [
{
id: 1,
name: "SAMPLE_NAME_1",
},
{
id: 2,
name: "SAMPLE_NAME2",
}
]
const query_template = `INSERT INTO TABLE_NAME VALUES ${_multiInsert(arr_Of_Values)}`
console.log(query_template) 
generate_seriessee dba.stackexchange.com/a/89544/16892