Reviewing Oracle To Postgres Migration

Oracle-to-PostgreSQL Database Migration

Surfaces migration risks and validates migration work against known Oracle/PostgreSQL behavioral differences documented in the references/ folder.

When to use

1. Planning — Before starting migration work on a procedure, trigger, query, or refcursor client. Identify which reference insights apply so risks are addressed up front. 2. Validating — After migration work is done, confirm every applicable insight was addressed and integration tests cover the new PostgreSQL semantics.

Workflow

Determine the task type:

Planning a migration? Follow the risk assessment workflow. Validating completed work? Follow the validation workflow.

Risk assessment workflow (planning)

Risk Assessment:
- [ ] Step 1: Identify the migration scope
- [ ] Step 2: Screen each insight for applicability
- [ ] Step 3: Document risks and recommended actions

Step 1: Identify the migration scope

List the affected database objects (procedures, triggers, queries, views) and the application code that calls them.

Step 2: Screen each insight for applicability

Review the reference index in references/REFERENCE.md. For each entry, determine whether the migration scope contains patterns affected by that insight. Read the full reference file only when the insight is potentially relevant.

Step 3: Document risks and recommended actions

For each applicable insight, note the specific risk and the recommended fix pattern from the reference file. Flag any insight that requires a design decision (e.g., whether to preserve Oracle empty-string-as-NULL semantics or adopt PostgreSQL behavior).

Validation workflow (post-migration)

Validation:
- [ ] Step 1: Map the migration artifact
- [ ] Step 2: Cross-check applicable insights
- [ ] Step 3: Verify integration test coverage
- [ ] Step 4: Gate the result

Step 1: Map the migration artifact

Identify the migrated object and summarize the change set.

Step 2: Cross-check applicable insights

For each reference in references/REFERENCE.md, confirm the behavior or test requirement is acknowledged and addressed in the migration work.

Step 3: Verify integration test coverage

Confirm tests exercise both the happy path and the failure scenarios highlighted in applicable insights (exceptions, sorting, refcursor consumption, concurrent transactions, timestamps, etc.).

Step 4: Gate the result

Return a checklist asserting each applicable insight was addressed, migration scripts run, and integration tests pass.

Oracle to PostgreSQL: Empty String Handling Differences

Problem

Oracle automatically converts empty strings ('') to NULL in VARCHAR2 columns. PostgreSQL preserves empty strings as distinct from NULL. This difference can cause application logic errors and test failures during migration.

Behavior Comparison

Oracle:

• Empty string ('') is always treated as NULL in VARCHAR2 columns
• WHERE column = '' never matches rows; use WHERE column IS NULL
• Cannot distinguish between explicit empty string and NULL

PostgreSQL:

• Empty string ('') and NULL are distinct values
• WHERE column = '' matches empty strings
• WHERE column IS NULL matches NULL values

Code Example

-- Oracle behavior
INSERT INTO table (varchar_column) VALUES ('');
SELECT * FROM table WHERE varchar_column IS NULL;  -- Returns the row

-- PostgreSQL behavior  
INSERT INTO table (varchar_column) VALUES ('');
SELECT * FROM table WHERE varchar_column IS NULL;  -- Returns nothing
SELECT * FROM table WHERE varchar_column = '';     -- Returns the row

Migration Actions

1. Stored Procedures

Update logic that assumes empty strings convert to NULL:

-- Preserve Oracle behavior (convert empty to NULL):
column = NULLIF(param, '')

-- Or accept PostgreSQL behavior (preserve empty string):
column = param

2. Application Code

Review code that checks for NULL and ensure it handles empty strings appropriately:

// Before (Oracle-specific)
if (value == null) { }

// After (PostgreSQL-compatible)
if (string.IsNullOrEmpty(value)) { }

3. Tests

Update assertions to be compatible with both behaviors:

// Migration-compatible test pattern
var value = reader.IsDBNull(columnIndex) ? null : reader.GetString(columnIndex);
Assert.IsTrue(string.IsNullOrEmpty(value));

4. Data Migration

Decide whether to:

• Convert existing NULL values to empty strings
• Convert empty strings to NULL using NULLIF(column, '')
• Leave values as-is and update application logic

PostgreSQL Exception Handling: SELECT INTO No Data Found

Overview

A common issue when migrating from Oracle to PostgreSQL involves SELECT INTO statements that expect to raise an exception when no rows are found. This pattern difference can cause integration tests to fail and application logic to behave incorrectly if not properly handled.

---

Problem Description

Scenario

A stored procedure performs a lookup operation using SELECT INTO to retrieve a required value:

SELECT column_name
INTO variable_name
FROM table1, table2 
WHERE table1.id = table2.id AND table1.id = parameter_value;

Oracle Behavior

