Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

I'm trying to do an insert or update in a postgres database using node.js with 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 queries work the problem is always running both instead of only running the insert function if the update fails.

The debug functions in 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={...}}

Insert

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 node-postgres developer:

It's possible to retrieve number of rows affected by an insert and update. It's not fully implemented in the native bindings, but does work in the pure javascript version. I'll work on this within the next week or two. In the mean time use pure javascript version and have a look here:

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

** END EDIT **

Can anyone help?

share|improve this question

2 Answers

up vote 1 down vote accepted

The immediate answer to your question is to use a stored procedure to do an 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're using some kind of model object that has all the values you need, even if they aren't changing. You have to pass them all into the upsert. If you're stuck doing it the way you've shown here, you should probably check the actual error object after the update to determine if it failed because the row is already there, or for some other reason (which is real db error that needs to be handled).

Then you've gotta deal with the potential race condition between the time your update failed and the time your insert goes through. If some other function tries to insert with the same id, you've got a problem. Transactions are good for that. That's all I got right now. Hope it helps.

share|improve this answer
 
Hi Marco!! Thanks for the answer! I got in touch with the developer of pg module for node.js and I eddited the question to feature his response! Regarding your answer, this was my initial intention but as I'm developing using heroku and need taps to migrate the database I have a problem as I think taps can't export stored procedures. (I can be wrong though) anyway can you enlighten me about the performance issues on stored procedures? About the concurrency issues I don't think they applybut if you could post some link to ilustrate Transictions it would be perfect... ;) Thanks once again!! –  jribeiro Nov 3 '11 at 15:23
up vote 2 down vote

I had this issue when connecting to a PG instance using the JDBC. The solution I ended up using 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 doesn't exist and the insert does nothing if the record does exist. It works pretty well and is an SQL based solution vs a stored procedure.

Here's the initial question: Insert, on duplicate update (postgresql)

share|improve this answer
 
I actually implemented something along those lines too. But I test the response of the first query and only run the second one if no row was afected. This is/was only supported in the native js mode of the github.com/brianc/node-postgres which is what i'm using. Thanks for taking the time to answer!! –  jribeiro Apr 28 '12 at 19:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.