SQLMentor
PostgreSQL vs Oracle: Key SQL Differences to Know
PostgreSQL and Oracle share the same relational core, but licensing, procedural code, identifier case-folding, and even how NULL behaves in string concatenation all differ enough to catch people off guard when moving between them. Here's what actually changes.
Same relational core, different everywhere else
Both PostgreSQL and Oracle Database are mature, standards-compliant relational databases — the SELECT/JOIN/GROUP BY core you already know transfers almost unchanged. The differences show up in licensing, procedural code, data types, and a handful of behavioural quirks that catch people migrating between the two off guard.
Quick comparison
| PostgreSQL | Oracle | |
|---|---|---|
| Licence & cost | free, open source (PostgreSQL Licence) | commercial; free Express Edition (XE) available with limits |
| Procedural language | PL/pgSQL | PL/SQL |
| Default identifier case | lowercase (unquoted identifiers folded down) | UPPERCASE (unquoted identifiers folded up) |
Concatenating with NULL (x || 'text') | returns NULL if x is NULL | returns 'text' — Oracle treats NULL as an empty string in concatenation |
| Row-limiting clause | LIMIT / OFFSET (always available) | FETCH FIRST / OFFSET (12c+) or the older ROWNUM pattern |
| Native JSON support | json and jsonb types, rich operators | JSON type and functions since 12c, less native than jsonb |
| Autoincrement columns | SERIAL / IDENTITY (backed by a sequence) | sequence + trigger, or IDENTITY columns (12c+) |
| MVCC old-version storage | old row versions kept in the table heap; VACUUM reclaims space | old versions kept in separate undo segments |
| Physical row identifier | ctid (not stable across VACUUM FULL) | ROWID (a Oracle-managed pseudocolumn) |
The identifier case-folding trap
This one causes more "table not found" confusion than anything else on this list. Both databases fold unquoted identifiers to a default case — but in opposite directions: PostgreSQL folds to lowercase, Oracle folds to UPPERCASE.
So CREATE TABLE Employees (...) silently becomes a table named employees in PostgreSQL, but EMPLOYEES in Oracle. If you later reference "Employees" with quotes and mixed case, neither database will find it — because a quoted identifier is taken literally, and neither actual table name matches that exact casing.
The NULL-concatenation difference, demonstrated
The same expression, different results — a genuinely important behavioural gap when porting code between the two.
-- Oracle: NULL is treated as an empty string in concatenation
SELECT 'Hello ' || NULL || 'World' FROM dual; -- 'Hello World'
-- PostgreSQL: NULL poisons the whole expression, standard SQL behaviour
SELECT 'Hello ' || NULL || 'World'; -- NULLPL/SQL vs PL/pgSQL
Both languages share the same DNA — block structure (DECLARE / BEGIN / EXCEPTION / END), similar cursor and exception-handling concepts — because PL/pgSQL was explicitly designed to feel familiar to PL/SQL developers. But the syntax isn't interchangeable: procedure/function declaration syntax, built-in package names (Oracle's DBMS_OUTPUT vs Postgres's RAISE NOTICE), and many built-in functions differ enough that code needs a real rewrite, not a find-and-replace, when porting between them.
Pagination: always simple in Postgres, version-dependent in Oracle
PostgreSQL has had LIMIT / OFFSET since its earliest versions — no special cases. Oracle only gained the equivalent standard-SQL FETCH FIRST n ROWS ONLY clause in 12c (2013); older Oracle code still relies on the less intuitive ROWNUM pseudocolumn, which has its own gotcha — it's assigned before ORDER BY runs unless you wrap the query in a subquery.