ApsaraDB RDS:Automatic time column update

Prerequisites

• The RDS instance runs PostgreSQL 15 or a later.

• The RDS instance runs the minor engine version 20241230 or later.

  Note

  For more information about how to upgrade the minor engine version of an instance, see Update the minor engine version.

Overview

In some cases, applications require an automatic update mechanism for the time column in a table when they update a row of data in the table regardless of whether the time column is explicitly defined in the statement. In native PostgreSQL, you can only configure triggers to achieve this goal. In RDS for PostgreSQL, you can configure the on_update_set_default parameter to automatically update the specified time column without using triggers.

This feature supports the update of time columns that store the following types of data: TIMESTAMP, TIMESTAMPTZ, DATE, TIME, TIMETZ, and INTERVAL.

Enable automatic update for a specified time column

You can use the following syntax to enable automatic update for a specified time column by setting the on_update_set_default parameter to true:

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

Example

1. Create a table named test and insert a row of test data to the table.

   CREATE TABLE test (id INT, create_time TIMESTAMPTZ DEFAULT now(), update_time TIMESTAMPTZ DEFAULT now());
   
   INSERT INTO test VALUES(1);
   
   SELECT * FROM test;

   Sample result:

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

2. Update the data in the test table.

   UPDATE test SET id=2;
   
   SELECT * FROM test;

   Sample result:

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

   As shown in the result, the time column (update_time) is not updated.

3. Set the on_update_set_default parameter to true for the update_time column in the test table to enable automatic update for the column.

   ALTER TABLE test ALTER COLUMN update_time SET(on_update_set_default=true);
   
   SELECT attoptions FROM pg_attribute WHERE attname='update_time';

   Sample result:

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

4. Update the data in the test table.

   UPDATE test SET id=3;
   
   SELECT * FROM test;

   Sample result:

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

   As shown in the result, the update_time column is automatically updated.