Get the count of affected rows with ease: The power of the RETURNING clause in Postgres

When working with databases, it is frequently vital to know how many rows have been changed by a query, and this is especially true when conducting operations such as updates or removals. Through its RETURNING clause, Postgres offers a method that is both powerful and versatile for doing this task.

Postgres's RETURNING clause makes it possible to retrieve both the data of the impacted rows and the total number of rows that were modified or deleted. The RETURNING clause is used to get certain columns from a database. It is appended at the end of a query.

For example, to update all rows in a table where a certain condition is true and return the number of affected rows, you would use the following query:

UPDATE mytable SET mycolumn = 'new value' WHERE mycondition = true RETURNING *;

This query would update the data of all rows that fit the condition "mycondition = true" and it would return the data of the rows that were changed.

In the same way that you would use the following query if you wanted to use the RETURNING clause with the DELETE statement, you also have the option to do so.

DELETE FROM mytable WHERE mycondition = true RETURNING *;

It's important to remember that the RETURNING clause only works with update and delete statements, not with select statements. It's also only available in Postgres, not in other RDBMS.

In conclusion, the RETURNING clause in Postgres is a powerful tool that makes it easy to find out how many rows are affected by update and delete queries. it can make it much easier to write robust and reliable code that works with databases.