A recent tweet pointed me to safeupdate which is a little extension that forbids unconditional UPDATE and DELETE statements, i.e. without WHERE clause.
Let's see how this works...
The build and install procedure is pretty non-spectacular: make, make install and then either activate it per session with load 'safeupdate' or globally by adding it to postgresql.conf:
shared_preload_libraries = 'safeupdate'
After that, if you try an unconditional UPDATE or DELETE on any table, you'll get the following errors:
ERROR: UPDATE requires a WHERE clause
and
ERROR: DELETE requires a WHERE clause
respectively and the operation is cancelled.
Unconditional UPDATE and DELETE can still be forced by using a condition that always evaluates to true, like
WHERE TRUE or WHERE 1=1
But now you have to explicitly request this behaviour.
I think this is ever so useful to protect against human error, that I wonder why this isn't something mandatory, even if it's non-standard.
However, there is one glitch: If you JOIN tables to UPDATE or DELETE only certain rows that match the ON condition, this is not recognized as a conditional operation and the WHERE TRUE is still needed to pacify the extension. Well, I can live with that...
