CODEWITHSUNDEEP
node.jspostgresql
lisibuvowo
lisibuvowo

Reputation: 15

How can I multiple query in postgres

I am using node-postgres (https://node-postgres.com/) pool in my project.

I some case I need to process insert operation in for loop, like this:

data.forEach(items => { 
    pgPool.query('INSERT INTO cache (hash, data) VALUES($1::text, $2::json)', [key, JSON.stringify(items)])
})

I think that it's not a good way how to multiple query insert.

Is there any better way?

For example pgPool.multipleQuery(queryArray) ?

Or is my solution correct?

Thank you for any help.

Upvotes: 0

Views: 376

Answers (1)

Daniel Husak
Daniel Husak

Reputation: 111

I don't know what data looks like, but I imagine it like that:

let data = [
  ["item1", "item2"],
  ["item3", "item4"],
  ["item5", "item6"]
];

And I suggest you use inserting multiple rows in a single query and build the query:

let parameters = data
    .map(
        (items, i) => ["($", (i * 2) + 1, "::text, $", (i * 2) + 2, "::json)"].join("")
    ).join(",");
//($1::text, $2::json),($3::text, $4::json),($5::text, $6::json)

let key = "hash";
let parametersValues = data
    .flatMap(items => [key, JSON.stringify(items)]);
//["hash", "["item1","item2"]", "hash", "["item3","item4"]", "hash", "["item5","item6"]"]

let queryText = "INSERT INTO cache (hash, data) VALUES" + parameters;
//INSERT INTO cache (hash, data) VALUES($1::text, $2::json),($3::text, $4::json),($5::text, $6::json)

pgPool.query(queryText, parametersValues);

jsfiddle

Upvotes: 1

Related Questions