How can I see the numbers of rows a query affects when deleting

Question

I want to see how many rows my delete query effects so I know its correct.

Is this possible using pgadmin?

Answer 1

I'm not sure how to automatically do this but you can always do a select then a delete.

SELECT COUNT(*) FROM foo WHERE delete_me=true;

DELETE FROM foo WHERE delete_me=true;

Answer 2

As Andrew said, when doing interactive administration, you can just replace DELETE by SELECT COUNT(*).

If you want to this information in a program of yours (after executing the DELETE), many programming languages provide a construct for this. For example, in PHP it's pg_affected_rows and in .NET it's the return value of ExecuteNonQuery.

Answer 3

Start a transaction, delete and then rollback;

In psql :

test1=> begin;
BEGIN
test1=> delete from test1 where test1_id = 1;
DELETE 2
test1=> rollback;
ROLLBACK

In pgAdmin (in the "History" tab on the "Output pane"):

-- Executing query:
begin;
Query returned successfully with no result in 16 ms.

-- Executing query:
delete from test1 where test1_id = 1;
Query returned successfully: 2 rows affected, 16 ms execution time.

-- Executing query:
rollback;
Query returned successfully with no result in 16 ms.

Answer 4

Use RETURNING and fetch the result like you would fetch a SELECT-result:

DELETE FROM test1 WHERE test1_id = 1 RETURNING id;

This works as of version 8.2