Get count of records affected by INSERT or UPDATE in PostgreSQL

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#PLPGSQL-STATEMENTS-DIAGNOSTICS

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;

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.

I might be replaying to question very late, but it will help someone who will in search for the complete answer like me, in hope

CREATE TABLE admin_layer.test(id integer, user_name varchar);

INSERT INTO admin_layer.test(id,user_name) values(1,'Ganesh'),(2, 'Nick');

-- Using Function
CREATE FUNCTION admin_layer.fn_insert_test(_id INTEGER, _user_name varchar)
RETURNS INTEGER AS 
$BODY$
DECLARE _row_count INTEGER;
BEGIN
EXECUTE format('INSERT INTO admin_layer.test(id,user_name) VALUES(%s,%L)',_id, _user_name);
GET DIAGNOSTICS _row = ROW_COUNT;
RETURN _row_count;
END;
$BODY$ LANGUAGE plpgsql;

-- Calling function
-- SELECT admin_layer.fn_insert_test(123, 'John Ray') AS inserted_rows;

-- using a 'DO' block to execute an anonymous code block.
DO $$
DECLARE
    _inserted_rows INTEGER := 0;
BEGIN
    _inserted_rows := admin_layer.fn_insert_test(123, 'John Doe');
    
    -- Check if any rows were inserted
    IF _inserted_rows > 0 THEN
        RAISE NOTICE 'Rows inserted: %', _inserted_rows;
        -- Additional processing based on the number of rows inserted
    ELSE
        RAISE NOTICE 'No rows inserted';
        -- Handle the case where no rows were inserted
    END IF;
END;
$$;


-- Using Procedure
CREATE PROCEDURE admin_layer.proc_insert_test(_id INTEGER, _user_name VARCHAR, INOUT _row_count INTEGER)
LANGUAGE plpgsql
AS $$
BEGIN
    EXECUTE format('INSERT INTO admin_layer.test(id, user_name) VALUES (%s, %L)', _id, _user_name);
    GET DIAGNOSTICS _row_count = ROW_COUNT;
END;
$$;

--  using a 'DO' block to execute an anonymous code block.
DO $BODY$
DECLARE
    _inserted_rows INTEGER:=0; -- Initialize to 0
BEGIN
    CALL admin_layer.proc_insert_test(123, 'John Sins', _inserted_rows);    
    -- Check if any rows were inserted
    IF inserted_rows > 0 THEN
        RAISE NOTICE 'Rows inserted: %', _inserted_rows;
        -- Additional processing based on the number of rows inserted
    ELSE
        RAISE NOTICE 'No rows inserted';
        -- Handle the case where no rows were inserted
    END IF;
END;
$BODY$

-- To get updated rows count using procedure
CREATE PROCEDURE admin_layer.proc_update_test(_id INTEGER, _new_user_name VARCHAR, INOUT _updated_rows INTEGER)
LANGUAGE plpgsql
AS $$
BEGIN
    EXECUTE format('UPDATE admin_layer.test SET user_name = %L WHERE id = %s;',$2, $1); 
    -- or instead using position we can use the arguments (_new_user_name, _id)
    GET DIAGNOSTICS _updated_rows = ROW_COUNT;
END;
$$;

-- 
DO $$
DECLARE
    _updated_rows INTEGER := 0;
BEGIN
    CALL admin_layer.proc_update_test(123, 'Max', _updated_rows);
    RAISE NOTICE 'Rows updated: %', _updated_rows;
END;
$$;

-- We can achieve this without using function or procedure
DO $$
DECLARE
    _updated_rows INTEGER := 0;
BEGIN
    UPDATE admin_layer.test SET user_name = 'Jony Sins' WHERE id = 123;    
    GET DIAGNOSTICS _updated_rows = ROW_COUNT;
    -- Check if any rows were updated
    IF _updated_rows > 0 THEN
        RAISE NOTICE 'Rows updated: %', _updated_rows;
        -- Additional processing based on the number of rows updated
    ELSE
        RAISE NOTICE 'No rows updated';
        -- Handle the case where no rows were updated
    END IF;
END;
$$;

select * from admin_layer.test

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