Last Updated Columns With Postgres - Gunnar Morling

[giscus[bot]]

Last Updated Columns With Postgres - Gunnar Morling

In many applications it’s a requirement to keep track of when a record was created and updated the last time.
Often, this is implemented by having columns such as created_at and updated_at within each table.
To make things as simple as possible for application developers,
the database itself should take care of maintaining these values automatically when a record gets inserted or updated.

https://www.morling.dev/blog/last-updated-columns-with-postgres/

[AdrienVH-c2c]

Would it be possible to handle the updated_at with a GENERATED column ?

CREATE TABLE movie (
  id SERIAL NOT NULL,
  title TEXT,
  viewer_rating NUMERIC(2, 1),
  created_at TIMESTAMP NOT NULL DEFAULT current_timestamp,
  updated_at TIMESTAMP GENERATED ALWAYS AS current_timestamp STORED
);

This way, we wouldn't have to add an updated_at = DEFAULT to all of our UPDATE queries...

[AdrienVH-c2c]

Looks like the answer is no : https://stackoverflow.com/questions/62793363/is-it-possible-to-update-a-columnautomatically-with-current-timestamp-in-pos

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.

[gunnarmorling]

Ha, you got my hopes up there for a second :) That would be great indeed, but unfortunately not possible as-is.

[morrisdeoryx]

What a great idea! Bugs me every time. You solution is a definite improvement over having to write (and run) a ROW trigger for something so basic. Thanks!

[StuartBertram]

Interesting approach. What's the benefit of updated_at = DEFAULT over just using updated_at = current_timestamp? Is it just that it always gets the same level of accuracy rather than trying to remember to use current_timestamp vs current_date vs localtimestamp?

[yisibl]

In AliCloud RDS PostgreSQL, on_update_set_default is implemented to automatically update the time.

ALTER TABLE test ALTER COLUMN update_time SET(on_update_set_default=true);

https://help.aliyun.com/zh/rds/apsaradb-rds-for-postgresql/automatic-update-of-time-columns?spm=a2c4g.11186623.0.i15