How to insert multiple rows with a single query?

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.

Prepare your insert statement

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.

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
)

@zettam

@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"],
    ]
)

This is not an ORM, this library just executes queries.

You don't need an ORM for that. PostgreSQL supports this natively, as stated in their docs

INSERT INTO films (code, title, did, date_prod, kind) VALUES
    ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
    ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

Since Postgres supports multi-record inserts using syntax.

 INSERT INTO videos (title, author) VALUES 
    ('Peru is a nice country.', 'limaGuy'), 
    ('Breaking bad is a great show.', 'mikeGuy'), 
    ('I like the winter.', 'novemberGuy');

I wrote a helper function called expand. It creates the query placeholders.

// expand(3, 2) returns "($1, $2), ($3, $4), ($5, $6)" 
function expand(rowCount, columnCount, startAt=1){
  var index = startAt
  return Array(rowCount).fill(0).map(v => `(${Array(columnCount).fill(0).map(v => `$${index++}`).join(", ")})`).join(", ")
}

// flatten([[1, 2], [3, 4]]) returns [1, 2, 3, 4]
function flatten(arr){
  var newArr = []
  arr.forEach(v => v.forEach(p => newArr.push(p)))
  return newArr
}

var videos = [["Peru is a nice country.", "limaGuy"], ["Breaking bad is a great show.", "mikeGuy"], ["I like the winter.", "novemberGuy"]]

Query(
    `INSERT INTO videos (title, author) VALUES ${expand(videos.length, 2)}`, 
    flatten(videos)
)

I wrote a helper function called expand. It creates the query placeholders.

// expand(3, 2) returns "($1, $2), ($3, $4), ($5, $6)" 
function expand(rowCount, columnCount, startAt=1){
  var index = startAt
  return Array(rowCount).fill(0).map(v => `(${Array(columnCount).fill(0).map(v => `$${index++}`).join(", ")})`).join(", ")
}

// flatten([[1, 2], [3, 4]]) returns [1, 2, 3, 4]
function flatten(arr){
  var newArr = []
  arr.forEach(v => v.forEach(p => newArr.push(p)))
  return newArr
}

This is great.
Since ES6 you can simplify using Array.prototype.flat() instead of the flatten function

const arr = [[1, 2], [3, 4]]
console.log(arr.flat()) // [1, 2, 3, 4]

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
)

is there any explanation why you use tmp as alias when it's not used anywhere?

@mymirai-nikki

is there any explanation why you use tmp as alias when it's not used anywhere?

@mymirai-nikki

SELECT FROM (SELECT);

ERROR: subquery in FROM must have an alias

is there any explanation why you use tmp as alias when it's not used anywhere?

@mymirai-nikki

SELECT FROM (SELECT);

ERROR: subquery in FROM must have an alias

I tried without tmp:

INSERT INTO registered_groups
SELECT (data->>'periode')::text, (data->>'group_id')::int, (data->>'user_id')::int FROM (SELECT json_array_elements($1::json) AS data)
ON CONFLICT (periode, group_id, user_id) DO NOTHING

and no error occurred?

@mymirai-nikki

@mymirai-nikki Ah, that requirement was dropped in PostgreSQL 16.

Navigation Menu

Repository navigation

How to insert multiple rows with a single query? #957

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

How to insert multiple rows with a single query? #957

Description

Activity

vitaly-t commented on Mar 6, 2016

zettam commented on Mar 7, 2016

vitaly-t commented on Mar 7, 2016

langpavel commented on Mar 10, 2016

rpedela commented on Mar 22, 2016

awenger commented on Jun 17, 2016

joskuijpers commented on Jun 21, 2016

mrxgodfather commented on Apr 20, 2017

charmander commented on Apr 20, 2017

barbu110 commented on Apr 21, 2018

bloodorca commented on Oct 4, 2018

ZiaDev commented on May 9, 2019

nhevia commented on Nov 12, 2020

mymirai-nikki commented on Nov 27, 2025

charmander commented on Nov 27, 2025

mymirai-nikki commented on Nov 29, 2025

charmander commented on Nov 29, 2025

Metadata

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Issue actions