Skip to content

oracle-to-postgres-to-char-numeric.md

Latest commit

 

History

History
145 lines (102 loc) · 4.19 KB

oracle-to-postgres-to-char-numeric.md

File metadata and controls

145 lines (102 loc) · 4.19 KB

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

    [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