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?
The INSERT statement also allows you to insert multiple rows into a table using a single statement as the following: INSERT INTO table_name(column1,column2…) VALUES (value1,value2,…), (value1,value2,…), … In this form, you need to provide multiple lists of values, each list is separated by a comma.
Answer. Yes, instead of inserting each row in a separate INSERT statement, you can actually insert multiple rows in a single statement.
INSERT-SELECT-UNION query to insert multiple records Thus, we can use INSERT-SELECT-UNION query to insert data into multiple rows of the table. The SQL UNION query helps to select all the data that has been enclosed by the SELECT query through the INSERT statement.
There are generally three methods in PostgreSQL with which you can fill a table with data: Use the INSERT INTO command with a grouped set of data to insert new values. Use the INSERT INTO command in conjunction with a SELECT statement to insert existing values from another table.
Multi-value insert syntax is:
insert into table values (1,1), (1,2), (1,3), (2,1);
But krokodilko's answer is much slicker.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With