pg nodejs package results in 'invalid input syntax for type json'
I had the same problem. Try converting your JS object to string using JSON.stringify()
before passing it into the query as pg won't always do that for you automatically.
See this issue on GitHub for more info.
Related videos on Youtube
iLuvLogix
I just love coding and tackling complex problems. Currently I'm working at a large company in the automation-industry as full-stack developer responsible for a small production team. I'm involved in project planning & designing, the development and production phase as well as the implementation of upcoming features and add-ons according to the industries/customers needs and wishes. Author of logification published on npm: https://www.npmjs.com/package/logification GitHub contributions: logification - Simple logger using console.log(), console.group(), some funky ASCII, coloring and custom delimiters - supports log-levels acc. to RFC5424.
Updated on May 25, 2022Comments
-
iLuvLogix almost 2 years
I have following setup for my project, using the pg node-postgres package:
The simple table 'tmp' looks like this:
According to jsonORG and the postgres docs the object:
{"foo" : true}
is syntactically valid JSON, and when using the pgAdmin Query-tool with:
UPDATE tmp SET data = '{"foo": false}' WHERE id = '1'
works fine, but when i try updating my table through my express route using pg:
router.put('/updateTMP', (req, res) => { // I use dummies in this case instead of req.body.someKey for testing purposes let dummyJSON = {"foo":true}; let dummyID = 1; pg.query(`UPDATE tmp SET data = '${dummyJSON}' WHERE id = '${dummyID}'`, (errUpdate, responseUpdate) => { if (!errUpdate) { // NO ERROR res.json({success: true, message: responseUpdate}); } else { // ERROR console.log(dummyJSON); console.log(errUpdate); res.json({success: false, message: errUpdate}); } }) })
I get the following error from the database:
error: invalid input syntax for type json
I've tried the to_json function from postgresql and the to-json package from npm in the express route - all with the same negative result.
Am i missing some fundamental understanding or is it some formating/quoting-issue?
Thanks in advance for your ideas! ;)
-
iLuvLogix almost 6 yearseven when i try to pass {"":""} to postgresql's to_json() function, I get following error: 'could not determine polymorphic type because input has type "unknown"'..
-
-
Muneer Khan over 2 yearsvery accurate answer bro thanks!