Sort in Postgres using node.js

Question

I am trying to do an insert or update in a postgres database using node.js with the pg extension (version 0.5.4).

So far I have this code: (...)

client.query({ text: "update users set is_active = 0, ip = $1 where id=$2", values: [ip,id] }, function(u_err, u_result){ debug(socket_id,"update query result: ",u_result); debug(socket_id,"update query error: ",u_err); date_now = new Date(); var month = date_now.getMonth() + 1; if(!u_err){ client.query({ text: 'insert into users (id,first_name,last_name,is_active,ip,date_joined) values' + '($1,$2,$3,$4,$5,$6)', values: [ result.id, result.first_name, result.last_name, 1, ip, date_now.getFullYear() + "-" + month + "-" + date_now.getDate() + " " + date_now.getHours() + ":" + date_now.getMinutes() + ":" + date_now.getSeconds() ] }, function(i_err, i_result){ debug(socket_id,"insert query result: ",i_result); debug(socket_id,"insert query error: ",i_err); }); } }); 

The problem is that although both requests work, the problem always runs as instead of running the insert function if the update does not work.

The debugging functions in the code output something like:

UPDATE

 Object { type="update query result: ", debug_value={...}} home (linha 56) Object { type="update query error: ", debug_value=null} home (linha 56) Object { type="insert query result: "} home (linha 56) Object { type="insert query error: ", debug_value={...}} 

Embed

 Object { type="update query result: ", debug_value={...}} home (linha 56) Object { type="update query error: ", debug_value=null} home (linha 56) Object { type="insert query result: ", debug_value={...}} home (linha 56) Object { type="insert query error: ", debug_value=null} 

** EDIT **

ANSWER FROM the developer node-postgres:

You can get the number of rows affected by the insert and Refresh. It is not fully implemented in its own bindings, but does work in a clean version of javascript. I will be working on this as part of next week or two. In the meantime, use a clean version of javascript and look here:

https://github.com/brianc/node-postgres/blob/master/test/integration/client/result-metadata-tests.js

** END EDIT **

Can anyone help?

Answer 1

The immediate answer to your question is to use a stored procedure to execute upsert.

http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE

Something like this works fine with the pg module.

 client.query({ text: "SELECT upsert($1, $2, $3, $4, $5, $6)" values: [ obj.id, obj.first_name, obj.last_name, 1, ip, date_now.getFullYear() + "-" + month + "-" + date_now.getDate() + " " + date_now.getHours() + ":" + date_now.getMinutes() + ":" + date_now.getSeconds() ] }, function(u_err, u_result){ if(err) // this is a real error, handle it // otherwise your data is updated or inserted properly }); 

Of course, this assumes that you are using some kind of model object that has all the necessary values, even if they do not change. You must pass them all into the future. If you get stuck doing it the way you showed here, you should probably check the actual error object after the update to determine if it failed because the line already exists or for some other reason (which is a real db error to be processed).

Then you have to deal with the potential state of the race between the moment when your update failed and the time at which your insertion passes. If any other function tries to insert with the same identifier, you have a problem. For this, transactions are beneficial. That’s all I got right now. Hope this helps.

Answer 2

I had this problem when connecting to a PG instance using JDBC. The solution I ended up with was:

 UPDATE table SET field='C', field2='Z' WHERE id=3; INSERT INTO table (id, field, field2) SELECT 3, 'C', 'Z' WHERE NOT EXISTS (SELECT 1 FROM table WHERE id=3); 

The update does nothing if the record does not exist, and the insert does nothing if the record exists. It works very well and is an SQL based solution and stored procedure.

Here's the initial question: Insert, duplicate an update in PostgreSQL?