So, I can email address, password and date using this code in node
client.query("INSERT INTO manlalaro (emailaddr,pwd,premiumexpiry) values ('[email protected]','123absurdcodes', DATE '2009-09-19') ",(err,res)=>{
console.log(err, res)
client.end()
})
But how do I enter JSON data type successfully without getting errors? I have playersaved which is a data type JSON.
The best way to pass the data to be inserted in a separated parameter where the library or the driver do the right treatment to each data type.
In most cases it will be something like this:
client.query("INSERT INTO x (a, b, c) VALUES (?, ?, ?)", [1, "text", { "json": "data" }]);
Or this:
client.query("INSERT INTO x (a, b, c) VALUES ($1, $2, $3)", [1, "text", { "json": "data" }]);
The way to know the right thing to do is read the documentation of the library.
If you are using pg (node-postgres) https://node-postgres.com/
Note: As @Aedric pointed out, in some cases your object must be previously "stringified" (JSON.stringify()). But node-postgres claims it do this automatically. (https://node-postgres.com/features/types#uuid%20+%20json%20/%20jsonb).
client.query("INSERT INTO manlalaro (userid,emailaddr,pwd,savegame) values ($1,$2,$3,$4)" , [13,'[email protected]','290cgyr789','{"ign":"leroy jenkins","level":7,"poisoned status":false}' ] ,(err,res)=>{ console.log(err, res) client.end() }) Thanks again~!
You can insert JSON data in postgresql by converting it into string using JSON.stringify(Object)
`insert into tableName (id,json_data) values(1,'{"test":1}')`
var equipment = JSON.stringify( {"items":1,"level":7,"description":"Small ax used for harvesting wood."}) client.query("INSERT INTO manlalaro (emailaddr,pwd,premiumexpiry,playerSaved) values ('[email protected]','123absurdcodes', DATE '2009-09-19', equipment) ", (err,res)=>{ console.log(err, res) client.end() }) It didnt work.
client.query("INSERT INTO manlalaro (userid,emailaddr,pwd,savegame) values ($1,$2,$3,$4)" , [13,'[email protected]','290cgyr789','{"ign":"leroy jenkins","level":7,"poisoned status":false}' ] no error on this one but I haven't tried using nest array due to SyntaxError: Unexpected token ':'
"in the JS string literal. Use\", or concatenate withJSON.stringify. And you will need to put the json text in a postgres literal value notation, i.e.'{…}'- notice the apostrophes (just like around an ordinary postgres string). But better follow JDuwe's advice and let node-pg do the escaping :-)