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, and anything else isn't worth getting into. — vitaly-t
– vitaly-t, Commented Jun 22, 2018 at 15:16

vitaly-t · Accepted Answer · 2018-06-25 12:48:41Z

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.

edited Jun 25, 2018 at 12:48

answered Jun 22, 2018 at 17:42

vitaly-t

26.1k17 gold badges129 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Tarek Deeb Over a year ago

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 Over a year ago

Something along the lines of INSERT INTO table_name (one, two) VALUES (myPredefinedFunction($1, $2, $3), $4)

vitaly-t Over a year ago

@TarekDeeb I have added an example for that.

Tarek Deeb Over a year ago

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 Over a year ago

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!

Collectives™ on Stack Overflow

Inserting records in PostgreSQL with 2 varying parameters from an array

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related