U0400003: The GaussDB column definition does not support ON UPDATE

Description

Database Type and Version

• Source database type and version: MySQL 5.5, 5.6, 5.7, and 8.0
• Target database type and version: GaussDB V2.0-3.2 and earlier

Syntax Example

This error is reported because UGO does not convert ON UPDATE by default.

In GaussDB V2.0-3.2 and earlier versions, ON UPDATE cannot be specified in the table column definition, for example:

CREATE TABLE demo (
    id INT,
    name VARCHAR(255) DEFAULT null,
    create_time TIMESTAMP WITH TIME ZONE DEFAULT (CLOCK_TIMESTAMP())::TIMESTAMP(0) WITH TIME ZONE,
    update_time TIMESTAMP WITH TIME ZONE DEFAULT (CLOCK_TIMESTAMP())::TIMESTAMP(0) WITH TIME ZONE ON UPDATE CURRENT_TIMESTAMP
);

Suggestion

Modify Support for on update and enable ON UPDATE using a trigger. Triggers may affect performance and make database maintenance more difficult. Therefore, you need to fully evaluate the impact on services.

You can also manually comment out ON UPDATE, for example:

CREATE TABLE demo (
    id INT,
    name VARCHAR(255) DEFAULT null,
    create_time TIMESTAMP WITH TIME ZONE DEFAULT (CLOCK_TIMESTAMP())::TIMESTAMP(0) WITH TIME ZONE,
    update_time TIMESTAMP WITH TIME ZONE DEFAULT (CLOCK_TIMESTAMP())::TIMESTAMP(0) WITH TIME ZONE /*ON UPDATE CURRENT_TIMESTAMP*/
);

In this case, the value of update_time needs to be explicitly updated each time the demo table is updated to ensure data accuracy.