Skip Null Values On Upsert

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 r

Solution 1:

Use the helpers methods for generating the query:

constskipIfNull = 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 skippedage: 123,
    type: 'tt'
};
    
    const query = pgp.helpers.insert(data, cs) + ' ON CONFLICT(id) DO UPDATE SET ' +
        pgp.helpers.sets(data, cs) + ' RETURNING *';

will generate:

INSERTINTO "table"("id","name","age","type") VALUES(1,null,123,'tt')
ON CONFLICT(id) DO UPDATESET "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:

• skip update columns with pg-promise