Get count of records affected by INSERT or UPDATE in PostgreSQL

sql

transaction-management

jdbc-management

performance-optimization

byAnton Shumikhin·Feb 8, 2025

To pinpoint the row count impacted by an INSERT or UPDATE operation in PostgreSQL, wield the RETURNING clause followed by count(*) inside a subquery. It's like Batman using his utility belt – efficient and effective!

For INSERT, consider this example:

-- It's like READY... STEADY...
INSERT INTO table_name (column1, column2)
VALUES (value1, value2)
RETURNING null;   -- GO!

All wrapped up in SELECT count(*), like a nice little burrito 🌯:

-- One SQL burrito, coming right up!
SELECT count(*) FROM (
  INSERT INTO table_name (column1, column2)
  VALUES (value1, value2)
  RETURNING *
) as subquery;

This method is equally effective for UPDATE.

In PL/pgSQL scripts make use of GET DIAGNOSTICS:

-- Let's call Sherlock Holmes to find out the row count mystery!
DO $$
DECLARE
  rows_affected int;  -- Watson, jot this down please!
BEGIN
  UPDATE table_name SET column1 = value1 WHERE column2 = 'condition';
  GET DIAGNOSTICS rows_affected = ROW_COUNT;  -- Sherlock nods approvingly
  RAISE NOTICE 'Rows affected: %', rows_affected;  -- Case solved!
END $$;

Practical guide: Counting records efficiently

Transaction management: The fine art

Within a transaction, maintaining appropriate data integrity and performance is essential. It's like being a skilled juggler in a crowded market – you don't want to drop any of your chainsaws, do you? So, if an update or insert operation falters, perform a rollback:

BEGIN;
-- Insert or Update ... It feels like casting a spell!
ROLLBACK; -- Reverso if something goes south!

If all goes as planned, wrap it up with a commit:

COMMIT; -- It's like mic drop moment!

JDBC management: Another fine art

If JDBC is your tool of choice while connecting to PostgreSQL, the record count management is done through executeUpdate:

// Boom! Gimme the modified count.
int affectedRows = statement.executeUpdate();

That variable right there holds the golden number of the impacted rows.

Cozying up with your driver: Compatibility matters

Ensure your PostgreSQL driver and RETURNING syntax are on speaking terms. Incompatibility is so last season! Might be the right time to give your driver a little upgrade to the latest version. This will ensure smooth support for latest syntax and PostgreSQL features including our hero – the record count.

Supercharging with WITH queries

Utilizing WITH queries, or Common Table Expressions (CTEs), is like adding a turbo boost to your operations. Combined with the RETURNING clause, you harness the true power of efficient data management:

-- Fasten your seatbelts!
WITH rows AS (
  UPDATE table_name SET column1 = value1 WHERE column2 = 'condition'
  RETURNING *  -- Voila!
)
SELECT count(*) FROM rows;  -- Check the scoreboard!

Evading data traps: Multiple row modifications

When performing an UPDATE across multiple rows, it's like walking a tightrope - keep your balance and ensure your RETURNING clause counts the correct number of modified rows:

-- Let's paint the whole town red!
UPDATE table_name
SET column1 = 'new_value'  -- New paint, anyone?
WHERE column2 > 10
RETURNING *;  -- Show me the masterpiece!

Defense strategies: Common pitfalls to avoid

Always be vigilant! Long running transactions in high concurrency environments are like ticking time bombs – they might lead to performance bottlenecks or deadlocks, so handle with care! Likewise, always ensure the syntax you employ is in sync with your PostgreSQL version – it's like making sure your shoes match your outfit!

explain-codes / Sql / Get count of records affected by INSERT or UPDATE in PostgreSQL

Linked

Running a .sql script using MySQL with JDBC



Get affected rows on ExecuteNonQuery



Efficient way to do batch INSERTS with JDBC



Sql try/catch rollback/commit - preventing erroneous commit after rollback



Add column to table and then update it inside transaction

