Reviewing Oracle To Postgres Migration

Oracle to PostgreSQL: TO_CHAR() Numeric Conversions

Contents

• Problem

• Root Cause

• Solution Patterns — CAST, format string, concatenation

• Migration Checklist

• Application Code Review

• Testing Recommendations

• Common Locations

• Error Messages to Watch For

Problem

Oracle allows TO_CHAR() to convert numeric types to strings without a format specifier:

-- Oracle: Works fine
SELECT TO_CHAR(vessel_id) FROM vessels;
SELECT TO_CHAR(fiscal_year) FROM certificates;

PostgreSQL requires a format string when using TO_CHAR() with numeric types, otherwise it raises:

42883: function to_char(numeric) does not exist

Root Cause

• Oracle: TO_CHAR(number) without a format mask implicitly converts the number to a string using default formatting

• PostgreSQL: TO_CHAR() always requires an explicit format string for numeric types (e.g., '999999', 'FM999999')

Solution Patterns

Pattern 1: Use CAST (Recommended)

The cleanest migration approach is to replace TO_CHAR(numeric_column) with CAST(numeric_column AS TEXT):

-- Oracle
SELECT TO_CHAR(vessel_id) AS vessel_item FROM vessels;

-- PostgreSQL (preferred)
SELECT CAST(vessel_id AS TEXT) AS vessel_item FROM vessels;

Advantages:

• More idiomatic in PostgreSQL

• Clearer intent

• No format string needed

Pattern 2: Provide Format String

If you need specific numeric formatting, use an explicit format mask:

-- PostgreSQL with format
SELECT TO_CHAR(vessel_id, 'FM999999') AS vessel_item FROM vessels;
SELECT TO_CHAR(amount, 'FM999999.00') AS amount_text FROM payments;

Format masks:

• 'FM999999': Fixed-width integer (FM = Fill Mode, removes leading spaces)

• 'FM999999.00': Decimal with 2 places

• '999,999.00': With thousand separators

Pattern 3: String Concatenation

For simple concatenation where numeric conversion is implicit:

-- Oracle
WHERE TO_CHAR(fiscal_year) = '2024'

-- PostgreSQL (using concatenation)
WHERE fiscal_year::TEXT = '2024'
-- or
WHERE CAST(fiscal_year AS TEXT) = '2024'

Migration Checklist

When migrating SQL containing TO_CHAR():

1. Identify all TO_CHAR() calls: Search for TO_CHAR\( in SQL strings, stored procedures, and application queries

2. Check the argument type:

   • DATE/TIMESTAMP: Keep TO_CHAR() with format string (e.g., TO_CHAR(date_col, 'YYYY-MM-DD'))

   • NUMERIC/INTEGER: Replace with CAST(... AS TEXT) or add format string

3. Test the output: Verify that the string representation matches expectations (no unexpected spaces, decimals, etc.)

4. Update comparison logic: If comparing numeric-to-string, ensure consistent types on both sides

Application Code Review

C# Example

// Before (Oracle)
var sql = "SELECT TO_CHAR(id) AS id_text FROM entities WHERE TO_CHAR(status) = @status";

// After (PostgreSQL)
var sql = "SELECT CAST(id AS TEXT) AS id_text FROM entities WHERE CAST(status AS TEXT) = @status";

Testing Recommendations

1. Unit Tests: Verify numeric-to-string conversions return expected values

   Copy

   [Fact]
   public void GetVesselNumbers_ReturnsVesselIdsAsStrings()
   {
       var results = dal.GetVesselNumbers(certificateType);
       Assert.All(results, item => Assert.True(int.TryParse(item.DISPLAY_MEMBER, out _)));
   }

2. Integration Tests: Ensure queries with CAST() execute without errors

3. Comparison Tests: Verify WHERE clauses with numeric-to-string comparisons filter correctly

Common Locations

Search for TO_CHAR in:

• ✅ Stored procedures and functions (DDL scripts)

• ✅ Application data access layers (DAL classes)

• ✅ Dynamic SQL builders

• ✅ Reporting queries

• ✅ ORM/Entity Framework raw SQL

Error Messages to Watch For

Npgsql.PostgresException: 42883: function to_char(numeric) does not exist
Npgsql.PostgresException: 42883: function to_char(integer) does not exist
Npgsql.PostgresException: 42883: function to_char(bigint) does not exist

See Also

• oracle-to-postgres-type-coercion.md - Related type conversion issues

• PostgreSQL Documentation: Data Type Formatting Functions