Postgres - indication for no rows updated
I'm running a simple update query, something开发者_开发问答 like:
UPDATE some_table SET columnA = 'value' WHERE columnB = 'other value'
There is a way in a Postgres to raise an error or print some message when there are no rows updated?
In the database, this is only possible when doing the update from a stored procedure. In plpgsql:
BEGIN
UPDATE some_table SET columnA = 'value' WHERE columnB = 'other value'
IF NOT FOUND THEN
raise exception 'Nothing updated';
END IF;
END
The value is however not logged, but the command is cancelled. If you just want it logged, use raise warning
instead, and set the postgresql logging to include warnings.
If you use an API to access PostgreSQL, you usually have a function that returns the number of values updated. In Java e.g. there is the Statement class, with the function executeUpdate():
public int executeUpdate(String sql) throws SQLException
Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement.
Parameters: sql - an SQL INSERT, UPDATE or DELETE statement or an SQL statement that returns nothing
Returns: either the row count for INSERT, UPDATE or DELETE statements, or 0 for SQL statements that return nothing
Throws: SQLException - if a database access error occurs or the given SQL statement produces a ResultSet object
精彩评论