Overcoming 50 Common PostgreSQL‑Oracle Migration Pitfalls in Financial Systems

The author, a technical manager at an insurance company, shares extensive experience migrating Oracle‑based financial systems to PostgreSQL, focusing on real‑world problems and practical solutions.

PostgreSQL vs Oracle: Technical Comparison

From a development and application perspective, a well‑built database is useless if applications cannot run efficiently on it. The article compares PostgreSQL and Oracle across architecture, data synchronization, sharding, RAC, and read‑write separation, highlighting PostgreSQL’s compatibility with SQL standards and extensibility.

Key Migration Challenges and Solutions

1. Character Set Issues

PostgreSQL does not support GBK; it uses UTF‑8. The EUC_CN encoding cannot handle many characters, so it is not recommended.

2. Multi‑line Comment Syntax

Oracle allows /* ... */ comments, but PostgreSQL treats them as illegal. Use -- for single‑line comments or proper PostgreSQL‑compatible multi‑line syntax.

3. NUMERIC Type Limitations

PostgreSQL rejects negative scales and scales larger than precision. Work‑arounds include using triggers with round() or adjusting the column definition, e.g. NUMERIC(3,3) instead of NUMBER(2,3).

SELECT round(123.6, -2);  -- returns 100

4. VARCHAR Truncation

When a value exceeds the defined length, PostgreSQL silently truncates instead of raising an error, which can lead to silent data loss.

5. CHAR Length Semantics

PostgreSQL stores CHAR as the actual number of characters, unlike Oracle’s padded semantics, causing compatibility errors that may require custom functions.

6. SEQUENCE Maximum Value

PostgreSQL’s SEQUENCE max is 9223372036854775807 (bigint), generally sufficient, but some legacy Oracle sequences exceed this. The suggested solution is to use a NUMERIC column with a trigger to emulate a larger sequence.

7. Type Casting

Implicit casts differ; explicit CAST statements or custom cast definitions are needed. Example:

# CREATE CAST (varchar AS integer) WITH INOUT AS IMPLICIT;

8. Subquery Alias Requirement

Oracle permits subqueries without an alias, while PostgreSQL requires one.

9. SELECT Expression Alias Issues

Oracle allows certain identifiers as aliases that PostgreSQL treats as keywords, causing errors. Adjust alias names or quote them.

10. SELECT INTO Restrictions

PostgreSQL follows the SQL standard and does not support some Oracle‑specific SELECT … INTO forms.

11. UPDATE Syntax Differences

Certain Oracle‑style UPDATE statements are illegal in PostgreSQL; rewrite to conform to the standard.

12. Oracle → PostgreSQL Access (DBLINK)

Using Oracle Database Gateway for DBLINK works between Oracle instances but not for PostgreSQL, which lacks 2‑phase commit support. Performance discrepancies were observed between test and production environments.

13. PostgreSQL → Oracle Access (oracle_fdw)

oracle_fdw works but may raise errors under high concurrency due to its default serializable transaction level; a retry mechanism and lowering the isolation level in the source code resolved the issue.

14. Empty String vs NULL

Oracle treats empty strings as NULL; PostgreSQL distinguishes them. The team customized PostgreSQL’s core to make empty strings equivalent to NULL, reducing migration code changes.

15. SYNONYM Absence

PostgreSQL lacks SYNONYM; use search_path adjustments or VIEWs as alternatives.

16. Column Name Case Sensitivity

Oracle stores identifiers in uppercase; PostgreSQL stores them in lowercase. Tools like MyBatis must map case correctly to avoid missing columns.

17. PACKAGE Emulation

PostgreSQL does not have PACKAGE; schemas can be used to simulate package functionality.

Performance Tuning Examples

Complex queries that run in milliseconds on Oracle may take seconds on PostgreSQL. Rewriting queries to match Oracle’s execution plan—e.g., using tuple IN predicates—can dramatically improve performance.

SELECT phone
FROM lcaddress
WHERE (customerno, addressno) IN (
    SELECT insuredno, addressno
    FROM lcinsured
    WHERE contno = '100005522831'
      AND sequenceno = 1
);

High Availability Architecture

PostgreSQL high‑availability is achieved with PGPool for load balancing and a primary‑two‑standby streaming replication setup. The configuration ensures that if one standby fails, the primary still returns a response, preventing service hang.

Conclusion

By systematically addressing the 50+ migration “pits”—from syntax differences to performance bottlenecks—applications can successfully transition from Oracle to PostgreSQL, achieving better stability, scalability, and cost efficiency in financial IT environments.