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!

`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`. You are pursuing the wrong sentiment. The right solution is [here](https://stackoverflow.com/questions/37300997/multi-row-insert-with-pg-promise), and anything else isn't worth getting into. — vitaly-t, Jun 22 '18 at 15:16

vitaly-t · Accepted Answer · 2018-06-25T12:48:41.323

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.

This is great, but I have an issue. Several of my columns depend on more than one parameter. So let's say ```INSERT INTO table_name (one) VALUES ($1-$2)```. How would I do that? Note: The calculated value depends on a predefined function in postgres — Tarek Deeb, Jun 25 '18 at 07:57
Something along the lines of ```INSERT INTO table_name (one, two) VALUES (myPredefinedFunction($1, $2, $3), $4)``` — Tarek Deeb, Jun 25 '18 at 08:22
Thanks for your reply. I'm trying to add those columns in the static object you suggested. The only way I was able to make it work is add the ```pgp.as.format``` in the ColumnSet array, and supply the static values in the ```data``` array in each object, instead of having the data array containing only the varying values. Is there a cleaner way to access the parameters in the ```format()``` query from the ```staticValues``` object? Apologies for too many stupid questions — Tarek Deeb, Jun 25 '18 at 15:10
Was able to achieve it by replacing ```c.source``` with an object of my static values, and in one case I had to use a variable value with a constant value, so I did ```Object.assign( { staticValue: 'foo' }, c.source)```. Thank you so much for you help! — Tarek Deeb, Jun 26 '18 at 07:09

Inserting records in PostgreSQL with 2 varying parameters from an array

1 Answers1