4

This query works for me when I tried in PG admin query tool:

UPDATE bankdetails SET bank_details = 'mysore' ,"PAN"= 123,bank_acc = 456,
                       "UAN"=789,tax = 'myy'
WHERE  user_id = 79;
INSERT INTO bankdetails (user_id,bank_details,"PAN",bank_acc,"UAN",tax)
       SELECT 79,'mysore',123,456,789,'myy'
       WHERE NOT EXISTS (SELECT 1 FROM bankdetails WHERE user_id=79);

Later I implemented in my code like this:

db.query('UPDATE bankdetails SET bank_details = $2 ,"PAN"= $3,bank_acc = $4,"UAN"=$5,tax = $6 WHERE user_id = $1; INSERT INTO bankdetails (user_id,bank_details,"PAN",bank_acc,"UAN",tax) SELECT $1,$2,$3,$4,$5,$6 WHERE NOT EXISTS (SELECT 1 FROM bankdetails WHERE user_id = $1);', [companyuser.rows[0]._id, data.bankDetails.bank_details, data.bankDetails.PAN, data.bankDetails.bank_acc, data.bankDetails.UAN, data.bankDetails.tax],function(err,bank) {

                if (err) return callback(new Error('error'));
})

it says:

cannot insert multiple commands into a prepared statement

Can some one guide me here?

  • The error message means that you cannot execute 2 distinct SQL statements at once. You need to split them up into 2 separate db.query() calls in your code – Philᵀᴹ May 24 '17 at 12:27
  • Why don't you use insert ... on conflict update ...? It not only is a single statement it is also safe against concurrent transactions and race conditions. – a_horse_with_no_name May 24 '17 at 12:30
  • @a_horse_with_no_name it requires unique constraints here user_id is not unique. – Schüler May 24 '17 at 12:32
  • @Philᵀᴹ 2 separate db.query() how? can you please brief me? but it should not contains if statements. – Schüler May 24 '17 at 12:35
  • But the way you use this, indicates that you should have a unique constraint on that column. – a_horse_with_no_name May 24 '17 at 12:43
8

You can do that in a single query using a writeable CTE:

with updated as (
   UPDATE bankdetails 
       SET bank_details = 'mysore',
           "PAN"= 123,
            bank_acc = 456,
            "UAN" = 789,
            tax = 'myy'  
  WHERE bankdetails.user_id = 79
  returning *
)
INSERT INTO bankdetails (user_id,bank_details,"PAN",bank_acc,"UAN",tax)
SELECT 79,'mysore',123,456,789,'myy'
WHERE NOT EXISTS (SELECT * FROM updated);

The insert will only be executed if the update did not change a row.


You can even avoid writing the values twice:

with data (user_id, bank_details, pan, bank_acc, uan, tax) as (
  values (79, 'mysore', 123, 456, 789, 'myy')
), updated as (
   UPDATE bankdetails 
       SET bank_details = d.bank_details,
           "PAN"= d.pan,
            bank_acc = d.bank_acc,
            "UAN" = d.uan,
            tax = d.tax  
  FROM data d 
  WHERE user_id = d.user_id
  returning *
)
INSERT INTO bankdetails (user_id,bank_details,"PAN",bank_acc,"UAN",tax)
SELECT user_id, bank_details, pan, bank_acc, uan, tax
FROM data
WHERE NOT EXISTS (SELECT * FROM updated);

I don't know which programming language you are using, but for e.g. a JDBC PreparedStatement you can replace the data in the values(..) part with parameter placeholder:

with data (user_id, bank_details, pan, bank_acc, uan, tax) as (
  values (?, ?, ?, ?, ?, ?)
)
...
  • am using nodejs – Schüler May 24 '17 at 13:04
  • Reading between the lines here, I suspect that @Schüler expects combining queries into a single statement to make them take effect atomically, avoiding race conditions. That's definitely not the case. Your query will produce wrong results and/or raise an ERROR if multiple copies run at the same time. See stackoverflow.com/q/17267417/398670 – Craig Ringer May 24 '17 at 13:13
  • @CraigRinger: so will the two statements, right? – a_horse_with_no_name May 24 '17 at 13:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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