Database migrations look simple on paper: export from Oracle, import into PostgreSQL, done. In practice, the real work is in the details: how a value is represented, and how much history actually needs to move.
In reality, the real challenge lies in preserving business semantics. Small differences in how values are represented, how legacy data has evolved over time, and how the target application expects to consume that data can significantly impact a migration.

This post walks through a real Oracle-to-PostgreSQL migration challenge, including the source schema, sample data, the generated PostgreSQL schema, the Ora2Pg configuration used, and the reasoning behind each step.

Transforming CHAR(1) (‘Y’/‘N’) into SMALLINT (1/0)

Source Table in Oracle

CREATE TABLE demo.customers (
    customer_id   NUMBER(10) PRIMARY KEY,
    customer_name VARCHAR2(100) NOT NULL,
    email         VARCHAR2(150),
    phone_number  VARCHAR2(20),
    gender        VARCHAR2(10),
    date_of_birth DATE,
    city          VARCHAR2(50),
    state         VARCHAR2(50),
    country       VARCHAR2(50),
    postal_code   VARCHAR2(10),
    registration_date DATE,
    customer_type VARCHAR2(20),
    credit_limit  NUMBER(10,2),
    active        CHAR(1)
);

Sample Data in Oracle

CUSTOMER_ID|CUSTOMER_NAME|EMAIL                  |PHONE_NUMBER|GENDER|DATE_OF_BIRTH      |CITY  |STATE       |COUNTRY|POSTAL_CODE|REGISTRATION_DATE  |CUSTOMER_TYPE|CREDIT_LIMIT|ACTIVE|
-----------+-------------+-----------------------+------------+------+-----------------------+----------+------------+-------+-----------+-----------------------+-------------+------------+------+
          1|Customer001  |customer001@example.com|9000000001  |Male  |1990-01-26 00:00:00.000|Bengaluru |Karnataka   |India  |500001 |2026-07-14 06:31:44.000|Premium  |       11000|Y |
          2|Customer002  |customer002@example.com|9000000002  |Female|1990-02-20 00:00:00.000|Chennai   |Tamil Nadu  |India  |500002     |2026-07-13 06:31:44.000|Gold         |   12000|Y |
          3|Customer003  |customer003@example.com|9000000003  |Male  |1990-03-17 00:00:00.000|Mumbai |Maharashtra |India  |500003 |2026-07-12 06:31:44.000|Regular  |       13000|N |

The ACTIVE column stores business status using 'Y' and 'N'

PostgreSQL Schema After Conversion

Suppose the target application expects the ACTIVE column to be stored as a numeric flag instead of characters.

The converted PostgreSQL schema therefore defines the column as
active SMALLINT 

                     Table "public.customers"
      Column   |          Type          | Collation | Nullable | Default
-------------------+--------------------------------+-----------+----------+---------
  customer_id   | bigint                     |       | not null |
  customer_name | character varying(100)     |       | not null |
  email         | character varying(150)     |       |      |
  phone_number  | character varying(20)      |       |      |
  gender        | character varying(10)      |       |      |
  date_of_birth | timestamp(0) without time zone |       |      |
  city          | character varying(50)      |       |      |
  state         | character varying(50)      |       |      |
  country       | character varying(50)      |       |      |
  postal_code   | character varying(10)      |       |      |
  registration_date | timestamp(0) without time zone |       |      |
  customer_type | character varying(20)      |       |      |
  credit_limit  | numeric(10,2)              |       |      |
  active        | smallint                   |       |      |
Indexes:
    "customers_pkey" PRIMARY KEY, btree (customer_id)

The ACTIVE column has already been created as a SMALLINT in PostgreSQL.

However, the schema conversion is only half of the migration. The source Oracle table still stores the data as ‘Y’ and ‘N’. Unless those values are transformed during the data migration, PostgreSQL cannot load character values into a SMALLINT column.

In other words, schema conversion alone does not convert the data. The business values still need to be mapped appropriately during export.

Why Schema Conversion Alone Is Not Enough

At first glance, converting the column type from CHAR(1) to SMALLINT seems sufficient.

However, the Oracle source data still contains: ‘Y’ and ‘N’
When Ora2Pg exports these values without any transformation, PostgreSQL attempts to insert them directly into the SMALLINT column. 

This results in an error similar to 

ERROR: invalid input syntax for type smallint: "Y" CONTEXT: COPY customers, line 1, column active: "Y"

The schema has been converted, but the data still has not.

This is an important distinction:

Schema conversion changes the structure of the database. Data migration must preserve the business meaning of the stored values.

Data Migration Using REPLACE_QUERY

The following Ora2Pg configuration converts the source values while exporting the data.

TYPE COPY
LIMIT 1000

REPLACE_QUERY CUSTOMERS[
SELECT
CUSTOMER_ID,
CUSTOMER_NAME,
EMAIL,
PHONE_NUMBER,
GENDER,
DATE_OF_BIRTH,
CITY,
STATE,
COUNTRY,
POSTAL_CODE,
REGISTRATION_DATE,
CUSTOMER_TYPE,
CREDIT_LIMIT,
CASE
    WHEN ACTIVE='Y' THEN 1
    WHEN ACTIVE='N' THEN 0
    ELSE NULL
END AS ACTIVE
FROM DEMO.CUSTOMERS
]

This maps the values as follows:

• ‘Y’ → 1
• ‘N’ → 0
• Any unexpected value → NULL

Using NULL for unexpected values prevents silent data corruption and highlights records that require business review.

Why Not Convert Everything Automatically?

Automatically converting every value during migration may seem convenient, but it can hide underlying data quality issues.

Consider source data such as:

Y
N
A
?
(blank)

Only Y and N have defined business meaning. If every unexpected value is automatically forced to 0, incorrect business information enters the target system without anyone noticing.

By allowing unexpected values to become NULL, the migration highlights data anomalies that require review and approval from the business team.

Why Use a Staged Migration Approach?

A reliable Oracle-to-PostgreSQL migration  is not simply about copying data from one database to another. It should follow a structured and auditable process:

A Recommended Migration Approach

1. Convert the database schema.
2. Validate the converted schema.
3. Migrate the data.
4. Validate the migrated data.
5. Identify and review exceptions with the business.
6. Apply approved business transformations.
7. Perform the final production cutover.

Separating schema conversion, data migration, validation, and business transformation ensures that every exception is visible, traceable, and resolved before the application goes live.

Here is a reference video :

Key Takeaway

Oracle-to-PostgreSQL migration is much more than copying tables and data.

The real engineering challenge lies in preserving business semantics while adapting to differences in data types, storage models, and application expectations.

In this example, converting a simple CHAR(1) column to SMALLINT required careful planning to ensure that business meaning was preserved and unexpected values were identified instead of silently converted.

Successful migrations are not measured by how quickly data moves they are measured by how accurately business rules are preserved and how confidently the migrated application behaves in production.