The Two Oracle-to-Postgres Migration Traps That Never Throw an Error
Migration tools and syntax checkers catch the obvious incompatibilities. These two traps bypass every automated check because the SQL is syntactically valid and executes successfully. A team that trusts tooling alone will ship silently corrupted financial, inventory, or approval logic to production.
Two silent data-corruption traps sit at the top of any Oracle-to-KES migration checklist. The first is outer join elimination: a `WHERE` clause filter on a right-table column causes the optimizer to rewrite an outer join as an inner join, dropping rows that should be preserved. The SQL runs without error, but row counts shrink, and the mismatch only surfaces when financial reports or reconciliations fail post-launch. The fix is pushing the filter into the `ON` clause, but detection requires row-by-row comparison of pre- and post-migration results, not just syntax validation.
The second trap is whether an empty string equals NULL. KES in Oracle compatibility mode replicates Oracle's behavior of treating `''` as NULL, but this is governed by the `ora_input_emptystr_isnull` parameter. If operations later switches to PostgreSQL mode or flips that parameter, all legacy code relying on the Oracle assumption silently breaks. Both traps share a common thread: they produce no error messages, no failed migration report entries, and no warning signs until business logic produces wrong answers.
Automated migration tools create a false sense of safety because they only flag syntax failures, not semantically equivalent queries that produce different results.
The optimizer's outer join elimination is correct by formal semantics but wrong by business intent, exposing a gap that no static analysis tool currently bridges.
Empty-string NULL handling is not a database property but a configuration property, meaning the same KES instance can behave like Oracle or like PostgreSQL depending on a single parameter.
Both traps are most dangerous in finance, inventory, and approval workflows where wrong row counts produce downstream reconciliation failures that are expensive to trace back to a migration change.