Reference · Oracle Database to PostgreSQL
Oracle to PostgreSQL data types
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 type | PostgreSQL type | Rule or decision | Notes |
|---|---|---|---|
| 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' | boolean | Decision: 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 4 | smallint | Rule: 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 9 | integer | Rule: 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 18 | bigint | Rule: 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 38 | numeric(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 0 | numeric(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 0 | numeric(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) | numeric | Decision: 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) | numeric | Decision: 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_FLOAT | real | Rule: Both are 4-byte binary floating point. | Special values (Infinity, NaN) exist in both. |
| BINARY_DOUBLE | double precision | Rule: Both are 8-byte binary floating point. | Special values (Infinity, NaN) exist in both. |
| DATE | timestamp(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 ZONE | timestamptz(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 ZONE | timestamptz(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 MONTH | interval year to month | Rule: Same fields. | PostgreSQL interval values can mix months, days and seconds; the field restriction keeps only years and months. |
| INTERVAL DAY TO SECOND | interval day to second | Rule: Same fields. | Fractional seconds above 6 digits are rounded, as for TIMESTAMP. |
| CLOB | text | Rule: 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. |
| NCLOB | text | Rule: Same as CLOB. | No national character types in PostgreSQL; a UTF8 database stores the same text. |
| LONG | text | Rule: Legacy long character data. | Oracle keeps LONG only for backward compatibility and recommends CLOB instead. |
| BLOB | bytea | Rule: 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) | bytea | Rule: 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() values | uuid | Decision: 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 RAW | bytea | Rule: Legacy long binary data. | Oracle keeps LONG RAW only for backward compatibility and recommends BLOB instead. |
| BFILE | text (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 UROWID | text | Decision: 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. |
| XMLTYPE | xml | Rule: XML documents. | Oracle XML functions and XPath queries need converting separately. |
| VARCHAR2, CLOB or BLOB with an IS JSON check | jsonb | Decision: 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 type | array of the element type | Decision: 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 BOOLEAN | boolean | Rule: 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_INTEGER | integer | Rule: 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.
Related
Related features
Sources (26)
- Oracle Database 19c SQL Language Reference: Data Types, checked
- Oracle Database 19c Reference: Datatype Limits, checked
- Oracle Database 19c PL/SQL Language Reference: BOOLEAN Data Type, checked
- Oracle Database 19c PL/SQL Language Reference: PLS_INTEGER and BINARY_INTEGER Data Types, checked
- PostgreSQL 16 Documentation: 8.3 Character Types, checked
- PostgreSQL 16 Documentation: 8.1 Numeric Types, checked
- PostgreSQL 16 Documentation: 8.5 Date/Time Types, checked
- PostgreSQL 16 Documentation: 8.4 Binary Data Types, checked
- PostgreSQL 16 Documentation: 8.6 Boolean Type, checked
- PostgreSQL 16 Documentation: 8.12 UUID Type, checked
- PostgreSQL 16 Documentation: 8.13 XML Type, checked
- PostgreSQL 16 Documentation: 8.14 JSON Types, checked
- PostgreSQL 16 Documentation: 8.15 Arrays, checked
- PostgreSQL 16 Documentation: Appendix K. PostgreSQL Limits, checked
- PostgreSQL 16 Documentation: 73.2 TOAST, checked
- PostgreSQL 16 Documentation: 5.5 System Columns, checked
- Oracle Database 19c Globalization Support Guide: Datetime Data Types and Time Zone Support, checked
- Oracle Database 19c SQL Language Reference: SYS_GUID, checked
- Oracle Database 19c SQL Language Reference: ROWID Pseudocolumn, checked
- Oracle Database 19c JSON Developer's Guide: JSON in Oracle Database, checked
- Oracle Database 19c Object-Relational Developer's Guide: Collection Data Types, checked
- PostgreSQL 15.0 Release Notes, checked
- PostgreSQL 16 Documentation: 24.3 Character Set Support, checked
- PostgreSQL 16 Documentation: 35.1 Introduction (Large Objects), checked
- PostgreSQL 16 Documentation: Chapter 8 Data Types, checked
- Oracle Database 19c SQL Language Reference: Nulls, checked
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.