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
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.