Postgres - Return error on update if record not found

Question

I am wondering if there is a way of getting an error back on update if the row does not exist in the database when I issue an update.

update users set email='[email protected]' where id=200

If users table does not have user with id=200, then postgres simply says "UPDATE 0". However, I am wondering if I can get an error back. That way, I don't have to issue 2 requests to the database, once to check existence, once to update. There could also be a race condition between checking the existence and issuing an update if we do it separately.

Why do I need an error? Simple - so the clients know they have used an invalid user id and they take corrective action.

Answer 1

You can use something like

update users set email='[email protected]' where id=200 returning id; 

This query will return the id of the updated row. If it returns 0 rows - then throw an error in your application.

Also you may want to check if your db driver / framework returns the count of affected rows (like getUpdateCount() in JDBC).

Answer 2

You can use an anonymous code block.

do $$ begin     update users set email='[email protected]' where id=200;     if not found then raise exception 'User not found';     end if; end $$; 

Or regular function:

create or replace function update_user(new_email text, user_id integer) returns void language plpgsql  as $$ begin     update users set email = new_email where id = user_id;     if not found then raise exception 'User id % not found', user_id;     end if; end $$;      select update_user('[email protected]', 200);