2

I am using pg-promise to handle my Postgres queries and I am having troubles finding a solution to the below query:

I am trying to create a single method for batch upserting many rows at once, This is my code:

massUpsert: (orgId, entities) => db.tx((t) => {
    const queries = [];
    entities.forEach((entity) => {
      const { id, name, age, type } = entity;
      queries.push(
        t.one(`INSERT INTO public.table (id, name, age, type) 
             VALUES ($1, $2, $3, $4)
              ON CONFLICT (id) DO update
                SET 
                 name = $2,
                 age = $3,
                 type = $4
              RETURNING *`,
        [id, name, age, type]));
    });
    return t.batch(queries);
}),

Now the problem is that in some cases I get null for one or more of the fields and I want the DB to remain with old values instead of replacing it with a null.

Any suggestions?

Ronco
  • 109
  • 1
  • 10
  • Have you thought about using triggers on the database itself? – Sumi Straessle Jul 22 '17 at 11:24
  • You shouldn't use within `ON CONFLICT` things like `name = $2`, it should be `name = excluded.name`, which you can further improve with a function checking against `null`. – vitaly-t Jul 22 '17 at 11:30
  • @vitaly-t I did not fully understand you, what do you mean by using excluded.name? – Ronco Jul 22 '17 at 11:34
  • That's the standard solution, and `excluded` is a reserved name, referring to the conflicting columns. Check PostgreSQL documentation. – vitaly-t Jul 22 '17 at 11:36
  • @vitaly-t, As far as I have to understand excluded does not answer to my need for the columns that are in conflict are not to be updated but the other columns, The only thing that I want to prevent is to ignore updating columns with a null value when updating. – Ronco Jul 22 '17 at 13:40
  • @Ronco `name = COALESCE(excluded.name, name)` will do that. Also see my answer below + the links. – vitaly-t Jul 22 '17 at 16:28
  • @Ronco didn't the answer work for you? – vitaly-t Jul 24 '17 at 12:03

1 Answers1

2

Use the helpers methods for generating the query:

const skipIfNull = name => ({name, skip: c => c.value === null});
    
const cs = new pgp.helpers.ColumnSet([
    '?id',
    skipIfNull('name'),
    skipIfNull('age'),
    skipIfNull('type')
], {table: 'table'});

See types ColumnSet and Column.

Generating the query from sample data:

const data = {
    id: 1,
    name: null, // will be skipped
    age: 123,
    type: 'tt'
};
    
    const query = pgp.helpers.insert(data, cs) + ' ON CONFLICT(id) DO UPDATE SET ' +
        pgp.helpers.sets(data, cs) + ' RETURNING *';

will generate:

INSERT INTO "table"("id","name","age","type") VALUES(1,null,123,'tt')
ON CONFLICT(id) DO UPDATE SET "age"=123,"type"='tt' RETURNING *

UPDATE: Newer syntax via assignColumns is better than the sets approach.

But beware that as per method set API, it will return an empty string, if all your conditional columns turn out to be null, so the resulting query will be invalid. You will want to add a check for that ;)

Also, considering that you are generating a multi-insert, it is possible to generate just one multi-row insert that would offer better performance. For that see Multi-row insert with pg-promise.

See also:

vitaly-t
  • 24,279
  • 15
  • 116
  • 138
  • The Solution worked, thank you for the quick answer. It is good to know that the library has an owner :) – Ronco Jul 24 '17 at 12:09
  • Is there a way to use pgp.helpers.insert(data, cs), To generate a query that skips NULL values on inserting? The logic behind it is to use the default values that are set in the table because the fields consisting with NOT-NULL constraint. – Ronco Jul 25 '17 at 17:34
  • I have tried using the above solutions but it still generates a query with inserting null on missing fields, see the example code here: https://gist.github.com/RoncoCo/70c07ff57375c83f77b18fbe6f0dab1e – Ronco Jul 26 '17 at 17:31
  • @Ronco your definition of the optional column there doesn't follow the logic of what you are trying to do. See my answer there. – vitaly-t Jul 26 '17 at 18:36
  • Is there a way to make the unique constraint on a combination of columns? i.e - const cs = new pgp.helpers.ColumnSet([ '?(id, external_id)', skipIfNull('name'), skipIfNull('age'), skipIfNull('type') ], {table: 'table'}); So, in that case, the unique will be on id AND external_id – Ronco Sep 23 '17 at 16:06
  • @Ronco Unique constraints are not part of INSERT or UPDATE queries. So I don't know what you are talking about. And if you want to add ON CONFLICT, just append one to the query. – vitaly-t Sep 23 '17 at 16:43