update helper with mixed "simple" object and columns

[mikicho]

One of our PATCH endpoints has the following body:

{ name: 'Vitali', contacts: [{..], {..}] }

When trying to use the update helper like this:

db.one(pgp.helpers.update(request.body, undefined, 'users') + ' where...'));

We get:

error: column "contacts" is of type json but expression is of type text[]

Because contacts field is from json type in PG.

This require us to use ColumnSet like this:

const columns = new pgp.helpers.ColumnSet([
  ...Object.keys(omit(request.body, 'contacts')),
  new pgp.helpers.Column({ name: 'contacts', mod: ':json' })
])
db.one(pgp.helpers.update(request.body, columns, 'users') + ' where...'));

I want to mix "simple" values with Column:

const data = {
   ...request.body,
  new pgp.helpers.Column({ name: 'contacts', mod: ':json' }),
}
db.one(pgp.helpers.update(data, undefined, 'users') + ' where...'));

Perhaps there is a more effective solution than the one I found?

[vitaly-t]

The correct way to diagnose query-generated queries is to take the final query and execute it in pgAdmin. Then see what SQL change is needed to make the query pass. Publish the results here, then I can advise how to change your code to produce that correct query.

In the meantime, I can only guess your SQL type casting is incorrect.

Also, this code:

new pgp.helpers.Column({ name: 'contacts', mod: ':json' })

can be reduced to just a string - 'contacts:json'

[mikicho]

Thanks!
Sorry if I wasn't clear, I found and fixed the problem. I'm just wondering if pg-promise has more "user-friendly" way to do this casting along with other properties that may be in the body.

[vitaly-t]

You didn't publish the issue you found, or the solution you used. How am I to advise you then?

Not sure what better solution you search, given the lack of detail, but Column supports type casting, as cast property.

[mikicho]

I see.
The question/issue is this: can I simply the following:

const columns = new pgp.helpers.ColumnSet([
  ...Object.keys(omit(request.body, 'contacts')),
  new pgp.helpers.Column({ name: 'contacts', mod: ':json' })
])
db.one(pgp.helpers.update(request.body, columns, 'users') + ' where...'));

Currently, I need to omit the contacts property.
I'm wondering if it's possible to use it with an object instead like:

const columns = new pgp.helpers.ColumnSet({
  ...request.body,
  new pgp.helpers.Column({ name: 'contacts', mod: ':json' })
})
db.one(pgp.helpers.update(request.body, columns, 'users') + ' where...'));

[vitaly-t]

SQL generation in this library is based on all columns you provide, you cannot skip one column, you need 2 separate column-sets for that. Instead, you should use extend method to create the second column-set from the original.