When a SELECT INTO statement in Oracle does not find any rows, it automatically raises:

ORA-01403: no data found

This exception is caught by the procedure's exception handler and re-raised to the calling application.

PostgreSQL Behavior (Pre-Fix)

When a SELECT INTO statement in PostgreSQL does not find any rows, it:

• Sets the FOUND variable to false
• Silently continues execution without raising an exception

This fundamental difference can cause tests to fail silently and logic errors in production code.

---

Root Cause Analysis

The PostgreSQL version was missing explicit error handling for the NOT FOUND condition after the SELECT INTO statement.

Original Code (Problematic):

SELECT column_name
INTO variable_name
FROM table1, table2 
WHERE table1.id = table2.id AND table1.id = parameter_value;

IF variable_name = 'X' THEN
 result_variable := 1;
ELSE
 result_variable := 2;
END IF;

Problem: No check for NOT FOUND condition. When an invalid parameter is passed, the SELECT returns no rows, FOUND becomes false, and execution continues with an uninitialized variable.

---

Key Differences: Oracle vs PostgreSQL

Add explicit NOT FOUND error handling to match Oracle behavior.

Fixed Code:

SELECT column_name
INTO variable_name
FROM table1, table2 
WHERE table1.id = table2.id AND table1.id = parameter_value;

-- Explicitly raise exception if no data found (matching Oracle behavior)
IF NOT FOUND THEN
    RAISE EXCEPTION 'no data found';
END IF;

IF variable_name = 'X' THEN
 result_variable := 1;
ELSE
 result_variable := 2;
END IF;

---

Migration Notes for Similar Issues

When fixing this issue, verify:

1. Success path tests - Confirm valid parameters still work correctly 2. Exception tests - Verify exceptions are raised with invalid parameters 3. Transaction rollback - Ensure proper cleanup on errors 4. Data integrity - Confirm all fields are populated correctly in success cases

Oracle to PostgreSQL: Parentheses in FROM Clause

Contents

• Problem
• Root Cause
• Solution Pattern
• Examples
• Migration Checklist
• Common Locations
• Application Code Examples
• Error Messages to Watch For
• Testing Recommendations

Problem

Oracle allows optional parentheses around table names in the FROM clause:

-- Oracle: Both are valid
SELECT * FROM (TABLE_NAME) WHERE id = 1;
SELECT * FROM TABLE_NAME WHERE id = 1;

PostgreSQL does not allow extra parentheses around a single table name in the FROM clause without it being a derived table or subquery. Attempting to use this pattern results in:

Npgsql.PostgresException: 42601: syntax error at or near ")"

Root Cause

• Oracle: Treats FROM(TABLE_NAME) as equivalent to FROM TABLE_NAME
• PostgreSQL: Parentheses in the FROM clause are only valid for:
• Subqueries: FROM (SELECT * FROM table)
• Explicit table references that are part of join syntax
• Common Table Expressions (CTEs)
• Without a valid SELECT or join context, PostgreSQL raises a syntax error

Solution Pattern

Remove the unnecessary parentheses around the table name:

-- Oracle (problematic in PostgreSQL)
SELECT col1, col2
FROM (TABLE_NAME)
WHERE id = 1;

-- PostgreSQL (correct)
SELECT col1, col2
FROM TABLE_NAME
WHERE id = 1;

Examples

Example 1: Simple Table Reference

-- Oracle
SELECT employee_id, employee_name
FROM (EMPLOYEES)
WHERE department_id = 10;

-- PostgreSQL (fixed)
SELECT employee_id, employee_name
FROM EMPLOYEES
WHERE department_id = 10;

Example 2: Join with Parentheses

-- Oracle (problematic)
SELECT e.employee_id, d.department_name
FROM (EMPLOYEES) e
JOIN (DEPARTMENTS) d ON e.department_id = d.department_id;

-- PostgreSQL (fixed)
SELECT e.employee_id, d.department_name
FROM EMPLOYEES e
JOIN DEPARTMENTS d ON e.department_id = d.department_id;

Example 3: Valid Subquery Parentheses (Works in Both)

-- Both Oracle and PostgreSQL
SELECT *
FROM (SELECT employee_id, employee_name FROM EMPLOYEES WHERE department_id = 10) sub;

Migration Checklist

When fixing this issue, verify:

1. Identify all problematic FROM clauses:

