'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?

postgresqlsql-insertmultiple-records


Solution 1:[1]

Multi-value insert syntax is:

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

But krokodilko's answer is much slicker.

Solution 2:[2]

A shorter version of krokodilko's answer:

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

Solution 3:[3]

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

Solution 4:[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;

Solution 5:[5]

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)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 yallie
Solution 2 yallie
Solution 3 Andreas Hultgren
Solution 4 Caleb Mayhew
Solution 5

Related Questions