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?

1

6 Answers

Multi-value insert syntax is:

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

But krokodilko's answer is much slicker.

5

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:

A shorter version of krokodilko's answer:

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

4

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]);

1

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;

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) 

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy