7

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?

6
  • 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, 2017 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.
    – user1822
    May 24, 2017 at 12:30
  • @a_horse_with_no_name it requires unique constraints here user_id is not unique.
    – Schüler
    May 24, 2017 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, 2017 at 12:35
  • But the way you use this, indicates that you should have a unique constraint on that column.
    – user1822
    May 24, 2017 at 12:43

1 Answer 1

12

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 (?, ?, ?, ?, ?, ?)
)
...
3
  • am using nodejs
    – Schüler
    May 24, 2017 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 May 24, 2017 at 13:13
  • @CraigRinger: so will the two statements, right?
    – user1822
    May 24, 2017 at 13:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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