Throwing error: invalid input syntax for type json on UUID field #1909
Description
I am running the below query, where all IDs are UUID, value is text and data is a jsonb:
{ text:
UPDATE public.entity_field\n SET template_field_id=$3, armicus_value=$4, armicus_data=$5, last_modified_by=$6\n WHERE id=$1 AND entity_id=$2\n',
values:
[ '520969c9-0439-41c9-acc7-0e04ec394e81',
'a4965682-5adb-4b51-bc40-b30c718dfc4e',
'9b34e8c7-7d83-4c7d-9b62-8ee3c484adf1',
'3245',
{},
'72e1d43b-0e6d-4397-9a04-ec7659c60c1a' ] }
and getting this error, note that the detail of the error includes the first part of the last UUID, not the actual JSON field:
error: invalid input syntax for type json
at Connection.parseE (/app/node_modules/pg/lib/connection.js:602:11)
at Connection.parseMessage (/app/node_modules/pg/lib/connection.js:399:19)
at TLSSocket.<anonymous> (/app/node_modules/pg/lib/connection.js:121:22)
at TLSSocket.emit (events.js:198:13)
at TLSSocket.EventEmitter.emit (domain.js:448:20)
at addChunk (_stream_readable.js:288:12)
at readableAddChunk (_stream_readable.js:269:11)
at TLSSocket.Readable.push (_stream_readable.js:224:10)
at TLSWrap.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
name: 'error',
length: 160,
severity: 'ERROR',
code: '22P02',
detail: 'Token "72e1d43b" is invalid.',
hint: undefined,
position: undefined,
internalPosition: undefined,
internalQuery: undefined,
where: 'JSON data, line 1: 72e1d43b...',
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'json.c',
line: '1248',
routine: 'report_invalid_token'
While it is throwing an error message it is still writing to the database correctly. In case it helps this running on Heroku (database and code), and the query is updating a view which then triggers some functions to write to the underlying tables, and if the query is run manually in DBeaver it executes without issue (though not using parametised queries).