pg-promise ColumnSet use Postgres functions with def property

Question

I am using a ColumnSet and the helper.insert function for a multi row insert.

I have a table column where I want to use the Postgres Date/Time now() function.

const cs = new helpers.ColumnSet([
    'lastname',
    {
        name: 'rental_date',
        def: 'now()'
    }
], { table: { table: 'book_rental', schema: 'public' } })

let rentals = [
    {
        lastname: 'Mueller'
    },
    {
        lastname: 'Johnson'
    }
]

let insert = helpers.insert(rentals, cs)

db.result(insert)
    .then(data => res.json({ message: 'Ok!' }))
    .catch(err => res.json({ message: 'Not ok!' }))

It seems to be working by using def: 'now()', but I want to make sure that I am using it the right way.

Edit:

Regarding the answer in the comment. I tried to do the insert manually and it looks like Postgres is converting the 'now()' string into the now() function.

INSERT INTO book_rental (lastname, rental_date) VALUES ('Mueller', 'now()');

To involve your answer, am I right that this should be the correct code then?

const cs = new helpers.ColumnSet([
    'lastname',
    {
        name: 'rental_date',
        mod: ':raw',
        def: 'now()'
    }
], { table: { table: 'book_rental', schema: 'public' } })

Answer 1

Your code doesn't look right, for the following reasons:

• You want to use now() without any condition, but the def value is only used when the property doesn't exist in the source object (see Column). The init callback is what should be used instead to guarantee the right value override.
• You return now() as an escaped string, while the query needs it as a raw-text string.

First, let's declare a reusable Raw Text string, as per Custom Type Formatting:

const rawText = text => ({toPostgres: () => text, rawType: true});

Then you can define the column like this:

{
    name: 'rental_date',
    init: () => rawText('now()')
}

And make sure you are using the latest version of pg-promise (v7.2.1 as of this writing).

Or alternatively, you can declare it like this:

{
    name: 'rental_date',
    mod: ':raw', // same as mode: '^'
    init: () => 'now()'
}

This syntax however will work in all versions of the library, and perhaps is even simpler to use ;)