# Migrate virtual generated columns from Oracle to PostgreSQL
<a name="migrate-virtual-generated-columns-from-oracle-to-postgresql"></a>

*Veeranjaneyulu Grandhi, Rajesh Madiwale, and Ramesh Pathuri, Amazon Web Services*

## Summary
<a name="migrate-virtual-generated-columns-from-oracle-to-postgresql-summary"></a>

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](https://www.postgresql.org/docs/12/ddl-generated-columns.html) are similar to Oracle virtual columns.

## Prerequisites and limitations
<a name="migrate-virtual-generated-columns-from-oracle-to-postgresql-prereqs"></a>

**Prerequisites**
+ An active AWS account
+ A source Oracle database 
+ Target PostgreSQL databases (on Amazon RDS for PostgreSQL or Aurora PostgreSQL-Compatible)
+ [PL/pgSQL](https://www.postgresql.org/docs/current/plpgsql.html) 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
<a name="migrate-virtual-generated-columns-from-oracle-to-postgresql-tools"></a>
+ [pgAdmin 4](https://www.pgadmin.org/) 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](https://www.oracle.com/database/sqldeveloper/) is a free, integrated development environment for working with SQL in Oracle databases in both traditional and cloud deployments. 

## Epics
<a name="migrate-virtual-generated-columns-from-oracle-to-postgresql-epics"></a>

### Create source and target database tables
<a name="create-source-and-target-database-tables"></a>


| Task | Description | Skills required | 
| --- | --- | --- | 
| Create a source Oracle Database table. | In Oracle Database, create a table with virtual generated columns by using the following statement.<pre>CREATE TABLE test.generated_column<br />( CODE NUMBER,<br />STATUS VARCHAR2(12) DEFAULT 'PreOpen',<br />FLAG CHAR(1) GENERATED ALWAYS AS (CASE UPPER(STATUS) WHEN 'OPEN' THEN 'N' ELSE 'Y' END) VIRTUAL VISIBLE<br />);</pre><br />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.<pre>CREATE TABLE test.generated_column<br />(<br />    code integer not null,<br />    status character varying(12) not null ,<br />    flag character(1)<br />);</pre><br />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 | 

### Create a trigger function to handle the virtual column in PostgreSQL
<a name="create-a-trigger-function-to-handle-the-virtual-column-in-postgresql"></a>


| Task | Description | Skills required | 
| --- | --- | --- | 
| Create a PostgreSQL trigger. | In PostgreSQL, create a trigger.<pre>CREATE TRIGGER tgr_gen_column<br />AFTER INSERT OR UPDATE OF status ON test.generated_column<br />FOR EACH ROW <br />EXECUTE FUNCTION test.tgf_gen_column();</pre> | 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.<pre>CREATE OR REPLACE FUNCTION test.tgf_gen_column() RETURNS trigger AS $VIRTUAL_COL$<br />BEGIN<br />IF (TG_OP = 'INSERT') THEN<br />IF (NEW.flag IS NOT NULL) THEN<br />RAISE EXCEPTION 'ERROR: cannot insert into column "flag"' USING DETAIL = 'Column "flag" is a generated column.';<br />END IF;<br />END IF;<br />IF (TG_OP = 'UPDATE') THEN<br />IF (NEW.flag::VARCHAR != OLD.flag::varchar) THEN<br />RAISE EXCEPTION 'ERROR: cannot update column "flag"' USING DETAIL = 'Column "flag" is a generated column.';<br />END IF;<br />END IF;<br />IF TG_OP IN ('INSERT','UPDATE') THEN<br />IF (old.flag is NULL) OR (coalesce(old.status,'') != coalesce(new.status,'')) THEN<br />UPDATE test.generated_column<br />SET flag = (CASE UPPER(status) WHEN 'OPEN' THEN 'N' ELSE 'Y' END)<br />WHERE code = new.code;<br />END IF;<br />END IF;<br />RETURN NEW;<br />END<br />$VIRTUAL_COL$ LANGUAGE plpgsql;</pre> | DBA, App developer | 

### Test data migration by using AWS DMS
<a name="test-data-migration-by-using-aws-dms"></a>


| Task | Description | Skills required | 
| --- | --- | --- | 
| Create a replication instance. | To create a replication instance, follow the [instructions](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_ReplicationInstance.Creating.html) 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](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Endpoints.Creating.html) 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](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.Creating.html) and [Full-load task settings](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TaskSettings.FullLoad.html) 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
<a name="migrate-virtual-generated-columns-from-oracle-to-postgresql-resources"></a>
+ [Getting started with AWS Database Migration Service](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_GettingStarted.html) (AWS DMS documentation)
+ [Using an Oracle database as a source for AWS DMS](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.Oracle.html) (AWS DMS documentation)
+ [Using a PostgreSQL database as a target for AWS DMS](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.PostgreSQL.html) (AWS DMS documentation)
+ [Generated columns in PostgreSQL](https://www.postgresql.org/docs/12/ddl-generated-columns.html) (PostgreSQL documentation)
+ [Trigger functions](https://www.postgresql.org/docs/12/plpgsql-trigger.html) (PostgreSQL documentation)
+ [Virtual columns](https://docs.oracle.com/database/121/SQLRF/statements_7002.htm#SQLRF01402) in Oracle Database (Oracle documentation)