Is it possible to update a column(automatically) with "current_timestamp" in PostgreSQL using "Generated Columns"?

Question

Is it possible to update a column (automatically) with "current_timestamp" in PostgreSQL using "Generated Columns", whenever the row gets update?

At present, I am using trigger to update the audit field last_update_date. But I am planning to switch to generated column

ALTER TABLE test ADD COLUMN last_update_date timestamp without time zone 
GENERATED ALWAYS AS (current_timestamp) STORED;

Getting error while altering column

ERROR: generation expression is not immutable

Sheriff

Answer 1

No, that won't work, for the reason specified in the error.

Functions used in generated columns must always return the same value for the same arguments, that is, depend on nothing but the current database row. current_timestamp obviously is not of that kind.

If PostgreSQL did allow such functions to be used in generated columns, then the value of the column would change if the database is restored from a pg_dump, for example.

Use a BEFORE INSERT OR UPDATE trigger for this purpose.

Maiga