pg-safeupdate: Required Where Clauses

pg-safeupdate is a PostgreSQL extension designed to prevent users from accidentally updating or deleting too many records in a single statement by requiring a "where" clause in all update and delete statements.

The pg-safeupdate extension is a useful tool for protecting data integrity and preventing accidental data loss. Without it, a user could accidentally execute an update or delete statement that affects all records in a table. With pg-safeupdate, users are required to be more deliberate in their update and delete statements, which reduces the risk of significant error.

Enable the extension#

pg-safeupdate can be enabled on a role basis (where anon is the role we want to modify in this example):

Usage#

Let's take a look at an example to see how pg-safeupdate works. Suppose we have a table called employees with the following columns: id, name, department, and date_of_birth. We want to update the date_of_birth for a specific employee with the id of 12345. Here is what the query would look like if we forgot to add a "where" clause:

This query updates the date_of_birth for all employees to 1987-01-28, which is not what we intended. With pg-safeupdate enabled, we receive an error message prompting us to add a "where" clause to the query:

We would then update our query to include a "where" clause specifying the employee with the id of 12345:

Resources#

• Official pg-safeupdate documentation
• Using pg-safeupdate with PostgREST