Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I insert multiple values into a postgres table at once?

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?

like image 830
jhamm Avatar asked Dec 28 '13 13:12

jhamm


People also ask

How do I insert multiple values in one table?

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.

Can we insert multiple values to a table at a time?

Answer. Yes, instead of inserting each row in a separate INSERT statement, you can actually insert multiple rows in a single statement.

How do I insert multiple data in one row?

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.

How do I fill a table in PostgreSQL?

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.


1 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.

like image 109
Scott Marlowe Avatar answered Nov 18 '22 16:11

Scott Marlowe