How do I update json data in postgres sql in nodejs?

Question

my data structure schema is like

users(
  id serial primary key,
  data json
);

I want to update data key.

I'm using node-postgres library and So far I have tried something like this:

pg.connect(process.env.DATABASE_URL, function(err, client, done) {
    var queryString = "UPDATE users SET data =" + myNewJsonData + " WHERE  id = " + userIdToEdit + ";";
    client.query(queryString, function(err, result) {
        done();
        if (err) {
            res.send("Failed to update user data ");
            throw err;
        } else {
            res.send("Successfully updated user data!! ");
        }
    });
});

this is not working, I'm getting error invalid input syntax for type json

Can anyone help, Thanks.

You are asking the wrong question. The right one should be: Here's the complete UPDATE, what is wrong with the syntax around the JSON column? And you don't even include what myNewJsonData is. — vitaly-t
– vitaly-t, Commented Apr 24, 2017 at 9:27
But your question is about the syntax issue, not the approach. Strictly speaking, the approach will work, if you escape everything correctly. But that's a separate question altogether. — vitaly-t
– vitaly-t, Commented Apr 24, 2017 at 9:31
ah man you are right, it was syntax issue, got it working finally. Thanks — Abhishek
– Abhishek, Commented Apr 24, 2017 at 9:34

Abhishek · Accepted Answer · 2017-04-25 11:31:55Z

7

So it was a syntax error, All I had to do stringfy json data and put that in 'quotes'

pg.connect(process.env.DATABASE_URL, function(err, client, done) {
var newJsonDataStringyfied = JSON.stringify(myNewJsonData)
var queryString = "UPDATE users SET data = '" + newJsonDataStringyfied + "' WHERE  id = " + userIdToEdit + ";";
    client.query(queryString, function(err, result) {
        done();
        if (err) {
            res.send("Failed to update user data ");
            throw err;
        } else {
            res.send("Successfully updated user data!! ");
        }
    });
});

answered Apr 25, 2017 at 11:31

Abhishek

1,7242 gold badges16 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How do I update json data in postgres sql in nodejs?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related