Get count of records affected by INSERT or UPDATE in PostgreSQL

Question

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;

Answer 1

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 ...

Answer 2

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