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;