Pg-promise: helpers.update not casting objects to JSONB

[benhjames]

Expected behavior

Let's say I have a table (mySchema.myTable) with a column of JSONB type (myColumn).

I'm expecting the following multi-update to work:

const keys = ['id', 'myColumn:json'];
const columnSet = new helpers.ColumnSet(keys, {
  table: { schema: 'mySchema', table: 'myTable' },
});

await db.query(`
  ${helpers.update([
    { id: 1, myColumn: {} },
    { id: 2, myColumn: {} },
  ], columnSet)} where v.id = t.id
`);

Actual behavior

It currently throws an error:

error: column "myColumn" is of type jsonb but expression is of type text

The query that is produced by helpers.update (formatted) is:

update "mySchema"."myTable" as t
set "id"=v."id", "myColumn"=v."myColumn" from
(values(1,'{}'),(2,'{}')) as v("id","myColumn")
where v.id = t.id

If I change "myColumn"=v."myColumn" to "myColumn"=to_jsonb(v."myColumn") and run it in psql, it would then succeed.

Would it be possible to automatically cast '{}' into to_jsonb here? Hopefully I'm missing a trick! Thanks! 🙂

[benhjames]

This is additionally the case for json and custom enum fields, for example:

error: column "myField" is of type "mySchema"."myEnum" but expression is of type text

[benhjames]

Never mind - I just read the https://github.com/vitaly-t/pg-promise#custom-type-formatting docs! I'll give this a go.

[benhjames]

Turns out I just needed to do:

const keys = ['id', new helpers.Column({ name: 'myField', cast: 'jsonb' })];

where cast is whatever the field type is!

[vitaly-t]

I've just noticed this post, since you closed it quickly :smile:

So basically yes, use of cast within the column definition is all you needed in your case, since you are producing an empty object there, which is a special case for object serialization.

As for [Custom Type Formatting], you can do anything with it, but in your specific case it is not needed.

[benhjames]

Excellent - thank you for sanity checking! 🙂

[vitaly-t]

@benhjames b.t.w., you do not have to use type Column explicitly, it can be used implicitly:

const keys = ['id', { name: 'myField', cast: 'jsonb' }];