Skip to content

Reference · Oracle Database to PostgreSQL

Oracle to PostgreSQL data types

Last updated 26 sources35 types
Answer

Most Oracle Database types map directly to PostgreSQL, such as VARCHAR2 to VARCHAR and CLOB to TEXT, while NUMBER without precision needs a decision and DATE becomes TIMESTAMP(0).

How to read the mapping table

Each row shows the Oracle Database type, its usual PostgreSQL type from the documentation under Sources, and whether it needs a decision.

Showing 35 of 35 types

Oracle Database to PostgreSQL data types: 35 mappings, Oracle Database 19c to PostgreSQL 16
Oracle Database typePostgreSQL typeRule or decisionNotes
VARCHAR2(n)varchar(n)Rule: Keep the declared length.Oracle can declare the length in bytes or characters (BYTE or CHAR). PostgreSQL always counts characters, so a byte length column can hold more characters after migration, never fewer.
NVARCHAR2(n)varchar(n)Rule: Keep the declared length in characters.PostgreSQL has no separate national character types. A UTF8 database stores all Unicode text in ordinary character types.
CHAR(n)char(n)Rule: Keep the declared length; both pad with spaces.PostgreSQL treats trailing spaces in char(n) as not significant when comparing two char(n) values, and removes them when converting to other string types.
NCHAR(n)char(n)Rule: Keep the declared length in characters.Same as NVARCHAR2: no national character types in PostgreSQL.
CHAR(1) holding only 'Y' and 'N'booleanDecision: Convert only when every value is Y, N or NULL.Oracle Database 19c has no BOOLEAN column type, so flags are often stored as CHAR(1). Moving to boolean changes queries and application code that compare with 'Y'. Keeping char(1) avoids those changes. In the example run (demo data), these columns were converted to boolean.
NUMBER(p) or NUMBER(p,0), p from 1 to 4smallintRule: Whole numbers of up to 4 digits fit smallint.smallint holds -32,768 to 32,767. Some tools keep numeric(p) instead when exact declared precision matters more than storage.
NUMBER(p) or NUMBER(p,0), p from 5 to 9integerRule: Whole numbers of up to 9 digits fit integer.integer holds -2,147,483,648 to 2,147,483,647.
NUMBER(p) or NUMBER(p,0), p from 10 to 18bigintRule: Whole numbers of up to 18 digits fit bigint.bigint holds -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
NUMBER(p) or NUMBER(p,0), p from 19 to 38numeric(p)Rule: Too large for bigint; keep exact precision.numeric is exact and slower than the integer types.
NUMBER(p,s) with s greater than 0numeric(p,s)Rule: Keep precision and scale.Both types are exact decimals, so values are stored without rounding.
NUMBER(p,s) with s less than 0numeric(p,s)Rule: Keep the negative scale.PostgreSQL accepts a negative scale from version 15, which rounds to the left of the decimal point as Oracle does. Older versions need numeric(p) and a rounding check.
NUMBER (no precision or scale)numericDecision: Choose per column: numeric, numeric(38,10), or an integer type when every value is whole.Oracle keeps the full precision and range that NUMBER allows. Unconstrained numeric keeps every value exactly; a fixed scale can round. In the example run (demo data), the team chose numeric(38,10) and accepted the rounding.
FLOAT(b)numericDecision: Keep exact decimal values; use double precision only if approximate values are acceptable.Oracle FLOAT is a NUMBER subtype with binary precision, so it stores exact decimals. PostgreSQL float types are approximate binary floating point.
BINARY_FLOATrealRule: Both are 4-byte binary floating point.Special values (Infinity, NaN) exist in both.
BINARY_DOUBLEdouble precisionRule: Both are 8-byte binary floating point.Special values (Infinity, NaN) exist in both.
DATEtimestamp(0)Rule: Oracle DATE includes a time to the second, so it maps to a timestamp, not to date.Mapping to PostgreSQL date would drop the time part. Use date only for columns whose time part is always midnight.
TIMESTAMP(p)timestamp(p)Rule: Keep fractional seconds up to 6 digits.Oracle allows up to 9 fractional digits; PostgreSQL allows up to 6, so digits 7 to 9 are rounded.
TIMESTAMP(p) WITH TIME ZONEtimestamptz(p)Rule: Store the same instant in time.PostgreSQL converts to UTC and does not keep the original time zone or offset. Store it in a separate column if it must be kept.
TIMESTAMP(p) WITH LOCAL TIME ZONEtimestamptz(p)Rule: Both store an instant and show it in the session time zone.Check the session time zone settings of the applications after cut-over.
INTERVAL YEAR TO MONTHinterval year to monthRule: Same fields.PostgreSQL interval values can mix months, days and seconds; the field restriction keeps only years and months.
INTERVAL DAY TO SECONDinterval day to secondRule: Same fields.Fractional seconds above 6 digits are rounded, as for TIMESTAMP.
CLOBtextRule: Large character data. text holds up to 1 GB per value; check the largest CLOB values before migrating.Oracle CLOB values can be far larger than 1 GB. Values above the PostgreSQL limit need a different design.
NCLOBtextRule: Same as CLOB.No national character types in PostgreSQL; a UTF8 database stores the same text.
LONGtextRule: Legacy long character data.Oracle keeps LONG only for backward compatibility and recommends CLOB instead.
BLOBbyteaRule: Binary data stored as an ordinary column value; PostgreSQL moves large values out of line automatically (TOAST).A single bytea value is limited to 1 GB. Larger objects need the PostgreSQL large object facility or external storage.
RAW(n)byteaRule: Binary data; bytea has no declared length.Add a check constraint on octet_length if the length limit must be kept.
RAW(16) holding SYS_GUID() valuesuuidDecision: Convert only when the column holds identifiers, not other binary data.uuid is easier to query and index than bytea, but every application that reads the raw bytes must change.
LONG RAWbyteaRule: Legacy long binary data.Oracle keeps LONG RAW only for backward compatibility and recommends BLOB instead.
BFILEtext (file path)Decision: No equivalent: PostgreSQL has no column type that points to a file outside the database.Choose between storing the path and moving the files elsewhere, or loading the contents into bytea.
ROWID or UROWIDtextDecision: No equivalent: stored row addresses are meaningless in another database.PostgreSQL ctid is a physical location that changes on update, so it cannot replace a stored ROWID. Use a primary key instead.
XMLTYPExmlRule: XML documents.Oracle XML functions and XPath queries need converting separately.
VARCHAR2, CLOB or BLOB with an IS JSON checkjsonbDecision: Oracle Database 19c stores JSON in character or binary columns; PostgreSQL has native JSON types.jsonb does not keep key order, duplicate keys or whitespace. Use json if the exact text must be kept.
VARRAY or nested table typearray of the element typeDecision: Collection columns become PostgreSQL arrays.PostgreSQL accepts a declared array size but does not enforce it; add a check constraint (for example on cardinality()) if the VARRAY limit must be kept. A child table is often the better design for nested tables.
PL/SQL BOOLEANbooleanRule: Variables and parameters in procedures and functions.In Oracle Database 19c BOOLEAN exists in PL/SQL only, not as a column type.
PLS_INTEGER or BINARY_INTEGERintegerRule: 32-bit signed whole numbers in PL/SQL.Oracle raises an overflow error outside the range, and so does PostgreSQL integer.

