159

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?

Martin Schröder
  • 4,176
  • 7
  • 47
  • 81
jhamm
  • 24,124
  • 39
  • 105
  • 179
  • 1
    If you want to insert them from a sequence, or using `generate_series` see http://dba.stackexchange.com/a/89544/16892 – rogerdpack Jul 08 '16 at 01:12

7 Answers7

257

Multi-value insert syntax is:

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

But krokodilko's answer is much slicker.

yallie
  • 2,200
  • 1
  • 28
  • 26
Scott Marlowe
  • 8,490
  • 3
  • 23
  • 21
  • With either of these methods, is there a way to also return the `id`? – davidtgq Nov 17 '17 at 07:36
  • 3
    Yes, use returning. "insert into table (id, yada) values (1,2),(9,22) returning id;" etc – Scott Marlowe Nov 18 '17 at 09:37
  • 1
    Also nowadays a CTE aka WITH statement often works well. – Scott Marlowe Nov 18 '17 at 09:38
  • Integer values can able insert as like above. But while inserting text using above method its getting error like column does't exist. `insert into orders_cancel_reasons_infos values (1,1,"Changed my mind",1), (2,2,"Quality not satisfactory",1), (3,3,"Incompatible or not useful",1), (4,4,"Damaged product but shipping box is good",1), (5,5,"Items arrived too late",1), (6,6,"Missing part or accessories",1), (7,7,"Product and shipping box are damaged",1), (8,8,"Wrong item was sent",1), (9,9,"Defective item",1), (10,10,"Inaccurate description",1), (11,11,"Other",1);` – sankar muniyappa Dec 20 '18 at 11:18
  • 4
    @sankarmuniyappa you'll have to use single quotes `' '` 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](https://stackoverflow.com/questions/41396195/what-is-the-difference-between-single-quotes-and-double-quotes-in-postgresql) out for more on single and double-quotes. – dr0pdb May 13 '20 at 05:11
  • 1
    This has the advantage of not being specific to Postgres too. – Adam Wise Nov 29 '22 at 14:53
  • Not sure why krokodilko's answer is 'much slicker'... I think this should be the accepted answer as this is SQL standard syntax and also much easier to understand. – Stijn de Witt Jul 03 '23 at 11:06
94

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: http://www.sqlfiddle.com/#!15/9a006/1

krokodilko
  • 35,300
  • 7
  • 55
  • 79
55

A shorter version of krokodilko's answer:

insert into user_subservices(user_id, subservice_id) 
values(1, unnest(array[1, 2, 3]));
yallie
  • 2,200
  • 1
  • 28
  • 26
  • I think this is more expressive of intent than the accepted answer. Is this standard SQL or PostgreSQL-specific? – Bernard Mar 24 '17 at 08:09
  • 4
    Both answers are PostgreSQL-specific. The accepted answer is perhaps more easily translated to other databases, Oracle for example: `insert into user_subservices(user_id, subservice_id) select 1, column_value from table(sys.odcinumberlist(1,2,3))`. – yallie Mar 25 '17 at 22:08
  • Thanks, this makes a lot more sense and is more readable than the accepted answer in my opinion. – frosty Jan 10 '18 at 22:53
  • 2
    A way to use this with information from another table: `insert into user_subservices(user_id, subservice_id) values(1, unnest(ARRAY(select id from subservices where name like '%test%')));` – Efren Mar 08 '18 at 07:09
20

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]);
Andreas Hultgren
  • 14,763
  • 4
  • 44
  • 48
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;
Caleb Mayhew
  • 58
  • 11
Envek
  • 4,426
  • 3
  • 34
  • 42
0

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;
MikeR
  • 11
  • 2
-2

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)
maazakn
  • 384
  • 2
  • 10