How to get the number of deleted rows in PostgreSQL?
You can use RETURNING
clause:
DELETE FROM table WHERE condition IS TRUE RETURNING *;
After that you just have to check number of rows returned. You can streamline it with CTE:
WITH deleted AS (DELETE FROM table WHERE condition IS TRUE RETURNING *) SELECT count(*) FROM deleted;
This should return just the number of deleted rows.
This should be simple in Java.
Statement stmt = connection.createStatement();
int rowsAffected = stmt.executeUpdate("delete from your_table");
System.out.println("deleted: " + rowsAffected);
See java.sql.Statement.
GET DIAGNOSTICS is used to display number of modified/deleted records.
Sample code
CREATE OR REPLACE FUNCTION fnName()
RETURNS void AS
$BODY$
declare
count numeric;
begin
count := 0;
LOOP
-- condition here update or delete;
GET DIAGNOSTICS count = ROW_COUNT;
raise notice 'Value: %', count;
end loop;
end;
$BODY$a
in Python using psycopg2, the rowcount attribute can be used. Here is an example to find out how many rows were deleted...
cur = connection.cursor()
try:
cur.execute("DELETE FROM table WHERE col1 = %s", (value,))
connection.commit()
count = cur.rowcount
cur.close()
print("A total of %s rows were deleted." % count)
except:
connection.rollback()
print("An error as occurred, No rows were deleted")
This works in functions. It works with other operations like INSERT as well.
DECLARE _result INTEGER;
...
DELETE FROM mytable WHERE amount = 0; -- or whatever other operation you desire
GET DIAGNOSTICS _result = ROW_COUNT;
IF _result > 0 THEN
RAISE NOTICE 'Removed % rows with amount = 0', _result;
END IF;