Ayodeji Jayeoba Ayodeji Jayeoba - 9 days ago 8
Node.js Question

Multiple insertion with addition data with pg-promise

I have a large dataset that I want to insert into a postgres db, I can achieve this using pg-promise like this

function batchUpload (req, res, next) {
var data = req.body.data;
var cs = pgp.helpers.ColumnSet(['firstname', 'lastname', 'email'], { table: 'customer' });
var query = pgp.helpers.insert(data, cs);
db.none(query)
.then(data => {
// success;

})
.catch(error => {
// error;
return next(error);
});
}


The dataset is an array of objects like this:

[
{
firstname : 'Lola',
lastname : 'Solo',
email: 'mail@solo.com',
},
{
firstname : 'hello',
lastname : 'world',
email: 'mail@example.com',
},
{
firstname : 'mami',
lastname : 'water',
email: 'mami@example.com',
}
]


The challenge is I have a column
added_at
which isn't included in the dataset and cannot be
null
. How do I add a timestamp for each record insertion to the query.

Answer

As per the ColumnConfig syntax:

var col = {
    name: 'added_at',
    def: new Date() // default to the current Date/Time
};

var cs = pgp.helpers.ColumnSet(['firstname', 'lastname', 'email', col], { table: 'customer' });

Alternatively, you can define it in a number of other ways, as ColumnConfig is very flexible.

Example:

var col = {
    name: 'added_at',
    mod: '^', // use raw-text modifier, to inject the string directly
    def: 'NOW()' // use NOW() for the column
};

or you can use property init to set the value dynamically:

var col = {
    name: 'added_at',
    mod: '^', // use raw-text modifier, to inject the string directly
    init: function(c) {
        return 'NOW()';
    }
};

See the ColumnConfig syntax for details.

P.S. I'm the author of pg-promise.