pg nodejs package results in 'invalid input syntax for type json'

Question

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! ;)

ps: And yes - I've read through this, and that article..

Tarratarradiddle

even 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"'.. – Tarratarradiddle

Answer 1

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.

Giltzow

very accurate answer bro thanks! – Berni