Get count of records affected by INSERT or UPDATE in PostgreSQL

user_303432 0
Viewed 94130

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;

sql-update postgresql sql
5 Answers

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;
user_51369550

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.

user_15499180
Related