Oracle to AlloyDB | Cloud SQL (PostgreSQL) migration series: A developer’s guide (Part 2)

Google Cloud Database
, ,
1

Mind the gotchas: 5 critical differences that can break your Oracle to AlloyDB | CloudSQL (PostgreSQL) migration

A developer’s guide to the small-but-critical differences between Oracle and Postgres that every migration team needs to know.

In any major database migration, it’s often not the big, obvious architectural changes that cause the most frustration. It’s the small, subtle behavioral differences — the “gotchas” that introduce silent bugs and hours of debugging. When moving from Oracle to a PostgreSQL-compatible database like AlloyDB, Cloud SQL for PostgreSQL, or a self-managed Postgres, a few of these paper-cut issues show up again and again.

Understanding them ahead of time can save your project from costly delays. Here are the top 5 critical differences to watch out for.

1. Empty string ('') vs. NULL

This is arguably the most famous difference between the two databases.

  • In Oracle: An empty string ('') is treated as functionally identical to NULL.
  • In PostgreSQL: An empty string is a value (a zero-length string), while NULL represents the absence of a value. They are completely distinct.

Why it breaks things: Code migrated from Oracle might use my_column = '' to check for what it considers a “null” value. In PostgreSQL, this check will fail to find actual NULLs, leading to flawed logic and incorrect results.

The fix: Be disciplined. Use the IS NULL operator for null checks and, when dealing with string variables that could be empty, use the NULLIF() function to standardize the behavior.

-- Don't do this (Oracle-style thinking)
-- WHERE my_string_variable = ''

-- Do this in Postgres
-- Explicitly check for both conditions
WHERE my_string_variable = '' OR my_string_variable IS NULL;-- Or, even better, standardize the input
-- This treats an empty string as NULL, mimicking Oracle's behavior
WHERE NULLIF(my_string_variable, '') IS NULL;

2. Oracle DATE vs. PostgreSQL TIMESTAMP

This gotcha leads to silent data loss if you’re not careful.

  • In Oracle: The DATE data type stores both the date and time components (down to the second).
  • In PostgreSQL: The DATE data type stores only the date. The direct equivalent to Oracle’s DATE is PostgreSQL’s TIMESTAMP (or TIMESTAMP WITHOUT TIME ZONE).

Why it breaks things: If you map an Oracle DATE column to a PostgreSQL DATE column, the time component of every single record will be silently truncated during the migration. Your application might appear to work, but all time-sensitive data will be lost.

The Fix: Always map Oracle DATE columns to TIMESTAMP in PostgreSQL. Be vigilant during the schema conversion phase to ensure this mapping is correct. Likewise, replace calls to Oracle’s SYSDATE with LOCALTIMESTAMP or NOW() in PostgreSQL.

3. INSERT with a Row-Type Variable

A common and convenient feature in Oracle PL/SQL is the %ROWTYPE attribute. It allows you to declare a record variable that dynamically mirrors the exact structure of a table row. This is incredibly useful for grouping related data and making your code more resilient to table changes (like adding a new column).

  • In Oracle PL/SQL: You can declare a variable using %ROWTYPE and then insert that entire record variable directly into the table with a clean, simple statement.
  • In PostgreSQL PL/pgSQL: While Postgres has a similar concept for creating record variables, you must explicitly “dereference” the variable using .* to expand it into its constituent columns during an INSERT.

Why it breaks things: Migrated code that tries to insert the record variable directly will simply fail to compile until the syntax is corrected.

The fix: This is a straightforward mechanical change. Find all INSERT statements using a row-type variable and add .* to the variable name.

-- The Oracle PL/SQL Approach
DECLARE
new_employee_rec employees%ROWTYPE;
BEGIN
new_employee_rec.id := 101;
-- ... populate other fields ...
INSERT INTO employees VALUES new_employee_rec;
END;

-- The PostgreSQL PL/pgSQL Fix
DECLARE
new_employee_rec employees;
BEGIN
new_employee_rec.id := 101;
-- ... populate other fields ...
-- The fix is adding ".*" to expand the record
INSERT INTO employees VALUES (new_employee_rec.*);
END;

4. The DECODE function and NULLs

The DECODE function in Oracle has a handy feature: it can compare a value to NULL directly. When this is converted to a standard CASE statement in PostgreSQL, that logic breaks.

Why it breaks things: A CASE statement uses standard equality (=), and in SQL, NULL = NULL never evaluates to true.

-- This Oracle DECODE works as expected if my_column is NULL
-- DECODE(my_column, NULL, 'Is Null', 'Not Null')

-- A naive conversion to a simple CASE statement will fail
CASE my_column
WHEN NULL THEN 'Is Null' -- This clause will NEVER be hit
ELSE 'Not Null'
END

The fix: Convert DECODE to a “searched” CASE statement, which allows you to use the IS NULL operator.

-- The correct PostgreSQL equivalent
CASE
WHEN my_column IS NULL THEN 'Is Null'
ELSE 'Not Null'
END

5. NVL() vs. COALESCE()

This is a simple function name change, but it’s one of the most common replacements you’ll make.

  • In Oracle: NVL(value, default_if_null) replaces a null value with a default.
  • In PostgreSQL: The ANSI-standard COALESCE(value1, value2, ...) function serves the same purpose.

Why it breaks things: NVL() does not exist in PostgreSQL, so all calls must be converted.

The fix: Replace NVL(a, b) with COALESCE(a, b). A key advantage is that COALESCE can take multiple arguments and will return the first non-null value it finds, making it more powerful and cleaner for chained fallbacks.

-- Oracle NVL requires nesting for multiple fallbacks
-- SELECT NVL(manager_name, NVL(regional_supervisor, 'No Manager Assigned')) FROM employees;

-- PostgreSQL COALESCE is cleaner and more efficient
SELECT COALESCE(manager_name, regional_supervisor, 'No Manager Assigned') FROM employees;

The takeaway

While architectural changes get the most attention, mastering these five “gotchas” is essential for a smooth and efficient code conversion. By addressing them proactively, your migration to AlloyDB, Cloud SQL, or any PostgreSQL database will be faster, less error-prone, and far more successful.

Next up in our series: “Implementing Autonomous Transactions in AlloyDB | Cloud SQL (PostgreSQL) using PLProxy”