Migrate Oracle to PostgreSQL
Certified — live-tested pathThe converter maps 15 Oracle column types to PostgreSQL. One of them changes kind on the way (DATE becomes TIMESTAMP); the table below lists every one. DBShifts converts the schema, copies the rows in parallel batches and checks the result with row counts and checksums.
Free tier · no credit card
Oracle vs PostgreSQL
Oracle
Oracle Database, the long-standing enterprise standard.
PostgreSQL
the most advanced open-source relational database.
Oracle to PostgreSQL data type mapping
Pulled directly from DBShifts's own conversion rules — not a general reference table, this is what actually runs.
| Oracle | PostgreSQL |
|---|---|
| NUMBER(10) | BIGINT |
| NUMBER(19) | NUMERIC(19,0) |
| NUMBER(10,2) | NUMERIC(10,2) |
| VARCHAR2(255) | VARCHAR(255) |
| NVARCHAR2(255) | VARCHAR(255) |
| CHAR(10) | CHAR(10) |
| CLOB | TEXT |
| NCLOB | TEXT |
| BLOB | BYTEA |
| DATE | TIMESTAMP |
| TIMESTAMP | TIMESTAMP |
| BINARY_FLOAT | REAL |
| BINARY_DOUBLE | DOUBLE PRECISION |
| RAW | BYTEA |
| LONG | TEXT |
Example: Oracle to PostgreSQL schema conversion
Oracle
CREATE TABLE orders ( id NUMBER(10) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, customer_id NUMBER(10) NOT NULL, total NUMBER(10,2) NOT NULL, notes CLOB, reference VARCHAR2(255), placed_at TIMESTAMP NOT NULL );
PostgreSQL
CREATE TABLE IF NOT EXISTS "orders" (
"id" BIGSERIAL,
"customer_id" BIGINT NOT NULL,
"total" NUMERIC(10,2) NOT NULL,
"notes" TEXT,
"reference" VARCHAR(255),
"placed_at" TIMESTAMP NOT NULL,
PRIMARY KEY ("id")
);Oracle DATE has a time in it
An Oracle DATE stores the year down to the second. PostgreSQL's DATE stores only the day, so mapping DATE to DATE would discard every time of day in the database without a single error. The converter maps it to TIMESTAMP instead, which is why the table above shows DATE becoming TIMESTAMP.
Code written against Oracle often strips the time with TRUNC(d). The PostgreSQL forms are date_trunc('day', d) or d::date. Comparisons with a bare date literal behave as they did in Oracle: they match midnight values only, which is a bug the application already had rather than a new one.
NUMBER(10) does not fit in INTEGER
NUMBER(10) holds up to 9,999,999,999, which is more than PostgreSQL's INTEGER maximum of 2,147,483,647, so the converter makes it BIGINT. NUMBER(19) can exceed even BIGINT's 9,223,372,036,854,775,807, so it stays NUMERIC(19,0): exact, but slower to index and join than a native integer.
Most NUMBER(19) columns are ids generated by a sequence and will never come near the limit. For those, narrowing to BIGINT is a per-column override, and it is worth making before the load rather than after, because every foreign key pointing at the column has to change with it.
Empty strings stop being NULL
Oracle cannot store an empty string. Insert '' into a VARCHAR2 and you get NULL, so the migrated data contains NULLs where the application thought it wrote empty strings. That part is correct. The difference arrives with the first new write: the same application inserting '' into PostgreSQL stores an empty string, and a column that held only NULLs starts holding both.
WHERE col IS NULL then stops finding the new rows, and a unique index treats '' as a real value that can collide. Decide which of the two each column means, and normalise either in the application or with a CHECK, before both kinds of row exist.
The (+) outer join has to be rewritten by hand
Oracle's (+) outer-join syntax is the one construct the SQL rewriter refuses to convert automatically. Transpiled mechanically it turns into an inner join, which runs without complaint and silently drops exactly the rows the outer join existed to keep. Each statement that uses it is reported for a hand rewrite as LEFT or RIGHT JOIN.
For ROWNUM, CONNECT BY, NVL, SYSDATE, sequence.NEXTVAL and FROM DUAL, the rewriter checks its own output: if any Oracle construct survives the rewrite, the statement is reported as not converted rather than passed along looking finished.
Oracle → PostgreSQL conversion notes
Engine-specific rules baked into the conversion and transfer pipeline — verified by live certification of this exact pair.
- NUMBER columns are fetched as exact decimals (38 digits), not lossy floats.
- Identifier case differences (Oracle uppercases unquoted names) are reconciled automatically during transfer.
- Empty string = NULL semantics are accounted for in validation so comparisons stay honest.
- Bulk loads use sequence reseeding (setval to MAX) so SERIAL columns keep working after migration.
- ENUMs from MySQL become native CREATE TYPE … AS ENUM — validation semantics preserved.
- Foreign keys and indexes are applied after data load for dramatically faster transfers.
Frequently asked questions
Is Oracle to PostgreSQL migration production-ready in DBShifts?
Yes. Oracle to PostgreSQL is a certified pair: it passes the live certification suite on real Oracle and PostgreSQL servers, which checks schema fidelity, row counts, type-aware checksums and paging across batches.
What happens to Oracle types that PostgreSQL doesn't have?
Each gets the nearest PostgreSQL type the converter treats as safe: DATE becomes TIMESTAMP. Every such decision is in the migration plan before anything runs, and any mapping can be overridden per column.
Can I keep Oracle and PostgreSQL in sync after the initial migration?
Yes, until you cut over. DBShifts reads Flashback Version Query between SCNs, from an SCN captured before the first row is copied. A change has to still be in undo when it is read, so undo retention sets how long the stream can pause.
Which Oracle data types need a second look when moving to PostgreSQL?
The table above carries a note on 4 of them: NUMBER(10) (BIGINT), CLOB (TEXT), DATE (TIMESTAMP), RAW (BYTEA). Analysis flags the same columns on your own schema before any data moves.
Go deeper
Leaving Oracle for PostgreSQL: A Field Guide
NUMBER precision, empty-string NULLs, identifier casing and PL/SQL: what changes on the way to Postgres.
How We Live-Certified All 49 Migration Paths
The certification harness behind this page's claims.
Rehearse the Migration: Dry Runs, Quarantine, and Rollback
Rehearse, isolate failures, and keep the exit open.
Reference
Compared with other tools
Errors you may hit
Ready to move Oracle to PostgreSQL?
Set up in under two minutes. Validation and rollback included.
Start Free Migration