Oracle has treated '' as NULL since long before PostgreSQL existed. Almost nothing that migrates naively survives that difference intact.
Try this on Oracle:
INSERT INTO customers (name, middle_name) VALUES ('Ana', '');
SELECT COUNT(*) FROM customers WHERE middle_name IS NULL;
-- returns 1
Now run the same two statements on PostgreSQL. The count comes back 0. Nothing is broken — both databases are behaving exactly as designed. They just disagree, fundamentally, about what an empty string is. Oracle has treated '' as NULL since long before PostgreSQL existed as a project. PostgreSQL, correctly by the SQL standard, treats them as two different things: one is the absence of a value, the other is a value that happens to be zero characters long.
Nobody sets out to depend on this. It just happens, quietly, in code that was only ever tested against Oracle.
Where it actually bites: concatenation
The clearest place this surfaces is string concatenation. Oracle's || operator treats a NULL operand as if it were an empty string and skips it:
-- Oracle
SELECT 'Old sal: ' || :old_value FROM dual;
-- :old_value is NULL -> returns 'Old sal: '
PostgreSQL's || does the opposite, and it's the standard-compliant behavior: concatenating anything with NULL returns NULL.
-- PostgreSQL, naive port
SELECT 'Old sal: ' || old_value;
-- old_value is NULL -> returns NULL
We hit this ourselves converting a trigger that logs old and new values on update. Ported literally, a line that should have printed Old sal: printed Old sal: <NULL> instead — technically valid PostgreSQL, semantically wrong, and exactly the kind of thing that passes every test that doesn't happen to touch a NULL column.
The fix, and what it actually preserves
Every concatenation chain pgrecon emits gets wrapped as NULLIF(concat(...), '') instead of a plain || chain. concat() ignores NULL arguments the way Oracle's || does, which covers the common case. NULLIF restores the one case Oracle does return NULL for: every operand empty, because in Oracle an all-empty result of || is itself '', and '' is NULL. After the fix, the same trigger prints Old sal: — matching Oracle's own output exactly, including on the all-null case, which is where a simpler fix like CONCAT_WS still gets it wrong: it returns '', not NULL.
We checked this the same way we check everything else: applied to a live PostgreSQL database and executed, not just inspected. WHERE a || b = c || d on data where both sides can be all-NULL is a real query shape, and it matches the wrong rows if this isn't handled correctly — so we ran it, before the fix and after, and confirmed the after.
One honest gap remains, and we'd rather say so than leave it implied: RAISE NOTICE '%', x prints the literal text <NULL> when the whole wrapped expression is NULL, where Oracle's equivalent prints a blank line. It's cosmetic — output formatting on a debug log line, not a value anywhere a query result depends on — and it's on the list, not swept under it.
Why this matters beyond one operator
|| is just the visible case. The same NULL-versus-empty-string identity quietly changes the meaning of DECODE, of WHERE column = '', of any report that counts "blank" fields. An assessment that doesn't check for this will tell you a migration is riskier or safer than it actually is — not because anyone lied, but because Oracle and PostgreSQL agree on almost everything except this, and "almost everything" is exactly where the surprises hide.
pgrecon's free tier ships this rewrite by default: muzzammil242.github.io/pgrecon has the install command and the full benchmark. If you want a written, line-by-line inventory of where your own schema depends on Oracle's NULL semantics, that's exactly what the fixed-price assessment surfaces — book one at DevCrafter AI.
Top comments (0)