Migrate virtual generated columns from Oracle to PostgreSQL - AWS Prescriptive Guidance
SummaryPrerequisites and limitationsToolsEpicsRelated resources

Migrate virtual generated columns from Oracle to PostgreSQL

Created by Veeranjaneyulu Grandhi (AWS), Rajesh Madiwale (AWS), and Ramesh Pathuri (AWS)

Environment: Production

Source: Oracle Database

Target: Amazon RDS for PostgreSQL or Aurora PostgreSQL-Compatible

R Type: Re-architect

Workload: Oracle

Technologies: Migration; Databases

AWS services: Amazon Aurora; Amazon RDS; AWS DMS

Summary

In version 11 and earlier, PostgreSQL doesn’t provide a feature that is directly equivalent to an Oracle virtual column. Handling virtual generated columns while migrating from Oracle Database to PostgreSQL version 11 or earlier is difficult for two reasons: 

  • Virtual columns aren’t visible during migration.

  • PostgreSQL doesn't support the generate expression before version 12.

However, there are workarounds to emulate similar functionality. When you use AWS Database Migration Service (AWS DMS) to migrate data from Oracle Database to PostgreSQL version 11 and earlier, you can use trigger functions to populate the values in virtual generated columns. This pattern provides examples of Oracle Database and PostgreSQL code that you can use for this purpose. On AWS, you can use Amazon Relational Database Service (Amazon RDS) for PostgreSQL or Amazon Aurora PostgreSQL-Compatible Edition for your PostgreSQL database.

Starting with PostgreSQL version 12, generated columns are supported. Generated columns can either be calculated from other column values on the fly, or calculated and stored. PostgreSQL generated columns are similar to Oracle virtual columns.

Prerequisites and limitations

Prerequisites

  • An active AWS account

  • A source Oracle database

  • Target PostgreSQL databases (on Amazon RDS for PostgreSQL or Aurora PostgreSQL-Compatible)

  • PL/pgSQL coding expertise

Limitations

  • Applies only to PostgreSQL versions before version 12. 

  • Applies to Oracle Database version 11g or later.

  • Virtual columns are not supported in data migration tools.

  • Applies only to columns defined in the same table.

  • If a virtual generated column refers to a deterministic user-defined function, it cannot be used as a partitioning key column.

  • The output of the expression must be a scalar value. It cannot return an Oracle supplied datatype, a user-defined type, LOB, or LONG RAW.

  • Indexes that are defined against virtual columns are equivalent to function-based indexes in PostgreSQL.

  • Table statistics must be gathered.

Tools

  • pgAdmin 4 is an open source management tool for PostgreSQL. This tool provides a graphical interface that simplifies the creation, maintenance, and use of database objects.

  • Oracle SQL Developer is a free, integrated development environment for working with SQL in Oracle databases in both traditional and cloud deployments. 

Epics

TaskDescriptionSkills required

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.

DBA, App developer

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.

DBA, App developer
TaskDescriptionSkills required

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();
DBA, App developer

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;
DBA, App developer
TaskDescriptionSkills required

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.

DBA, App developer

Create source and target endpoints.

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

DBA, App developer

Test the endpoint connections.

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

DBA, App developer

Create and start a full load task.

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

DBA, App developer

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.

DBA, App developer

Related resources