Inserting records in PostgreSQL with 2 varying parameters from an array

Question

I'm trying to bulk insert in postgresql from an array. What I'm currently doing is the following inside of a loop.

INSERT INTO table_name (
  first, second, third, forth
) VALUES (
  $1, $2, $3, $4
)

There are around 8 to 9 other fields, and only 2 of them change in each iteration. What I would ideally want to do is something along the lines of this psudo-code

FOREACH (obj IN $5)
  INSERT INTO table_name (
    first, second, third, forth
  ) VALUES (
    obj.fieldOne, $2, obj.fieldThree, $4
  ) 

and only supply $2 and $4 (the non-changing properties), and $5 which is an array of changing properties like so

[{one: 123, two: 63}]

---

I know that PostgreSQL allows inserting multiple values in one INSERT statement, but to construct the query and manage its parameters can be a hassle and honestly I'm running away from it just because of parameter and query string hell.

INSERT INTO tabel_name (
  first, second, third, forth
) VALUES (
  $1, $2, $3, $4
) VALUES (
  $5, $2, $6, $4
) VALUES (
  $7, $2, $8, $4
)

I also read that PostgreSQL has loops like FOR and FOREACH, but I have no idea how to work with them in this scenario. I'm using NodeJS with pg-promise if that is relevant. Any help is appreciated!

Answer 1

Using the same approach as for the multi-row inserts, you have two options:

• Add the other fixed 7 properties to each object in the array;

OR

• Use pre-defined values for the columns that are set only in the beginning

Solution for the first option is obvious, you simply iterate through the array and set the same values for the 7 properties.

For the second option, you can use property init of Column to redirect the value to a static object:

/* You can set this once, since you do not need to change it; */
const staticValues = {
  one: 111, three: 333, five: 555, six: 666, seven: 777, eight: 888, nine: 999
};

Then you can define your ColumnSet object like this:

/* helper for defining our static-value column: */
const col = name => ({name, init: ()=> staticValues[name]});

/* Our ColumnSet object: */
const cs = new pgp.helpers.ColumnSet([
    col('one'),
    'two',
    col('three'),
    'four',
    col('five'),
    col('six'),
    col('seven'),
    col('eight'),
    col('nine')],
{table: 'table_name'});

Now when you want to generate a complete insert query, you set all the fixed values into your staticValues object, and then generate the query:

const query = pgp.helpers.insert(data, cs);

This will generate the query string based on your original idea, that you can pass it data with only properties two and four set for each object in it.

Then you can execute the query:

db.none(query).then().catch();

Several of my columns depend on more than one parameter. How would I do that?

You can define such column like any way you want, as type Column is very flexible:

{
    name: 'col-name',
    init: c => pgp.as.format('myFunction(${one}, ${two}, ${three})', c.source),
    mod: ':raw'
}

Or use any other formatting template.