Oracle to PostgreSQL Converter: Syntax Guide & Examples

Why Convert Oracle to PostgreSQL?

Oracle's licensing costs are among the highest in the database industry, often running six or seven figures per year for enterprise deployments. PostgreSQL offers comparable features (JSONB, partitioning, parallel queries, logical replication, CTEs, window functions) with zero licensing cost. This is the primary driver for Oracle-to-PostgreSQL migrations.

PostgreSQL is also more cloud-friendly, with managed services on every major cloud provider (AWS Aurora PostgreSQL, Google Cloud SQL, Azure Database for PostgreSQL). Organizations moving to the cloud often use this transition as an opportunity to migrate away from Oracle.

PostgreSQL's PL/pgSQL is deliberately similar to Oracle's PL/SQL, making stored procedure migration less painful than you might expect. However, there are significant differences in built-in functions, data types, and SQL syntax that this guide covers in detail.

Key Syntax Differences

Feature	Oracle	PostgreSQL
NULL replacement	NVL(a, b)	COALESCE(a, b)
Current date/time	SYSDATE	NOW() / CURRENT_TIMESTAMP
Row limiting	WHERE ROWNUM <= 10	LIMIT 10
String concatenation	a || b	a || b (same)
Sequence next value	seq_name.NEXTVAL	nextval('seq_name')
Dual table	SELECT 1 FROM DUAL	SELECT 1
Empty string vs NULL	'' = NULL (same in Oracle)	'' != NULL (different)
Decode function	DECODE(col, v1, r1, def)	CASE WHEN col = v1 THEN r1 ELSE def END
Date arithmetic	SYSDATE + 30 (days)	NOW() + INTERVAL '30 days'
Outer join syntax	(+) operator	Standard LEFT JOIN / RIGHT JOIN

Common Conversions with Examples

NVL to COALESCE

-- Oracle
SELECT NVL(nickname, name) AS display_name FROM users;
SELECT NVL2(commission, salary + commission, salary) AS total FROM employees;

-- PostgreSQL
SELECT COALESCE(nickname, name) AS display_name FROM users;
SELECT CASE WHEN commission IS NOT NULL THEN salary + commission ELSE salary END AS total FROM employees;

SYSDATE to NOW()

-- Oracle
SELECT SYSDATE FROM DUAL;
SELECT * FROM orders WHERE order_date > SYSDATE - 30;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM DUAL;

-- PostgreSQL
SELECT NOW();
SELECT * FROM orders WHERE order_date > NOW() - INTERVAL '30 days';
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD');

ROWNUM to LIMIT

-- Oracle (classic)
SELECT * FROM employees WHERE ROWNUM <= 10;

-- Oracle (12c+ standard)
SELECT * FROM employees
FETCH FIRST 10 ROWS ONLY;

-- PostgreSQL
SELECT * FROM employees LIMIT 10;

-- PostgreSQL also supports the SQL standard syntax
SELECT * FROM employees
FETCH FIRST 10 ROWS ONLY;

Sequences

-- Oracle
CREATE SEQUENCE user_seq START WITH 1 INCREMENT BY 1;
INSERT INTO users (id, name) VALUES (user_seq.NEXTVAL, 'Alice');
SELECT user_seq.CURRVAL FROM DUAL;

-- PostgreSQL
CREATE SEQUENCE user_seq START WITH 1 INCREMENT BY 1;
INSERT INTO users (id, name) VALUES (nextval('user_seq'), 'Alice');
SELECT currval('user_seq');

Or use SERIAL / IDENTITY to avoid managing sequences manually:

-- PostgreSQL (preferred for auto-increment)
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

DECODE to CASE

-- Oracle
SELECT DECODE(status, 'A', 'Active', 'I', 'Inactive', 'Unknown') FROM users;

-- PostgreSQL
SELECT CASE status
    WHEN 'A' THEN 'Active'
    WHEN 'I' THEN 'Inactive'
    ELSE 'Unknown'
END FROM users;

Date Formatting

-- Oracle
SELECT TO_DATE('2026-03-11', 'YYYY-MM-DD') FROM DUAL;
SELECT TO_CHAR(created_at, 'DD-MON-YYYY') FROM orders;

