I've seen a common pattern like this used to update a record:

UPDATE posts SET (foo=bar) WHERE id=123 AND owner_id=234

However, what if there is a requirement that a non-existentent ID is differentiated from a owner_id mismatch?

The only easy solution I know of is to fetch the row first, compare owner_id in the client (app server), then proceed with the actual update. Alternatively, one could attempt the update, then if no rows were affected, try selecting the ID to see whether the ID doesn't exist or if the owner_id just didn't match.

Is there a way to communicate an owner_id mismatch so that extra round-trip to the DB can be skipped?


Try this query. I don't know if its gonna work, please let me know. My idea is to select the owner in a A table and the id in a B table. If you get the owner_id and not the id, that means the id is missing. If it returns no data, that means there was no owner.... Maybe you can try different joins like FULL OUTER JOIN and see what happens... Good luck!

select A.owner_id ,
from(select distinct owner_id from  posts where owner_id=234) A left outer join
(UPDATE posts SET (foo=bar) WHERE id=123 AND owner_id=234 RETURNING owner_id, id) B on A.owner_id = B.owner_id

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.