Configure automatic time column update for PostgreSQL without triggers - ApsaraDB RDS

PostgreSQL does not natively refresh a timestamp column when a row is updated — unlike MySQL, which provides ON UPDATE CURRENT_TIMESTAMP for this purpose. ApsaraDB RDS for PostgreSQL addresses this gap with the on_update_set_default parameter: set it on any time column and the column's DEFAULT expression re-evaluates on every UPDATE, keeping the timestamp current without any trigger logic.

Prerequisites

Before you begin, ensure that you have:

An RDS for PostgreSQL instance running PostgreSQL 15 or later
Minor engine version 20241230 or later

To upgrade the minor engine version, see Update the minor engine version.

How it works

A typical table has two time columns with distinct semantics:

create_time — set once at INSERT and never changed
update_time — should refresh automatically on every UPDATE

The on_update_set_default parameter works by re-evaluating the column's DEFAULT expression on each UPDATE. The parameter supports the following column data types: TIMESTAMP, TIMESTAMPTZ, DATE, TIME, TIMETZ, and INTERVAL.

Enable automatic update on a time column

Run the following statement to enable automatic update for a column:

ALTER TABLE <table> ALTER COLUMN <column> SET (on_update_set_default=true);

Replace <table> with your table name and <column> with the target time column name.

Example

The following example demonstrates how on_update_set_default keeps update_time current while create_time remains at its original insertion value.

Step 1: Create a table and insert a row.

CREATE TABLE test (id INT, create_time TIMESTAMPTZ DEFAULT now(), update_time TIMESTAMPTZ DEFAULT now());

INSERT INTO test VALUES(1);

SELECT * FROM test;

Expected output:

 id |          create_time          |          update_time
----+-------------------------------+-------------------------------
  1 | 2025-01-09 16:07:48.481879+08 | 2025-01-09 16:07:48.481879+08

Step 2: Update the row without enabling automatic update.

UPDATE test SET id=2;

SELECT * FROM test;

Expected output:

 id |          create_time          |          update_time
----+-------------------------------+-------------------------------
  2 | 2025-01-09 16:07:48.481879+08 | 2025-01-09 16:07:48.481879+08

update_time is unchanged because on_update_set_default is not yet enabled.

Step 3: Enable automatic update on the `update_time` column.

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

Verify that the parameter is set:

SELECT attoptions FROM pg_attribute WHERE attname='update_time';

Expected output:

       attoptions
------------------------------
 {on_update_set_default=true}

Step 4: Update the row again and confirm that `update_time` refreshes.

UPDATE test SET id=3;

SELECT * FROM test;

Expected output:

 id |          create_time          |          update_time
----+-------------------------------+-------------------------------
  3 | 2025-01-09 16:07:48.481879+08 | 2025-01-09 16:08:39.601701+08

update_time now refreshes automatically on every UPDATE, while create_time remains at its original insertion value.