Handling Oracle NULLs and Empty Strings in PostgreSQL

The Nastiest Silent Difference

Of all the Oracle→PostgreSQL gotchas, this is the one that corrupts data quietly: Oracle treats an empty string '' as NULL. PostgreSQL does not. In PostgreSQL, '' is a real, distinct value — an empty string that is emphatically not NULL. Row counts match, the migration reports success, and then application logic that relied on col IS NULL to catch empty Oracle strings starts behaving differently in production. No error, no warning — just wrong answers.

See It For Yourself

-- Oracle: an empty string IS null
INSERT INTO t (note) VALUES ('');
SELECT count(*) FROM t WHERE note IS NULL;   -- 1  (the '' row counts as NULL)

-- PostgreSQL: '' is a distinct, non-null value
INSERT INTO t (note) VALUES ('');
SELECT count(*) FROM t WHERE note IS NULL;   -- 0
SELECT count(*) FROM t WHERE note = '';      -- 1

The same query, the same data, two different answers. That's why (NULL, 'foo') and ('', 'foo') are different rows in PostgreSQL but indistinguishable in Oracle.

Decide the Mapping Per Column

There is no global right answer — it depends on what each column means. Make an explicit choice and apply it during the load:

• Preserve Oracle semantics (most common): coerce empty strings to NULL on the way in, so IS NULL logic keeps working.
• Keep them distinct: only when the application genuinely treats "blank" and "unknown" differently — rare, and usually a redesign smell.

-- Preserve Oracle semantics: empty string -> NULL during load
UPDATE staging.customers SET middle_name = NULL WHERE middle_name = '';

-- Or enforce it going forward with a constraint / trigger
ALTER TABLE customers
  ADD CONSTRAINT middle_name_not_empty CHECK (middle_name <> '');

-- NULLIF is handy in the migration SELECT itself
SELECT id, NULLIF(middle_name, '') AS middle_name FROM src.customers;

This Is Exactly What Validation Must Catch

Row counts are blind to this — both databases report the same number of rows whether the value is NULL or ''. Only column-level validation catches the divergence: a checksum or per-partition Merkle fingerprint over the actual column values will flag a column where Oracle's NULLs landed as empty strings (or vice versa). If your validation is count-only, this bug ships.

Related Traps

• Concatenation. Oracle || treats NULL as empty ('a' || NULL = 'a'); PostgreSQL || with a NULL yields NULL unless you COALESCE. Watch generated columns and keys built by concatenation.
• NOT NULL columns. An Oracle column that was "never empty" via ''=NULL may now accept '' in PG — add a CHECK (col <> '') if blank should be rejected.

How DBMigrateAIPro Handles It

The tool flags every nullable text column during assessment, defaults to preserving Oracle semantics (empty string → NULL) on load, and — critically — validates at the column level with Merkle fingerprints, so a column where the NULL/empty mapping went wrong shows up as a DATA MISMATCH with the exact rows, not a silent success. The trap that count-only tools miss is the one the tool is built to catch.