Oracle to PostgreSQL DDL
Convert common Oracle DDL and expressions to PostgreSQL. Covers types (NUMBER, VARCHAR2, DATE, CLOB), sequences, SYSDATE, NVL, MINUS. Flags non-trivial cases.
About this tool
Oracle and PostgreSQL share most SQL, but the data types, sequence syntax, and some built-in functions differ enough that a straight copy-paste of an Oracle DDL rarely runs against PostgreSQL without edits. This tool handles the predictable cases — types, defaults, simple expressions — and flags everything that needs a human.
Type mapping follows common convention: VARCHAR2 → VARCHAR,
NUMBER(p,s) → NUMERIC(p,s), CLOB → TEXT,
BLOB → BYTEA. DATE → TIMESTAMP because
Oracle’s DATE includes time; PostgreSQL’s DATE does not.
NUMBER(p) maps to SMALLINT,
INTEGER, BIGINT, or NUMERIC(p)
by size; the NUMBER(10) → INTEGER case carries a
warning because Oracle’s NUMBER(10) range exceeds 32-bit INTEGER.
Expressions: SYSDATE and SYSTIMESTAMP
become CURRENT_TIMESTAMP. NVL becomes
COALESCE. MINUS becomes
EXCEPT. seq.NEXTVAL becomes
nextval('seq'). FROM DUAL is dropped.
ROWNUM, CONNECT BY, and
DECODE() are flagged but not auto-rewritten.
For anything procedural — triggers, stored procedures, packages — reach for ora2pg. It’s the reference tool for whole-schema migrations and knows about dependencies this DDL translator intentionally doesn’t touch.
Frequently asked questions
Will this convert PL/SQL procedures?
No — PL/SQL procedural code must be rewritten as PL/pgSQL. The conversion is non-trivial (different cursor syntax, different exception names, no packages, NUMBER vs NUMERIC arithmetic). This tool flags CREATE PROCEDURE / FUNCTION / PACKAGE / TRIGGER statements but does not attempt to rewrite them.
Is NUMBER(10) really equivalent to INTEGER?
Not exactly. NUMBER(10) can hold 10 digits (up to ~9.9 billion), which overflows PostgreSQL INTEGER (32-bit signed, max ~2.1 billion). The tool maps NUMBER(10) to INTEGER per common convention but emits a warning — consider BIGINT if values may exceed 2^31.
What about DATE?
Oracle DATE stores both date and time; PostgreSQL DATE stores only a date. The tool maps Oracle DATE to PostgreSQL TIMESTAMP to preserve the time component. If you genuinely only store dates in the column, change it manually to DATE.
Are sequences converted?
Mostly. NOCACHE, NOORDER, NOCYCLE, NOMAXVALUE, NOMINVALUE are remapped to PostgreSQL-compatible equivalents. seq.NEXTVAL becomes nextval('seq'). The one thing PostgreSQL has no equivalent for is ORDER — it’s dropped with a warning.
How do I convert a whole schema at once?
For bulk conversion use ora2pg, a mature Perl tool that handles triggers, packages, types, and dependencies. This tool is for spot translations — a single CREATE TABLE you’re rewriting by hand, or a stored query you need to rerun against PostgreSQL.