Get count of records affected by INSERT or UPDATE in PostgreSQL

My database driver for PostgreSQL 8/9 does not return a count of records affected when executing INSERT or UPDATE.

PostgreSQL offers the non-standard syntax "RETURNING" which seems like a good workaround. But what might be the syntax? The example returns the ID of a record, but I need a count.

> INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets') RETURNING did;

I know this question is oooolllllld and my solution is arguably overly complex, but that's my favorite kind of solution!

Anyway, I had to do the same thing and got it working like this:

-- Get count from INSERT
WITH rows AS (
    INSERT INTO distributors
        (did, dname)
    VALUES
        (DEFAULT, 'XYZ Widgets'),
        (DEFAULT, 'ABC Widgets')
    RETURNING 1
)
SELECT count(*) FROM rows;

-- Get count from UPDATE
WITH rows AS (
    UPDATE distributors
    SET dname = 'JKL Widgets'
    WHERE did <= 10
    RETURNING 1
)
SELECT count(*) FROM rows;

One of these days I really have to get around to writing a love sonnet to PostgreSQL's WITH clause ...

I agree w/ Milen, your driver should do this for you. What driver are you using and for what language? But if you are using plpgsql, you can use GET DIAGNOSTICS my_var = ROW_COUNT;

http://www.postgresql.org/docs/current/static/plpgsql-statements.html

You can take ROW_COUNT after update or insert with this code:

insert into distributors (did, dname) values (DEFAULT, 'XYZ Widgets');
get diagnostics v_cnt = row_count;

It's not clear from your question how you're calling the statement. Assuming you're using something like JDBC you may be calling it as a query rather than an update. From JDBC's executeQuery:

> Executes the given SQL statement, which returns a single ResultSet > object.

This is therefore appropriate when you execute a statement that returns some query results, such as SELECT or INSERT ... RETURNING. If you are making an update to the database and then want to know how many tuples were affected, you need to use executeUpdate which returns:

> either (1) the row count for SQL Data Manipulation Language (DML) > statements or (2) 0 for SQL statements that return nothing

You could wrap your query in a transaction and it should show you the count before you ROLLBACK or COMMIT. Example:

BEGIN TRANSACTION;

INSERT .... ;

ROLLBACK TRANSACTION;

If you run the first 2 lines of the above, it should give you the count. You can then ROLLBACK (undo) the insert if you find that the number of affected lines isn't what you expected. If you're satisfied that the INSERT is correct, then you can run the same thing, but replace line 3 with COMMIT TRANSACTION;.

Important note: After you run any BEGIN TRANSACTION; you must either ROLLBACK; or COMMIT; the transaction, otherwise the transaction will create a lock that can slow down or even cripple an entire system, if you're running on a production environment.