U0400057: GaussDB does not support functions in partition keys of tables

Description

GaussDB does not support functions in partition keys of tables.

Database Type and Version

• Source database type and version: MySQL 5.5, 5.6, 5.7, and 8.0
• Target database type and version: all GaussDB versions

create table test_to
(
    id  date,
    id2 date
)
partition by range (to_days(id2))
    (
    partition p1 values less than (100),
    partition p2 values less than (maxvalue)
    );

Suggestion

Method 1:

Add a redundant column as the basis of the new partition. The trigger ensures that the value of the redundant column is always the same as the result of the original partition expression.

This method is applicable to centralized B-compatible GaussDB and distributed MySQL-compatible GaussDB instances.

Example:

create table test_to
(
    id      date,
    id2     date,
    COLUMN1 bigint
)
partition by range (COLUMN1)
    (
    partition p1 values less than (100),
    partition p2 values less than (maxvalue)
    );

CREATE OR REPLACE FUNCTION FUNC_test_to RETURN TRIGGER
as
BEGIN
    NEW.COLUMN1=to_days(new.id2);
    RETURN NEW;
END;
/
CREATE TRIGGER TRI_test_to
    BEFORE insert or update ON test_to
    FOR EACH ROW
EXECUTE PROCEDURE FUNC_test_to();