Create an updated_at
timestamp column that is automatically updated whenever row values are changed in a postgresql database.
- Modifying a column when a row is changed requires a
BEFORE UPDATE
TRIGGER
on row updates. - A
trigger
cancall
aprocedure
- A function can create a
trigger
programatically.
I searched and found a variety of sources with a handful of suggestions and examples on StackOverflow, sql blogs, and similar. The code I found was OK at best, with redundancies and shortcomings.
That research taken together with the excellent postgresql docs and a fair amount of experimentation brought me to create the following snippets.
For my uses, I want every updated_at
column to have a trigger to update when the row is modified.
- Use
moddatetime
for the trigger, which is distributed with postgresql.moddatetime
is documented in thespi
section
- A function to create the trigger for a table.
- Takes the name of the table and optionally the name of the column, defaulting to
updated_at
- Takes the name of the table and optionally the name of the column, defaulting to
- A query to apply the trigger to all tables containing the
updated_at
column.- Search for all tables in the current schema containing an
updated_at
column.
- Search for all tables in the current schema containing an
- in the WHEN clause of the trigger I have used
WHEN (OLD DISTINCT FROM NEW)
this is subtely different and preferred toWHEN OLD <> NEW
when comparingNULL
columns.- The WHEN clause exists to prevent the timestamp from changing when a row is "updated" with the current fields and no net changes. If you consider that an update with tracking, then remove the WHEN clause completely.
- The database schema is a separation of a single database by namespace. The default schema is
public
unless the client changes it for a connection. Database schema is entirely different from table schemas. Data and functions in other schemas can be accessed directly. Accessing data from other databases requires work and connectors. - Wrapping a query with multiple rows of output into a function is a pain.
DROP TRIGGER IF EXISTS
vsCREATE OR REPLACE TRIGGER
, the latter is added in Postgresql 14. My target database was still stuck on postgresql 13. https://www.postgresql.org/docs/current/sql-createtrigger.html
Updated to add a return string from the function containing the name of the trigger.
Also improved clarity of the FORMAT argument by using values by index rather than having to duplicate
the variable names.
%s
=>%1$s
. Positional arguments are numbered from 1.