Search code examples
node.jspostgresqlpg-promise

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: '[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.


Solution

  • 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.