Node Postgres insert multiple rows based on array and static variable

Question

I am trying to insert multiple rows into a PostgresSQL server from my Nodejs server based on an array. I have a static variable, user_id, which would be the same for all entries but I want to change the filter_name based off an array. My goal is to not make multiple SQL insert calls.

arrayOfFilters = ["CM", "CBO", "SA", "EPS", "AD"]

await db.query(
            "INSERT INTO filters(filter_name, requests_id)VALUES($1, $2)",
            [arrayOfFiltersParams, user_id]);

I am hoping to have a row in the filters table for each one of the filters found in the arrayOfFilters with a matching user_id key for each entry (aka 5 rows for this example). Thanks so much!

No, you cannot do this. You need to repeat this query for each item in arrayOfFilters. There is slight optimization of constructing multivalued insert statement, but in this case its not worth it. — mvp
– mvp, Commented Apr 6, 2020 at 3:57

Lin Du · Accepted Answer · 2020-04-16 15:08:00Z

Write a helper function expand to build Parameterized query. Here is a solution:

import { pgclient } from '../../db';

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(', ');
}
function flatten(arr) {
  var newArr: any[] = [];
  arr.forEach((v) => v.forEach((p) => newArr.push(p)));
  return newArr;
}

(async function test() {
  try {
    await pgclient.connect();
    // create table
    await pgclient.query(`
      CREATE TABLE IF NOT EXISTS filters (
        id serial PRIMARY KEY,
        filter_name VARCHAR(10),
        requests_id INTEGER
      )
    `);
    // test
    const arrayOfFilters = ['CM', 'CBO', 'SA', 'EPS', 'AD'];
    const user_id = 1;
    const arrayOfFiltersParams: any[] = arrayOfFilters.map((el) => [el, user_id]);
    await pgclient.query(
      `INSERT INTO filters(filter_name, requests_id) VALUES ${expand(arrayOfFilters.length, 2)}`,
      flatten(arrayOfFiltersParams),
    );
  } catch (error) {
    console.log(error);
  } finally {
    pgclient.end();
  }
})();

After executed above code, check the database:

node-sequelize-examples=# select * from "filters";
 id | filter_name | requests_id
----+-------------+-------------
  1 | CM          |           1
  2 | CBO         |           1
  3 | SA          |           1
  4 | EPS         |           1
  5 | AD          |           1
(5 rows)

Collectives™ on Stack Overflow

Node Postgres insert multiple rows based on array and static variable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related