Count number of updates

问题:

I have some postgresql tables, and I want to know if there is any possibility to count the number of updated rows?

To be more clear is being able to count the number of updates that may occur on a table.

回答1:

For example by using RETURNING:

DROP TABLE IF EXISTS t;
CREATE TABLE t(i INT);

INSERT INTO t(i) VALUES (1),(2),(3);

WITH cte AS
(
    UPDATE t
    SET i = i * 10
    WHERE i > 1
    RETURNING *   
)
SELECT COUNT(*) AS num_of_rows_updated
FROM cte;

Rextester Demo

回答2:

These extension might helpful: https://www.postgresql.org/docs/9.6/static/pgstatstatements.html Then a query like: select count() from pg_stat_statements where query ~ 'update.*your_table'; might provide what you are looking for.