Which mappings need care?

8 mappings need a decision, because the right choice depends on your data and an approver chooses before the migration runs.

In the example run, 1,284 columns used NUMBER without precision. They mapped to NUMERIC(38,10), decided by Sarah Mitchell.

Frequently asked questions

What is the PostgreSQL equivalent of Oracle NUMBER?

NUMERIC for decimals. Whole numbers up to 18 digits map to SMALLINT, INTEGER or BIGINT. A NUMBER with no precision needs a decision: unconstrained NUMERIC keeps every digit, while NUMERIC(38,10) can round values.

Should Oracle DATE become PostgreSQL DATE or TIMESTAMP?

Usually TIMESTAMP(0). Oracle DATE stores a time to the second, and PostgreSQL DATE has no time, so mapping to DATE would lose it.

What does VARCHAR2 become in PostgreSQL?

VARCHAR, with the same length. Check whether the Oracle column length was set in bytes or characters, because PostgreSQL lengths count characters.

How are empty strings handled?

Oracle Database treats an empty string as NULL, so code and constraints that rely on this need review during the migration.

How do I check that the types converted correctly?

Datalobe checks column structure against the plan, compares checksums on all rows and compares sample rows value by value.

Sources (26)

Next step

Map the types in your own schema

The free assessment maps every column in your schema and lists the ones that need a decision.