Pg-promise: Possibility to set 'partial' as default globally

[Richie765]

Could an initialization option 'partial' be added so that it would format missing fields as NULL by default? Something like:

var pgp = require('pg-promise')({
  // Initialization Options
  partial: true
});

Perhaps it is already possible somehow but I couldn't find it. Using pgp.as.format(..., { partial: true }) all the time gets a bit boring.

Thanks!

[vitaly-t]

Why would you want to use partial query formatting by default?

You will end up with queries not fully formatted, so they cannot be executed.

The support for partial query formatting was added at the same time as QueryFile, because it was the first instance where partial formatting became important, to be able to pre-format SQL that's being loaded. You normally do not need partial formatting outside of QueryFile.

[Richie765]

Thanks for the fast response. Perhaps I misunderstood the function of it, but what I'm looking for is this. I have an array of items I'd like to insert, but not all fields are set. For instance:

var countries = [
 { name: 'Canada', currency: 'CAD' },
 { name: 'Australia' },  // Currency not yet set
];

Then I iterate each item and insert the record in the db:

// loop countries
db.none('INSERT INTO Country(name, currency) VALUES(${name}, ${currency})', country);

That would succeed for the first row, but fail for the second: Property 'currency' doesn't exist. So I have to put something in like this:

if(! ('currency' in country)) country.currency = undefined;

I've quite a bit of data like that, and it would be nice it it would just insert NULL values where the property doesn't exist.

I suppose it could be done like this, but a default partial = true somewhere would make it easier.

db.none(pgp.as.format('INSERT INTO Country(name, currency) VALUES(${name}, ${currency})', country, { partial: true }));

[vitaly-t]

The right approach in this case is via helpers.insert.

Not only it can generate a proper single-query insert for multiple objects, but also you can configure defaults for any column.

For your example:

// create these once, to be re-used:
var columns = ['name', {
    name: 'currency',
    def: null
}];

var csCountries = new pgp.helpers.ColumnSet(columns, {table: 'countries'});

// and then you can generate your insert query where needed:
var query = pgp.helpers.insert(data, csCountries);
// data = a single object or an array of objects;
// query = query string that you now can execute

[vitaly-t]

See also: Multi-row insert with pg-promise.

[Richie765]

That looks good, I'm going to try it after the weekend! Thank you!

[vitaly-t]

Closing for now. You can re-open it, if there are still issues with it ;)

[noinkling]

How do you let the database determine a default for the column?

When feeding columns to helpers.update at least I can specify skip: ({exists}) => !exists to omit a column that doesn't exist on the object (even if it feels clunky), but helpers.insert ignores that property. def: null and def: undefined both set NULL regardless of any default set on the column in the db itself, and def: 'DEFAULT' just sets that string.

[vitaly-t]

@noinkling this depends on the type of the column data. If it is not a string, and can be injected as a [Raw Text], then you can just do:

{name: 'myColumn', def: 'DEFAULT', mod: ':raw'}

If it can be a string, then you will need to create a more generic solution that uses [Custom Type Formatting]:

var setDefault = col => {
    return col.exists ? col.value : {_rawDBType: true, formatDBType: () => 'DEFAULT'};
}

// which then can be used with the ColumnSet as:

{name: 'myColumn', init: setDefault}

[vitaly-t]

F.Y.I. Important breaking change in v6.5.0.