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: '[email protected]',
},
{
firstname : 'hello',
lastname : 'world',
email: '[email protected]',
},
{
firstname : 'mami',
lastname : 'water',
email: '[email protected]',
}
]
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.
As per the ColumnConfig syntax:
const col = {
name: 'added_at',
def: () => new Date() // default to the current Date/Time
};
const 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:
const col = {
name: 'added_at',
mod: ':raw', // 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:
const col = {
name: 'added_at',
mod: ':raw', // use raw-text modifier, to inject the string directly
init: () => {
return 'now()';
}
};
See the ColumnConfig syntax for details.
P.S. I'm the author of pg-promise.