Read time: ~6 minutes

A real-world deep dive into operator precedence, implicit casting, and why database engines “don’t think the same way”.

The Database Migration Mystery That Started It All

You migrate a perfectly stable Oracle application to PostgreSQL.

• The SQL runs
• The tests pass
• The syntax looks correct
• Nothing crashes

And yet… the numbers or query calculations are wrong.

Not obviously wrong. Not broken. Just different.
Those are the worst bugs the ones that quietly ship to production. This is a story about one such bug, hiding behind familiar operators, clean-looking conversions, and false confidence.

The Original Business Logic (Oracle)

Here’s a simplified piece of real production logic used to compute a varhour value from timestamp data:

At first glance, this feels routine:

• Extract minutes
• Perform arithmetic
• Concatenate values

Nothing here screams “migration risk”.

The Migration Illusion: “Looks Correct, Right?”

During migration, teams don’t blindly copy Oracle SQL. They do the right thing make types explicit and clean up the logic.

Here’s the PostgreSQL converted version, already “fixed” with necessary casts:

No syntax errors. Explicit casting. Clean and readable. At this point, most migrations move on.

Side-by-Side: Oracle vs PostgreSQL (At First Glance)

Let’s compare the two versions:

Aspect	Oracle	PostgreSQL
Concatenation operator	||	||
Arithmetic operators	+, -	+, -
Minute extraction	TO_CHAR(varmonth,'MI')	TO_CHAR(varmonth,'MI')::integer
Explicit casting	❌ Implicit	✅ Explicit
Query runs successfully	✅	✅
Logic looks identical	✅	✅

Everything appears aligned.
Same operators. Same order. Same intent. So naturally, we expect the same result.

Let’s test with a real value:

Output:

Database	varhour
Oracle	1500
PostgreSQL	14100

Same logic. Same data. Different result. Now the real question appears:

How can two “explicit” queries still behave differently?

What Your Brain Thinks Is Happening

When most of us read this expression:

Our brain assumes:

1. Arithmetic happens first (+, -)
2. Concatenation happens last (||)

That assumption is correct in PostgreSQL. It is not correct in Oracle.

Oracle’s Behavior: “Let Me Help You”

Oracle aggressively applies implicit type conversion. Internally, Oracle rewrites the expression to something closer to:

Concatenation happens before arithmetic.

Step by step:

1. varhr - 1 → 14
2. TO_CHAR(14) → '14'
3. TO_CHAR(varmonth,'MI') → '59'
4. '14' || '59' → '1459'
5. TO_NUMBER('1459') → 1459
6. 1459 + 1 + 40 → 1500

Oracle silently guessed your intent.

PostgreSQL’s Behavior: “Be Explicit”

PostgreSQL does no guessing. It follows strict operator precedence:

1. TO_CHAR(loadmonth,'MI')::integer → 59
2. 59 + 1 + 40 → 100
3. (end_hr - 1)::text → '14'
4. '14' || '100' → 14100

Different grouping. Different result. No error.

Proof: Oracle’s Execution Plan

Oracle doesn’t hide this, it just doesn’t advertise it.

The projection shows:

That TO_NUMBER() wrapping the concatenation is the smoking gun.

Why This Bug Is So Hard to Catch

1. It never throws an error
2. The SQL looks correct
3. Early test data rarely hits edge cases
4. Automated migration tools miss it
5. The behavior difference is undocumented in most migration guides

This is not a syntax problem. It’s a behavioral difference.

The Real Issue Isn’t concat operator(||) or implicit casting

This comes down to philosophy:

Aspect	Oracle	PostgreSQL
Type handling	Implicit type coercion	Explicit casting
Operator behavior	Flexible, context-driven	Strict and deterministic
Operator precedence	May group expressions implicitly	Fixed, well-defined precedence
Developer experience	Convenience-oriented	Precision-oriented
Error tolerance	Tries to “make it work”	Forces you to be explicit
Core philosophy	“Make it work”	“Say what you mean”

Neither is wrong. But assuming they behave the same is dangerous.

The Fix: Make Intent Explicit

Output:

This version:

• Produces identical results
• Documents intent
• Survives migrations
• Prevents silent data corruption

Real-World Impact

I’ve seen this exact pattern cause:

• Financial miscalculations
• Audit timestamp mismatches
• Reconciliation failures weeks after go-live
• “The numbers don’t add up” production escalations

The worst part? These bugs surface after trust is already established.

Key Takeaways

• Execution plans reveal truth, not source code
• || mixed with + is a migration red flag
• Explicit casting doesn’t guarantee identical behavior
• Migration is about semantics, not syntax

The Bottom Line

Database migration isn’t translation. It’s interpretation.

When Oracle silently rewrites logic and PostgreSQL refuses to guess, you must be explicit. And once you start writing SQL that works the same everywhere, you don’t just migrate safely you migrate confidently.

Try it Yourself