-- PostgreSQL
SELECT '2026-03-11'::DATE;
SELECT TO_CHAR(created_at, 'DD-Mon-YYYY') FROM orders;

PostgreSQL's TO_CHAR is very similar to Oracle's, but format masks are case-sensitive in PostgreSQL (Mon vs MON).

Data Type Mapping

Oracle Type	PostgreSQL Type	Notes
NUMBER(10)	INTEGER or BIGINT	Based on precision
NUMBER(10,2)	NUMERIC(10,2)	Same precision/scale
NUMBER (no precision)	NUMERIC	Arbitrary precision
VARCHAR2(n)	VARCHAR(n)	Drop the "2"
CHAR(n)	CHAR(n)	Same
CLOB	TEXT	Unlimited length in PostgreSQL
BLOB	BYTEA	Binary data
DATE	TIMESTAMP	Oracle DATE includes time
TIMESTAMP	TIMESTAMP	Same
RAW(n)	BYTEA	Variable-length binary
BINARY_FLOAT	REAL	4-byte float
BINARY_DOUBLE	DOUBLE PRECISION	8-byte float
XMLTYPE	XML	PostgreSQL has native XML

Step-by-Step Migration Guide

1. Use Ora2Pg. The open-source Ora2Pg tool automates much of the Oracle-to-PostgreSQL migration, including schema conversion, data transfer, and PL/SQL to PL/pgSQL translation.
2. Convert the schema. Replace NUMBER with appropriate PostgreSQL numeric types, VARCHAR2 with VARCHAR, CLOB with TEXT, and Oracle DATE (which includes time) with TIMESTAMP.
3. Handle Oracle-specific SQL. Replace NVL with COALESCE, DECODE with CASE, SYSDATE with NOW(), ROWNUM with LIMIT, (+) outer joins with standard LEFT/RIGHT JOIN, and remove FROM DUAL.
4. Convert PL/SQL to PL/pgSQL. Change DBMS_OUTPUT.PUT_LINE to RAISE NOTICE, %ROWTYPE works in both, replace Oracle packages with PostgreSQL schemas, and update exception handling syntax.
5. Beware of empty strings. Oracle treats empty string ('') as NULL. PostgreSQL treats them as distinct values. This is the most subtle migration issue. Audit your code for any logic that depends on Oracle's empty-string-is-NULL behavior.
6. Test with production-like data. Load a full copy of your Oracle data into PostgreSQL and run regression tests. Pay attention to date arithmetic, NULL handling, and character set differences.

For SQL fundamentals, see our What is SQL guide. For join syntax that works across databases, check SQL Joins Explained.

Convert Automatically with AI2SQL

Paste your Oracle query into the SQL Converter tool and get correct PostgreSQL syntax with COALESCE, NOW(), LIMIT, and proper data types. No need to remember the Oracle-to-PostgreSQL differences.

Try the converter free. See also: Oracle to SQL Server and MySQL to PostgreSQL. Browse all converter tools.

Frequently Asked Questions

What is the PostgreSQL equivalent of Oracle's NVL?

Use COALESCE in PostgreSQL. Oracle: NVL(column, 'default'). PostgreSQL: COALESCE(column, 'default'). COALESCE is SQL standard and accepts multiple arguments, returning the first non-NULL value. Oracle also supports COALESCE, so switching to it before migration makes the code portable.

How do I convert Oracle ROWNUM to PostgreSQL?

Replace ROWNUM with LIMIT. Oracle: SELECT * FROM employees WHERE ROWNUM <= 10. PostgreSQL: SELECT * FROM employees LIMIT 10. For more complex row numbering, use the ROW_NUMBER() window function which both databases support.

Can I migrate Oracle PL/SQL procedures to PostgreSQL?

Yes, but it requires rewriting. PostgreSQL uses PL/pgSQL which is similar to PL/SQL but has differences. Key changes: Oracle's packages become PostgreSQL schemas or extensions, %TYPE works in both, EXCEPTION blocks are similar but error codes differ, and DBMS_OUTPUT.PUT_LINE becomes RAISE NOTICE. Tools like Ora2Pg can automate much of the conversion.