• Search for FROM ( pattern in SQL
• Verify the opening parenthesis is immediately after FROM followed by a table name
• Confirm it's not a subquery (no SELECT keyword inside)

2. Distinguish valid parentheses:

• ✅ FROM (SELECT ...) - Valid subquery
• ✅ FROM (table_name followed by a join - Check if JOIN keyword follows
• ❌ FROM (TABLE_NAME) - Invalid, remove parentheses

3. Apply the fix:

• Remove the parentheses around the table name
• Keep parentheses for legitimate subqueries

4. Test thoroughly:

• Execute the query in PostgreSQL
• Verify result set matches original Oracle query
• Include in integration tests

Common Locations

Search for FROM ( in:

• ✅ Stored procedures and functions (DDL scripts)
• ✅ Application data access layers (DAL classes)
• ✅ Dynamic SQL builders
• ✅ Reporting queries
• ✅ Views and materialized views
• ✅ Complex queries with multiple joins

Application Code Examples

VB.NET

' Before (Oracle)
StrSQL = "SELECT employee_id, NAME " _
       & "FROM (EMPLOYEES) e " _
       & "WHERE e.department_id = 10"

' After (PostgreSQL)
StrSQL = "SELECT employee_id, NAME " _
       & "FROM EMPLOYEES e " _
       & "WHERE e.department_id = 10"

C #

// Before (Oracle)
var sql = "SELECT id, name FROM (USERS) WHERE status = @status";

// After (PostgreSQL)
var sql = "SELECT id, name FROM USERS WHERE status = @status";

Error Messages to Watch For

Npgsql.PostgresException: 42601: syntax error at or near ")"
ERROR: syntax error at or near ")"
LINE 1: SELECT * FROM (TABLE_NAME) WHERE ...
                      ^

Testing Recommendations

1. Syntax Verification: Parse all migrated queries to ensure they run without syntax errors

   [Fact]
   public void GetEmployees_ExecutesWithoutSyntaxError()
   {
       // Should not throw PostgresException with error code 42601
       var employees = dal.GetEmployees(departmentId: 10);
       Assert.NotEmpty(employees);
   }

2. Result Comparison: Verify that result sets are identical before and after migration 3. Regex-based Search: Use pattern FROM\s*\(\s*[A-Za-z_][A-Za-z0-9_]*\s*\) to identify candidates

Related Files

• Reference: oracle-to-postgres-type-coercion.md - Other syntax differences
• PostgreSQL Documentation: SELECT Statement

Migration Notes

• This is a straightforward syntactic fix with no semantic implications
• No data conversion required
• Safe to apply automated find-and-replace, but manually verify complex queries
• Update integration tests to exercise the migrated queries

Oracle to PostgreSQL Sorting Migration Guide

Purpose: Preserve Oracle-like sorting semantics when moving queries to PostgreSQL.

Key points

• Oracle often treats plain ORDER BY as binary/byte-wise, giving case-insensitive ordering for ASCII.
• PostgreSQL defaults differ; to match Oracle behavior, use COLLATE "C" on sort expressions.

1) Standard SELECT … ORDER BY

Goal: Keep Oracle-style ordering.

Pattern:

SELECT col1
FROM your_table
ORDER BY col1 COLLATE "C";

Notes:

• Apply COLLATE "C" to each sort expression that must mimic Oracle.
• Works with ascending/descending and multi-column sorts, e.g. ORDER BY col1 COLLATE "C", col2 COLLATE "C" DESC.

2) SELECT DISTINCT … ORDER BY

Issue: PostgreSQL enforces that ORDER BY expressions appear in the SELECT list for DISTINCT, raising: Npgsql.PostgresException: 42P10: for SELECT DISTINCT, ORDER BY expressions must appear in select list

Oracle difference: Oracle allowed ordering by expressions not projected when using DISTINCT.

Recommended pattern (wrap and sort):

SELECT *
FROM (
  SELECT DISTINCT col1, col2
  FROM your_table
) AS distinct_results
ORDER BY col2 COLLATE "C";

Why:

• The inner query performs the DISTINCT projection.
• The outer query safely orders the result set and adds COLLATE "C" to align with Oracle sorting.

Tips:

• Ensure any columns used in the outer ORDER BY are included in the inner projection.
• For multi-column sorts, collate each relevant expression: ORDER BY col2 COLLATE "C", col3 COLLATE "C" DESC.

Validation checklist

• [ ] Added COLLATE "C" to every ORDER BY that should follow Oracle sorting rules.
• [ ] For DISTINCT queries, wrapped the projection and sorted in the outer query.
• [ ] Confirmed ordered columns are present in the inner projection.
• [ ] Re-ran tests or representative queries to verify ordering matches Oracle outputs.

Oracle to PostgreSQL: CURRENT_TIMESTAMP and NOW() Timezone Handling

Contents

• Problem
• Behavior Comparison
• PostgreSQL Timezone Precedence
• Common Error Symptoms
• Migration Actions — Npgsql config, DateTime normalization, stored procedures, session timezone, application code
• Integration Test Patterns
• Checklist

Problem

Oracle's CURRENT_TIMESTAMP returns a value in the session timezone and stores it in the column's declared precision. When .NET reads this value back via ODP.NET, it is surfaced as a DateTime with Kind=Local, reflecting the OS timezone of the client.

PostgreSQL's CURRENT_TIMESTAMP and NOW() both return a timestamptz (timestamp with time zone) anchored to UTC, regardless of the session timezone setting. How Npgsql surfaces this value depends on the driver version and configuration:

• Npgsql < 6 / legacy mode (`EnableLegacyTimestampBehavior = true`): timestamptz columns are returned as DateTime with Kind=Unspecified. This is the source of silent timezone bugs when migrating from Oracle.
• Npgsql 6+ with legacy mode disabled (the new default): timestamptz columns are returned as DateTime with Kind=Utc, and writing a Kind=Unspecified value throws an exception at insertion time.

Projects that have not yet upgraded to Npgsql 6+, or that explicitly opt back into legacy mode, remain vulnerable to the Kind=Unspecified issue. This mismatch — and the ease of accidentally re-enabling legacy mode — causes silent data corruption, incorrect comparisons, and off-by-N-hours bugs that are extremely difficult to trace.

---

Behavior Comparison

---

PostgreSQL Timezone Precedence

PostgreSQL resolves the effective session timezone using the following hierarchy (highest priority wins):

The session timezone does not affect the stored UTC value of a timestamptz column — it only controls how SHOW timezone and ::text casts format a value for display. Application code that relies on DateTime.Kind or compares timestamps without an explicit timezone can produce incorrect results if the server's default timezone is not UTC.

---

Common Error Symptoms

• Timestamps read from PostgreSQL have Kind=Unspecified; comparisons with DateTime.UtcNow or DateTime.Now produce incorrect results.
• Date-range queries return too few or too many rows because the WHERE clause comparison is evaluated in a timezone that differs from the stored UTC value.
• Integration tests pass on a developer machine (UTC OS timezone) but fail in CI or production (non-UTC timezone).
• Stored procedure output parameters carrying timestamps arrive with a session-offset applied by the server but are then compared to UTC values in the application.

---

Migration Actions

1. Configure Npgsql for UTC via Connection String or AppContext

Npgsql 6+ ships with EnableLegacyTimestampBehavior set to false by default, which causes timestamptz values to be returned as DateTime with Kind=Utc. Explicitly setting the switch at startup is still recommended to guard against accidental opt-in to legacy mode (e.g., via a config file or a transitive dependency) and to make the intent visible to future maintainers:

// Program.cs / Startup.cs — apply once at application start
AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", false);

With this switch disabled, Npgsql throws if you try to write a DateTime with Kind=Unspecified to a timestamptz column, making timezone bugs loud and detectable at insertion time rather than silently at query time.

2. Normalise DateTime Values Before Persistence

Replace any DateTime.Now with DateTime.UtcNow throughout the migrated codebase. For values that originate from external input (e.g., user-provided dates deserialized from JSON), ensure they are converted to UTC before being saved:

// Before (Oracle-era code — relied on session/OS timezone)
var timestamp = DateTime.Now;

// After (PostgreSQL-compatible)
var timestamp = DateTime.UtcNow;

// For externally-supplied values
var utcTimestamp = dateTimeInput.Kind == DateTimeKind.Utc
    ? dateTimeInput
    : dateTimeInput.ToUniversalTime();

3. Fix Stored Procedures Using CURRENT_TIMESTAMP / NOW()

Stored procedures that assign CURRENT_TIMESTAMP or NOW() to a timestamp without time zone (timestamp) column must be reviewed. Prefer timestamptz columns or cast explicitly:

-- Ambiguous: server timezone influences interpretation
INSERT INTO audit_log (created_at) VALUES (NOW()::timestamp);

-- Safe: always UTC
INSERT INTO audit_log (created_at) VALUES (NOW() AT TIME ZONE 'UTC');

-- Or: use timestamptz column type and let PostgreSQL store UTC natively
INSERT INTO audit_log (created_at) VALUES (CURRENT_TIMESTAMP);

4. Force Session Timezone on Connection Open (Defence-in-Depth)

Regardless of role or database defaults, set the session timezone explicitly when opening a connection. This guarantees consistent behavior independent of server configuration:

// Npgsql connection string approach
var connString = "Host=localhost;Database=mydb;Username=app;Password=...;Timezone=UTC";

// Or: apply via NpgsqlDataSourceBuilder
var dataSource = new NpgsqlDataSourceBuilder(connString)
    .Build();

// Or: execute on every new connection
await using var conn = new NpgsqlConnection(connString);
await conn.OpenAsync();
await using var cmd = new NpgsqlCommand("SET TimeZone = 'UTC'", conn);
await cmd.ExecuteNonQueryAsync();

5. Application Code — Avoid DateTime.Kind=Unspecified

Audit all repository and data-access code that reads timestamp columns. Where Npgsql returns Unspecified, either configure the data source globally (option 1 above) or wrap the read:

// Safe reader helper — convert Unspecified to Utc at the boundary
DateTime ReadUtcDateTime(NpgsqlDataReader reader, int ordinal)
{
    var dt = reader.GetDateTime(ordinal);
    return dt.Kind == DateTimeKind.Unspecified
        ? DateTime.SpecifyKind(dt, DateTimeKind.Utc)
        : dt.ToUniversalTime();
}

---

Integration Test Patterns

Test: Verify timestamps persist and return as UTC

[Fact]
public async Task InsertedTimestamp_ShouldRoundTripAsUtc()
{
    var before = DateTime.UtcNow;

    await repository.InsertAuditEntryAsync(/* ... */);

    var retrieved = await repository.GetLatestAuditEntryAsync();

    Assert.Equal(DateTimeKind.Utc, retrieved.CreatedAt.Kind);
    Assert.True(retrieved.CreatedAt >= before,
        "Persisted CreatedAt should not be earlier than the pre-insert UTC timestamp.");
}

Test: Verify timestamp comparisons across Oracle and PostgreSQL baselines

[Fact]
public async Task TimestampComparison_ShouldReturnSameRowsAsOracle()
{
    var cutoff = DateTime.UtcNow.AddDays(-1);

    var oracleResults = await oracleRepository.GetEntriesAfter(cutoff);
    var postgresResults = await postgresRepository.GetEntriesAfter(cutoff);

    Assert.Equal(oracleResults.Count, postgresResults.Count);
}

---

Checklist

• [ ] AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", false) applied at application startup.
• [ ] All DateTime.Now usages in data-access code replaced with DateTime.UtcNow.
• [ ] Connection string or connection-open hook sets Timezone=UTC / SET TimeZone = 'UTC'.
• [ ] Stored procedures that use CURRENT_TIMESTAMP or NOW() reviewed; timestamp without time zone columns explicitly cast or replaced with timestamptz.
• [ ] Integration tests assert DateTime.Kind == Utc on retrieved timestamp values.
• [ ] Tests cover date-range queries to confirm row counts match Oracle baseline.

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

• oracle-to-postgres-type-coercion.md - Related type conversion issues
• PostgreSQL Documentation: Data Type Formatting Functions

Oracle to PostgreSQL Type Coercion Issues

Contents

• Overview
• The Problem — symptom, root cause, example
• The Solution — string literals, explicit casting
• Common Comparison Operators Affected
• Detection Strategy
• Real-World Example
• Prevention Best Practices

Overview

This document describes a common migration issue encountered when porting SQL code from Oracle to PostgreSQL. The issue stems from fundamental differences in how these databases handle implicit type conversions in comparison operators.

The Problem

Symptom

When migrating SQL queries from Oracle to PostgreSQL, you may encounter the following error:

Npgsql.PostgresException: 42883: operator does not exist: character varying <> integer
POSITION: [line_number]

Root Cause

PostgreSQL has strict type enforcement and does not perform implicit type coercion in comparison operators. Oracle, by contrast, automatically converts operands to compatible types during comparison operations.

Example Mismatch

Oracle SQL (works fine):

AND physical_address.pcountry_cd <> 124

• pcountry_cd is a VARCHAR2
• 124 is an integer literal
• Oracle silently converts 124 to a string for comparison

PostgreSQL (fails):

AND physical_address.pcountry_cd <> 124

42883: operator does not exist: character varying <> integer

• pcountry_cd is a character varying
• 124 is an integer literal
• PostgreSQL rejects the comparison because the types don't match

The Solution

Approach 1: Use String Literals (Recommended)

Convert integer literals to string literals:

AND physical_address.pcountry_cd <> '124'

Pros:

• Semantically correct (country codes are typically stored as strings)
• Most efficient
• Clearest intent

Cons:

• None

Approach 2: Explicit Type Casting

Explicitly cast the integer to a string type:

AND physical_address.pcountry_cd <> CAST(124 AS VARCHAR)

Pros:

• Makes the conversion explicit and visible
• Useful if the value is a parameter or complex expression

Cons:

• Slightly less efficient
• More verbose

Common Comparison Operators Affected

All comparison operators can trigger this issue:

• <> (not equal)
• = (equal)
• < (less than)
• > (greater than)
• <= (less than or equal)
• >= (greater than or equal)

Detection Strategy

When migrating from Oracle to PostgreSQL:

1. Search for numeric literals in WHERE clauses comparing against string/varchar columns 2. Look for patterns like:

• column_name <> 123 (where column is VARCHAR/CHAR)
• column_name = 456 (where column is VARCHAR/CHAR)
• column_name IN (1, 2, 3) (where column is VARCHAR/CHAR)

3. Code review checklist:

• Are all comparison values correctly typed?
• Do string columns always use string literals?
• Are numeric columns always compared against numeric values?

Real-World Example

Original Oracle Query:

SELECT ac040.stakeholder_id,
       ac006.organization_etxt
  FROM ac040_stakeholder ac040
  INNER JOIN ac006_organization ac006 ON ac040.stakeholder_id = ac006.organization_id
 WHERE physical_address.pcountry_cd <> 124
   AND LOWER(ac006.organization_etxt) LIKE '%' || @orgtxt || '%'
 ORDER BY UPPER(ac006.organization_etxt)

Fixed PostgreSQL Query:

SELECT ac040.stakeholder_id,
       ac006.organization_etxt
  FROM ac040_stakeholder ac040
  INNER JOIN ac006_organization ac006 ON ac040.stakeholder_id = ac006.organization_id
 WHERE physical_address.pcountry_cd <> '124'
   AND LOWER(ac006.organization_etxt) LIKE '%' || @orgtxt || '%'
 ORDER BY UPPER(ac006.organization_etxt)

Change: 124 → '124'

Prevention Best Practices

1. Use Type-Consistent Literals:

• For string columns: Always use string literals ('value')
• For numeric columns: Always use numeric literals (123)
• For dates: Always use date literals (DATE '2024-01-01')

2. Leverage Database Tools:

• Use your IDE's SQL linter to catch type mismatches
• Run PostgreSQL syntax validation during code review

3. Test Early:

• Execute migration queries against PostgreSQL before deployment
• Include integration tests that exercise all comparison operators

4. Documentation:

• Document any type coercions in comments
• Mark migrated code with revision history

References

• PostgreSQL Type Casting Documentation
• Oracle Type Conversion Documentation
• Npgsql Exception: Operator Does Not Exist

Related Issues

This issue is part of broader Oracle → PostgreSQL migration challenges:

• Implicit function conversions (e.g., TO_CHAR, TO_DATE)
• String concatenation operator differences (|| works in both, but behavior differs)
• Numeric precision and rounding differences
• NULL handling in comparisons

Oracle to PostgreSQL: Concurrent Transaction Handling

Contents

• Overview
• The Core Difference
• Common Error Symptoms
• Problem Scenarios
• Solutions — materialize results, separate connections, single query
• Detection Strategy
• Error Messages to Watch For
• Comparison Table
• Best Practices
• Migration Checklist

Overview

When migrating from Oracle to PostgreSQL, a critical difference exists in how concurrent operations on a single database connection are handled. Oracle's ODP.NET driver allows multiple active commands and result sets on the same connection simultaneously, while PostgreSQL's Npgsql driver enforces a strict one active command per connection rule. Code that worked seamlessly in Oracle will throw runtime exceptions in PostgreSQL if concurrent operations share a connection.

The Core Difference

Oracle Behavior:

• A single connection can have multiple active commands executing concurrently
• Opening a second DataReader while another is still open is permitted
• Nested or overlapping database calls on the same connection work transparently

PostgreSQL Behavior:

• A connection supports only one active command at a time
• Attempting to execute a second command while a DataReader is open throws an exception
• Lazy-loaded navigation properties or callback-driven reads that trigger additional queries on the same connection will fail

Common Error Symptoms

When migrating Oracle code without accounting for this difference:

System.InvalidOperationException: An operation is already in progress.

Npgsql.NpgsqlOperationInProgressException: A command is already in progress: <SQL text>

These occur when application code attempts to execute a new command on a connection that already has an active DataReader or uncommitted command in flight.

---

Problem Scenarios

Scenario 1: Iterating a DataReader While Executing Another Command

using (var reader = command1.ExecuteReader())
{
    while (reader.Read())
    {
        // PROBLEM: executing a second command on the same connection
        // while the reader is still open
        using (var command2 = new NpgsqlCommand("SELECT ...", connection))
        {
            var value = command2.ExecuteScalar(); // FAILS
        }
    }
}

Scenario 2: Lazy Loading / Deferred Execution in Data Access Layers

// Oracle: works because ODP.NET supports concurrent readers
var items = repository.GetItems(); // returns IEnumerable backed by open DataReader
foreach (var item in items)
{
    // PROBLEM: triggers a second query on the same connection
    var details = repository.GetDetails(item.Id); // FAILS on PostgreSQL
}

Scenario 3: Nested Stored Procedure Calls via Application Code

// Oracle: ODP.NET handles multiple active commands
command1.ExecuteNonQuery(); // starts a long-running operation
command2.ExecuteScalar();   // FAILS on PostgreSQL — command1 still in progress

---

Solutions

Solution 1: Materialize Results Before Issuing New Commands (Recommended)

Close the first result set by loading it into memory before executing subsequent commands on the same connection.

// Load all results into a list first
var items = new List<Item>();
using (var reader = command1.ExecuteReader())
{
    while (reader.Read())
    {
        items.Add(MapItem(reader));
    }
} // reader is closed and disposed here

// Now safe to execute another command on the same connection
foreach (var item in items)
{
    using (var command2 = new NpgsqlCommand("SELECT ...", connection))
    {
        command2.Parameters.AddWithValue("id", item.Id);
        var value = command2.ExecuteScalar(); // Works
    }
}

For LINQ / EF Core scenarios, force materialization with .ToList():

// Before (fails on PostgreSQL — deferred execution keeps connection busy)
var items = dbContext.Items.Where(i => i.Active);
foreach (var item in items)
{
    var details = dbContext.Details.FirstOrDefault(d => d.ItemId == item.Id);
}

// After (materializes first query before issuing second)
var items = dbContext.Items.Where(i => i.Active).ToList();
foreach (var item in items)
{
    var details = dbContext.Details.FirstOrDefault(d => d.ItemId == item.Id);
}

Solution 2: Use Separate Connections for Concurrent Operations

When operations genuinely need to run concurrently, open a dedicated connection for each.

using (var reader = command1.ExecuteReader())
{
    while (reader.Read())
    {
        // Use a separate connection for the nested query
        using (var connection2 = new NpgsqlConnection(connectionString))
        {
            connection2.Open();
            using (var command2 = new NpgsqlCommand("SELECT ...", connection2))
            {
                var value = command2.ExecuteScalar(); // Works — different connection
            }
        }
    }
}

Solution 3: Restructure to a Single Query

Where possible, combine nested lookups into a single query using JOINs or subqueries to eliminate the need for concurrent commands entirely.

// Before: two sequential queries on the same connection
var order = GetOrder(orderId);          // query 1
var details = GetOrderDetails(orderId); // query 2 (fails if query 1 reader still open)

// After: single query with JOIN
using (var command = new NpgsqlCommand(
    "SELECT o.*, d.* FROM orders o JOIN order_details d ON o.id = d.order_id WHERE o.id = @id",
    connection))
{
    command.Parameters.AddWithValue("id", orderId);
    using (var reader = command.ExecuteReader())
    {
        // Process combined result set
    }
}

---

Detection Strategy

Code Review Checklist

• [ ] Search for methods that open a DataReader and call other database methods before closing it
• [ ] Look for IEnumerable return types from data access methods that defer execution (indicate open readers)
• [ ] Identify EF Core queries without .ToList() / .ToArray() that are iterated while issuing further queries
• [ ] Check for nested stored procedure calls in application code that share a connection

Common Locations to Search

• Data access layers and repository classes
• Service methods that orchestrate multiple repository calls
• Code paths that iterate query results and perform lookups per row
• Event handlers or callbacks triggered during data iteration

Search Patterns

ExecuteReader\(.*\)[\s\S]*?Execute(Scalar|NonQuery|Reader)\(

\.Where\(.*\)[\s\S]*?foreach[\s\S]*?dbContext\.

---

Error Messages to Watch For

---

Comparison Table: Oracle vs. PostgreSQL

---

Best Practices

1. Materialize early — Call .ToList() or .ToArray() on query results before iterating and issuing further database calls. This is the simplest and most reliable fix.

2. Audit data access patterns — Review all repository and data access methods for deferred-execution return types (IEnumerable, IQueryable) that callers iterate while issuing additional queries.

3. Prefer single queries — Where feasible, combine nested lookups into JOINs or subqueries to eliminate the concurrent-command pattern entirely.

4. Isolate connections when necessary — If concurrent operations are genuinely required, use separate connections rather than attempting to share one.

5. Test iterative workflows — Integration tests should cover scenarios where code iterates result sets and performs additional database operations per row, as these are the most common failure points.

Migration Checklist

• [ ] Identify all code paths that execute multiple commands on a single connection concurrently
• [ ] Locate IEnumerable-backed data access methods that defer execution with open readers
• [ ] Add .ToList() / .ToArray() materialization where deferred results are iterated alongside further queries
• [ ] Refactor nested database calls to use separate connections or combined queries where appropriate
• [ ] Verify EF Core navigation properties and lazy loading do not trigger concurrent connection usage
• [ ] Update integration tests to cover iterative data access patterns
• [ ] Load-test connection pool sizing if Solution 2 (separate connections) is used extensively

References

• Npgsql Documentation: Basic Usage
• PostgreSQL Documentation: Concurrency Control
• Npgsql GitHub: Multiple Active Result Sets Discussion

Oracle to PostgreSQL: Refcursor Handling in Client Applications

The Core Difference

Oracle's driver automatically unwraps SYS_REFCURSOR output parameters, exposing the result set directly in the data reader. PostgreSQL's Npgsql driver instead returns a cursor name (e.g., "<unnamed portal 1>"). The client must issue a separate FETCH ALL FROM "<cursor_name>" command to retrieve actual rows.

Failing to account for this causes:

System.IndexOutOfRangeException: Field not found in row: <column_name>

The reader contains only the cursor-name parameter — not the expected result columns.

Transaction requirement: PostgreSQL refcursors are scoped to a transaction. Both the procedure call and the FETCH must execute within the same explicit transaction, or the cursor may be closed before the fetch completes under autocommit.

Solution: Explicit Refcursor Unwrapping (C#)

public IEnumerable<User> GetUsers(int departmentId)
{
    var users = new List<User>();
    using var connection = new NpgsqlConnection(connectionString);
    connection.Open();

    // Refcursors are transaction-scoped — wrap both the call and FETCH in one transaction.
    using var tx = connection.BeginTransaction();

    using var command = new NpgsqlCommand("get_users", connection, tx)
    {
        CommandType = CommandType.StoredProcedure
    };
    command.Parameters.AddWithValue("p_department_id", departmentId);
    var refcursorParam = new NpgsqlParameter("cur_result", NpgsqlDbType.Refcursor)
    {
        Direction = ParameterDirection.Output
    };
    command.Parameters.Add(refcursorParam);

    // Execute the procedure to open the cursor.
    command.ExecuteNonQuery();

    // Retrieve the cursor name, then fetch the actual data.
    string cursorName = (string)refcursorParam.Value;
    using var fetchCommand = new NpgsqlCommand($"FETCH ALL FROM \"{cursorName}\"", connection, tx);
    using var reader = fetchCommand.ExecuteReader();
    while (reader.Read())
    {
        users.Add(new User
        {
            UserId   = reader.GetInt32(reader.GetOrdinal("user_id")),
            UserName = reader.GetString(reader.GetOrdinal("user_name")),
            Email    = reader.GetString(reader.GetOrdinal("email"))
        });
    }

    tx.Commit();
    return users;
}

Reusable Helper

Returning a live NpgsqlDataReader from a helper leaves the underlying NpgsqlCommand undisposed and creates ambiguous ownership. Prefer materializing results inside the helper instead:

public static class PostgresHelpers
{
    public static List<T> ExecuteRefcursorProcedure<T>(
        NpgsqlConnection connection,
        NpgsqlTransaction transaction,
        string procedureName,
        Dictionary<string, object> parameters,
        string refcursorParameterName,
        Func<NpgsqlDataReader, T> map)
    {
        using var command = new NpgsqlCommand(procedureName, connection, transaction)
        {
            CommandType = CommandType.StoredProcedure
        };
        foreach (var (key, value) in parameters)
            command.Parameters.AddWithValue(key, value);

        var refcursorParam = new NpgsqlParameter(refcursorParameterName, NpgsqlDbType.Refcursor)
        {
            Direction = ParameterDirection.Output
        };
        command.Parameters.Add(refcursorParam);
        command.ExecuteNonQuery();

        string cursorName = (string)refcursorParam.Value;
        if (string.IsNullOrEmpty(cursorName))
            return new List<T>();

        // fetchCommand is disposed here; results are fully materialized before returning.
        using var fetchCommand = new NpgsqlCommand($"FETCH ALL FROM \"{cursorName}\"", connection, transaction);
        using var reader = fetchCommand.ExecuteReader();

        var results = new List<T>();
        while (reader.Read())
            results.Add(map(reader));
        return results;
    }
}

// Usage:
using var connection = new NpgsqlConnection(connectionString);
connection.Open();
using var tx = connection.BeginTransaction();

var users = PostgresHelpers.ExecuteRefcursorProcedure(
    connection, tx,
    "get_users",
    new Dictionary<string, object> { { "p_department_id", departmentId } },
    "cur_result",
    r => new User
    {
        UserId   = r.GetInt32(r.GetOrdinal("user_id")),
        UserName = r.GetString(r.GetOrdinal("user_name")),
        Email    = r.GetString(r.GetOrdinal("email"))
    });

tx.Commit();

Oracle vs. PostgreSQL Summary

Migration Checklist

• [ ] Identify all procedures returning SYS_REFCURSOR (Oracle) / refcursor (PostgreSQL)
• [ ] Replace ExecuteReader() with ExecuteNonQuery() → cursor name → FETCH ALL FROM
• [ ] Wrap each call-and-fetch pair in an explicit transaction
• [ ] Ensure commands and readers are disposed (prefer materializing results inside a helper)
• [ ] Update unit and integration tests

References

• PostgreSQL Documentation: Cursors
• PostgreSQL FETCH Command
• Npgsql Refcursor Support

Reference Index