Node-postgres: Error: invalid input syntax for type json

[PythonDevOp]

I have the following situation. I am using the pg driver version 4.5.3 (yes I know its old, but can't yet refactor or switch to new version), along with Node 4.x, Express 4, and a postgres 9.5 database. I have a table that looks like this:
code to recreate(https://gist.github.com/PythonDevOp/aced5918de770365258690cecbceee92)

 id          |   x        |   y       |   row    |   col
--------+--------+------+--------+------------
    1        |  5        |     1     |    2      |  1
    2        |  6        |     2     |    3      |  2
    3        |  7        |     3     |    4      |  7
    4        |  8        |     4     |    5      |  9

I am calling a function, taking an array of data, and passing that array as a parameter using commands in the following gist:
https://gist.github.com/PythonDevOp/f7a7f95fd7fa11a06420c54c5be4501a

However, I am getting an error when trying to insert the data via the node postgres driver. When I call the function in pgadmin or via the console, it works as expected. When calling this via the application, something breaks and I can't tell where. I am guessing this is a bug with the postgres driver in how it parses JSONB.

Any ideas?

[joskuijpers]

What is the error?

On Oct 7, 2016, at 8:33 PM, PythonDevOp [email protected] wrote:

I have the following situation. I am using the pg driver, along with Node 4.x, Express 4, and a postgres 9.5 database. I have a table that looks like this:
code to recreate(https://gist.github.com/PythonDevOp/aced5918de770365258690cecbceee92)

id | x | y | row | col
--------+--------+------+--------+------------
1 | 5 | 1 | 2 | 1
2 | 6 | 2 | 3 | 2
3 | 7 | 3 | 4 | 7
4 | 8 | 4 | 5 | 9
I am calling a function, taking an array of data, and passing that array as a parameter using commands in the following gist:
https://gist.github.com/PythonDevOp/f7a7f95fd7fa11a06420c54c5be4501a

However, I am getting an error when trying to insert the data via the node postgres driver. When I call the function in pgadmin or via the console, it works as expected. When calling this via the application, something breaks and I can't tell where. I am guessing this is a bug with the postgres driver in how it parses JSONB. Any ideas?

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub, or mute the thread.

[PythonDevOp]

The error is below (also in the gist):
{ [error: invalid input syntax for type json]
name: 'error',
length: 217,
severity: 'ERROR',
code: '22P02',
detail: 'Expected ":", but found ",".',
hint: undefined,
position: undefined,
internalPosition: undefined,
internalQuery: undefined,
where: 'JSON data, line 1: ..."chart_y\":2,\"chart_row\":0,\"chart_col\":0}",...',
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'src\backend\utils\adt\json.c',
line: '1140',
routine: 'report_parse_error' }

[karladler]

probably related to https://github.com/brianc/node-postgres/issues/1143
I also have the problem. Is there already a solution beside parsing it to jsonString?

[PythonDevOp]

@abimelex What do you mean by "parsing it to jsonString". Is there a specific postgres command that you are using as a workaround?

[joskuijpers]

I found the problem.

When parsing the value to something postgres understands, the input is checked on its type.
Whet it is an object, it is stringified using JSON.stringify (as we want).
However, when the input is an array, it is transformed into Postgres arrays ({'a','b'}).
Ergo, the input becomes {'key',..... Parsed this as JSON gives the error you get: expected : when getting a comma.

To solve this, there would need to be a check on the true input type (when using ::type). If this typecast is not used there is no way to know whether to parse the data as array or as object. Sadly, the content of the query string is not used anywhere in node-postgres currently.

I am open to solutions.

[PythonDevOp]

I don't necessarily have any solutions, but I did encounter other errors when trying to send the JSON in various formats, for example without the single quotes, or not stringified. I got errors such as :Array value must start with "{" or dimension information. or '"[" must introduce explicitly-specified array dimensions.', when passing in as an object instead of an array. Perhaps there can be some logic that takes the string and casts it to an array, such as array_to_json or a common table expression that does the casting?

References:
JSON Functions-https://www.postgresql.org/docs/9.5/static/functions-json.html,
Common Table Expressions-https://www.postgresql.org/docs/9.5/static/queries-with.html

[charmander]

Reopened #442.