How to insert multiple rows with a single query?

[zettam]

I have n number of items. I would like to add them all with a single insert query.

insert into testtable(id,name) values (1,"jack"),(2,"john"),(3,"jill");

I have an array for my rows, and I like to add them all in. So, if I provide pg the correct string (after manually forming it in a variable) it works perfectly fine, but this all seemed a bit pointless to me. I expect pg to handle arrays for me as well, but I'm unable to find the option for that. So I'm asking, is this possible?

How can I insert multiple rows from an array of rows, with a single insert query with this library?

Would love to know. Thank you.

[vitaly-t]

No such thing supported. This is not an ORM, this library just executes queries.

[zettam]

hm, okay.
What's the best way to handle this situation in this case? Sending multiple queries is pretty costy hen you can do the same with a single query.

[vitaly-t]

You can concatenate your inserts. However, since this library doesn't provide a separate query formatting, you can't really do it correctly.

You can do it with pg-promise though. Article Performance Boost echoes all your concerns nicely.

[langpavel]

Prepare your insert statement

[rpedela]

Using pg-format with a nested array:

var format = require('pg-format');

var values = [
    [ 1, 'jack' ],
    [ 2, 'john' ],
    [ 3, 'jill' ],
];
console.log(format('INSERT INTO test_table (id, name) VALUES %L', values));
// INSERT INTO test_table (id, name) VALUES ('1', 'jack'), ('2', 'john'), ('3', 'jill')

Disclaimer: I wrote pg-format.

[awenger]

You could also do something like:

var query = 'INSERT INTO table (SELECT (data->>'c1')::int, (data->>'c2')::text FROM (SELECT json_array_elements($1::json) AS data) tmp)'
var data = JSON.stringify([{c1: 1, c2: 'text1'},{c1: 2, c2: 'text2'}])

client.query(query, [data], ...)

Formatted version of the query:

INSERT INTO table (
  SELECT 
    -- extract the props from the json object so that they match the table cols
    (data->>'c1')::int, (data->>'c2')::text
  FROM (
    -- expand the json array
    SELECT json_array_elements($1::json) AS data
  ) tmp
)

[joskuijpers]

@zettam Did any of the above solve the problem for you?

[th3h3lly34h]

You can user this plugin https://www.npmjs.com/package/mysql-insert-multiple

[charmander]

@x0days That’s for MySQL, not PostgreSQL.

@zettam:

pg.query(
    "INSERT INTO testtable (id, name) SELECT * FROM UNNEST ($1::int[], $2::text[])",
    [
        [1, 2, 3],
        ["Jack", "John", "Jill"],
    ]
)