Finally i move forward from postgresql 9.1 to postresql 9.3 that supports JSON data type. Then the same code function properly.
However i think that what i want to do in the first place can be done... if someone know how i still want to know.
Enviroment
node v0.10.28
pg v3.3.0
postgresql 9.1
I got this insert query
INSERT INTO sessions(sid, user_id, session_object) VALUES ('id1', 1, '{"id":"fX2HkXYLclB","data": testing"}') RETURNING session_id
When testing it from pgAdmin (or command line) it works fine, but when my app try to run it from pg.client.query
it try to turn the object as a string saving "[object object]". Here is the node code:
var session = {"id":"fX2HkXYLclB","data": "testing"};
var sql = 'INSERT INTO sessions(sid, user_id, session_object) VALUES (\'id1\', ' +
' 1, \''+JSON.stringify(session)+'\' ) RETURNING session_id';
console.log(sql);
client.query(sql, function(err, info) {
if(err) {
return addSessionCB(err, null);
}
return addSessionCB(null, info.rows[0].session_id);
});
After runing the application and testing the query manually on pg_admin the table show this 2 results.
session_id | sid | user_id | session_object | active
------------+-------------+---------+---------------------------------------+---------
1 | fX2HkXYLclB | 1 | [object Object] | t
2 | fX2HkXYLclB | 1 | {"id":"fX2HkXYLclB","data": "testing"}| t
(2 filas)
So, the question is ¿what im doing wrong with the pg.query
?
Adding the original code, the code before is simplified for readability.
function addSession(session, addSessionCB) {
log.log('debug', '[PSQL]Add new session on DB. \nsession: '+ session.id +
'\nuser:'+ session.data.user);
function executeAddSessionQuery(user_id){
var sql = 'INSERT INTO sessions(sid, user_id, session_object) VALUES (\'' +
session.id + '\', '+user_id+', \''+JSON.stringify(session)+'\' ) ' +
'RETURNING session_id';
log.log('debug', '[PSQL]Adding session: '+session.id+' for user_id: '+
user_id+'\nSQL: '+sql);
client.query(sql, function(err, info) {
if(err) {
log.log('debug', '[PSQL]Error adding new session. \n'+err);
return addSessionCB(err, null);
}
return addSessionCB(null, info.rows[0].session_id);
});
};
//to save session we need to know user id
getUserByName({name: session.data.user},
function getUserByNameCB(err, user_result){
if(err || !user_result){
//if error or not result we try to save new user
log.log('debug', '[PSQL]Associated user not found. Creating a new ' +
'user entry. ');
addUser({name: session.data.user},
function addUserCB(err, newUserID){
if(err){
log.log('warn', '[PSQL]Session user didn\'t exists and cannot be ' +
'added to DB');
return addSessionCB(err, null);
}
executeAddSessionQuery(newUserID);
});
} else {
//if the user exist we use his id.
executeAddSessionQuery(user_result.user_id);
}
});
}
obj.toString()
problem. You're not usingtoString()
anywhere on an object are you? I don't see it here, but otherwise your code should work. – glortho Jun 30 '14 at 15:26JSON.stringify(session)
give me a string so i dont needsession.toString()
fot nothing. – darofar Jun 30 '14 at 15:34