Node pg-promise, bind multiple values with type ca

问题:

I'm currently using the pg-promise library to insert multiple values into a database in the format:

const cs = new pgp.helpers.ColumnSet(['booking_id', {name:'timeslot', cast:'timestamp'}], {table: 'booking'});

// data input values:
const values = [];
bookings.forEach(slot => {
   values.push({booking_id: booking_id, timeslot: slot});
});

Where I need timeslot to be a timestamp. However it comes into the API as value like

1515586500.0

Using the above cast property my query gets resolved like so

insert into "booking"("booking_id","timeslot") values(1,'1515586500.0'::timestamp)

however this throws an error of cannot cast type numeric to timestamp without time zone

If I use the to_timestamp function however this works how I need it to e.g

insert into "booking"("booking_id","timeslot") values(1,to_timestamp('1515586500.0'));

Is there any way I can get pg-promise to use to_timestamp rather than the ::timestamp notation?

回答1:

Change the column definition to this one:

{
    name: 'timeslot',
    mod: ':raw',
    init: c => pgp.as.format('to_timestamp($1)', c.value)
}

or

{
    name: 'timeslot',
    mod: ':raw',
    init: c => pgp.as.format('to_timestamp(${value})', c)
}

...as per the Column type documentation.

Or you can use Custom Type Formatting on the type, to self-format automatically.

---

Also, you do not need to remap values to suit the ColumnSet object, you use ColumnSet object to fit the data instead. So if the value for column timeslot is in property slot, you just use prop: 'slot' within your column definition to change where the value is coming from.