postgresql
oracle

Migrate PostgreSQL to Oracle

Certified — live-tested path

The converter maps 18 PostgreSQL column types to Oracle. 5 of them change kind on the way (boolean becomes NUMBER(1), time becomes VARCHAR2(18), json becomes CLOB, and more); 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.

Start PostgreSQL → Oracle Migration

Free tier · no credit card

PostgreSQL vs Oracle

postgresql

PostgreSQL

the most advanced open-source relational database.

oracle

Oracle

Oracle Database, the long-standing enterprise standard.

PostgreSQL to Oracle data type mapping

Pulled directly from DBShifts's own conversion rules — not a general reference table, this is what actually runs.

PostgreSQLOracle
integerNUMBER(10)
bigintNUMBER(19)
smallintNUMBER(5)
booleanNUMBER(1)
numeric(10,2)NUMBER(10,2)
realBINARY_FLOAT
double precisionBINARY_DOUBLE
varchar(255)VARCHAR2(255 CHAR)
textCLOB
char(10)CHAR(10 CHAR)
dateDATE
timestampTIMESTAMP
timestamptzTIMESTAMP WITH TIME ZONE
timeVARCHAR2(18)
byteaBLOB
jsonCLOB
jsonbCLOB
uuidVARCHAR2(255 CHAR)

Example: PostgreSQL to Oracle schema conversion

PostgreSQL

CREATE TABLE orders (
  id            bigserial PRIMARY KEY,
  customer_id   integer NOT NULL,
  total         numeric(10,2) NOT NULL,
  is_gift       boolean NOT NULL DEFAULT false,
  notes         text,
  metadata      jsonb,
  placed_at     timestamptz NOT NULL
);

Oracle

CREATE TABLE "orders" (
    "id" NUMBER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    "customer_id" NUMBER(10) NOT NULL,
    "total" NUMBER(10,2) NOT NULL,
    "is_gift" NUMBER(1) DEFAULT 0 NOT NULL,
    "notes" CLOB,
    "metadata" CLOB,
    "placed_at" TIMESTAMP WITH TIME ZONE NOT NULL,
    CONSTRAINT pk_orders PRIMARY KEY ("id")
);

Lowercase names stop resolving

PostgreSQL folds unquoted identifiers to lower case. Oracle folds them to upper case. The converter keeps your names exactly as they are and quotes them, CREATE TABLE "orders" with a column "is_gift", so the Oracle tables really are lowercase. Unquoted SQL against them then fails: SELECT is_gift FROM orders looks for ORDERS and returns ORA-00942.

There are two ways out. Quote every identifier in the application's SQL, which keeps the names but touches every query. Or rename the objects to upper case right after the load, before views, synonyms and grants start depending on the lowercase spelling. Choose before cutover; switching later means doing it with production traffic on the tables.

An empty string is NULL in Oracle

Oracle stores '' in a VARCHAR2 as NULL. PostgreSQL rows holding empty strings therefore arrive as NULL: a nullable column changes meaning without an error, so WHERE col = '' now matches nothing, and a NOT NULL column refuses those rows with ORA-01400. Rows that fail to insert land in quarantine with that error rather than disappearing.

Find them before the load, not in quarantine afterwards. SELECT count(*) FROM t WHERE col = '' on each NOT NULL text column shows where the problem is, and the fix is a decision: allow NULL, or store a placeholder that means empty.

boolean, text and jsonb change how you query

Before Oracle 23ai there is no BOOLEAN column, so boolean becomes NUMBER(1) DEFAULT 0, as the worked example above shows. NUMBER(1) accepts any digit from -9 to 9. Add CHECK (is_gift IN (0, 1)) if the database should keep enforcing true and false, and expect WHERE is_gift to become WHERE is_gift = 1.

text, json and jsonb land in CLOB, because a VARCHAR2 tops out at 4,000 bytes by default. A CLOB cannot be compared with =, and cannot appear in GROUP BY, DISTINCT or ORDER BY; each of those fails with ORA-00932. PostgreSQL's -> and ->> operators have no Oracle spelling either: JSON_VALUE(metadata, '$.key') is the equivalent.

PostgreSQL → Oracle conversion notes

Engine-specific rules baked into the conversion and transfer pipeline — verified by live certification of this exact pair.

  • Arrays (integer[], text[]) are preserved natively on PostgreSQL targets and serialized to JSON elsewhere.
  • JSONB, UUID, INET and other rich types fall back to portable representations with every downgrade recorded in the fidelity report.
  • SERIAL/identity sequences are recreated and reseeded on the target.
  • CHAR columns use character semantics, so multi-byte UTF-8 doesn't overflow byte-sized columns.
  • Booleans land as NUMBER(1) with values bound as integers (Oracle drivers can't bind Python booleans to NUMBER).
  • DROP TABLE … CASCADE CONSTRAINTS wrappers handle re-runs without manual cleanup.

Frequently asked questions

Is PostgreSQL to Oracle migration production-ready in DBShifts?

Yes. PostgreSQL to Oracle is a certified pair: it passes the live certification suite on real PostgreSQL and Oracle servers, which checks schema fidelity, row counts, type-aware checksums and paging across batches.

What happens to PostgreSQL types that Oracle doesn't have?

Each gets the nearest Oracle type the converter treats as safe: boolean becomes NUMBER(1), time becomes VARCHAR2(18), json becomes CLOB, jsonb becomes CLOB, uuid becomes VARCHAR2(255 CHAR). Every such decision is in the migration plan before anything runs, and any mapping can be overridden per column.

Can I keep PostgreSQL and Oracle in sync after the initial migration?

Yes, until you cut over. DBShifts uses logical decoding through a replication slot, which needs wal_level = logical and the wal2json plugin on the server. With zero-downtime mode on, the slot is created before the first row is copied, so it holds every change made during the copy.

Which PostgreSQL data types need a second look when moving to Oracle?

The table above carries a note on 3 of them: timestamptz (TIMESTAMP WITH TIME ZONE), bytea (BLOB), jsonb (CLOB). Analysis flags the same columns on your own schema before any data moves.

Go deeper

Ready to move PostgreSQL to Oracle?

Set up in under two minutes. Validation and rollback included.

Start Free Migration