How to get the number of deleted rows in PostgreSQL?

Question

I am looking for a way to return the number of rows affected by a DELETE clause in PostgreSQL. The documentation states that;

On successful completion, a DELETE command returns a command tag of the form

DELETE count

The count is the number of rows deleted. If count is 0, no rows matched the condition (this is not considered an error).

If the DELETE command contains a RETURNING clause, the result will be similar to that of a SELECT statement containing the columns and values defined in the RETURNING list, computed over the row(s) deleted by the command.

But I am having trouble finding a good example of it. Can anyone help me with this, how can I find out how many rows were deleted?

EDIT: I wanted to present an alternative that I have found later. It can be found in here, explained under 38.5.5. Obtaining the Result Status title.

Preference for scripting language? Perl, PHP, Python, C, Klingon? — Paul, Feb 12 '10 at 12:11

score 159 · Accepted Answer · edited Aug 06 '15 at 09:10

159

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.

edited Aug 06 '15 at 09:10

Zakaria

851
1
7
14

answered Mar 20 '14 at 23:04

Jakub Fedyczak

2,174
1
13
15

9

This works without an extension! Definitively should be the accepted answer :D – Alfabravo Dec 05 '17 at 21:50
4

Is this less efficient somehow than just `DELETE FROM table WHERE condition IS TRUE`? For example, does Postgres temporarily store the deleted rows somewhere when evaluating the CTE or is it smart enough to see that only the count is used and just count the rows as they are deleted? – EM0 Dec 03 '21 at 11:26

score 17 · Answer 2 · answered Feb 08 '17 at 02:37

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

score 15 · Answer 3 · answered Feb 13 '10 at 01:21

15

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.

answered Feb 13 '10 at 01:21

cope360

6,195
2
20
33

8

@Nate, in the second comment on the question the OP states, "The language is Java." – cope360 May 14 '13 at 02:37

score 11 · Answer 4 · answered Mar 20 '14 at 21:10

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")

score 3 · Answer 5 · answered Apr 14 '21 at 14:09

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;

score -6 · Answer 6 · answered Feb 12 '10 at 12:23

-6

You need the PQcmdTuples function from libpq. Which in PHP for example is wrapped as pg_affected_rows.

answered Feb 12 '10 at 12:23

Milen A. Radev

60,241
22
105
110

2

Strictly speaking, you do not need that extension. A vanilla solution is offered below. – Craig Labenz Jan 25 '19 at 04:05

How to get the number of deleted rows in PostgreSQL?

6 Answers6