Migrate virtual generated columns from Oracle to PostgreSQL

Create a source Oracle Database table.

In Oracle Database, create a table with virtual generated columns by using the following statement.

CREATE TABLE test.generated_column
( CODE NUMBER,
STATUS VARCHAR2(12) DEFAULT 'PreOpen',
FLAG CHAR(1) GENERATED ALWAYS AS (CASE UPPER(STATUS) WHEN 'OPEN' THEN 'N' ELSE 'Y' END) VIRTUAL VISIBLE
);

In this source table, the data in the STATUS column is migrated through AWS DMS to the target database. The FLAG column, however, is populated by using generate by functionality, so this column isn’t visible to AWS DMS during migration. To implement the functionality of generated by, you must use triggers and functions in the target database to populate the values in the FLAG column, as shown in the next epic.

Create a target PostgreSQL table on AWS.

Create a PostgreSQL table on AWS by using the following statement.

CREATE TABLE test.generated_column
(
    code integer not null,
    status character varying(12) not null ,
    flag character(1)
);

In this table, the status column is a standard column. The flag column will be a generated column based on the data in the status column.

Create a PostgreSQL trigger.

In PostgreSQL, create a trigger.

CREATE TRIGGER tgr_gen_column
AFTER INSERT OR UPDATE OF status ON test.generated_column
FOR EACH ROW 
EXECUTE FUNCTION test.tgf_gen_column();

Create a PostgreSQL trigger function.

In PostgreSQL, create a function for the trigger. This function populates a virtual column that is inserted or updated by the application or AWS DMS, and validates the data.

CREATE OR REPLACE FUNCTION test.tgf_gen_column() RETURNS trigger AS $VIRTUAL_COL$
BEGIN
IF (TG_OP = 'INSERT') THEN
IF (NEW.flag IS NOT NULL) THEN
RAISE EXCEPTION 'ERROR: cannot insert into column "flag"' USING DETAIL = 'Column "flag" is a generated column.';
END IF;
END IF;
IF (TG_OP = 'UPDATE') THEN
IF (NEW.flag::VARCHAR != OLD.flag::varchar) THEN
RAISE EXCEPTION 'ERROR: cannot update column "flag"' USING DETAIL = 'Column "flag" is a generated column.';
END IF;
END IF;
IF TG_OP IN ('INSERT','UPDATE') THEN
IF (old.flag is NULL) OR (coalesce(old.status,'') != coalesce(new.status,'')) THEN
UPDATE test.generated_column
SET flag = (CASE UPPER(status) WHEN 'OPEN' THEN 'N' ELSE 'Y' END)
WHERE code = new.code;
END IF;
END IF;
RETURN NEW;
END
$VIRTUAL_COL$ LANGUAGE plpgsql;

Create a replication instance.

To create a replication instance, follow the instructions in the AWS DMS documentation. The replication instance should be in the same virtual private cloud (VPC) as your source and target databases.

Create source and target endpoints.

To create the endpoints, follow the instructions in the AWS DMS documentation.

Test the endpoint connections.

You can test the endpoint connections by specifying the VPC and replication instance and choosing Run test.

Create and start a full load task.

For instructions, see Creating a Task and Full-load task settings in the AWS DMS documentation.

Validate the data for the virtual column.

Compare the data in the virtual column in the source and target databases. You can validate the data manually or write a script for this step.