Developer ToolsFree referencePDF, Excel, Word exportMulti-database

SQL Data Type Mapping Table: SQL Server, Oracle, PostgreSQL and MySQL

ERP data migrations fail quietly when a type is mapped almost correctly. This reference lines up the common data types across SQL Server, Oracle, PostgreSQL and MySQL, with the gotcha for each.

Download:
ConceptSQL ServerOraclePostgreSQLMySQLWatch out for
16-bit integer
smallint
NUMBER(5)
smallint
SMALLINT
Oracle has no native small integer type, NUMBER(5) is the common choice.
32-bit integer
int
NUMBER(10)
integer
INT
Oracle INTEGER is an alias of NUMBER(38), so precision can differ from the source.
64-bit integer
bigint
NUMBER(19)
bigint
BIGINT
Surrogate keys above 2 billion need bigint on every side of a join.
Exact decimal
decimal(p,s)
NUMBER(p,s)
numeric(p,s)
DECIMAL(p,s)
Use exact types for quantities and money, never float, to avoid rounding drift.
Money
money
NUMBER(19,4)
numeric(19,4)
DECIMAL(19,4)
SQL Server money has four decimals and rounds in intermediate division, so many teams prefer decimal.
Double precision float
float
BINARY_DOUBLE
double precision
DOUBLE
Oracle FLOAT is a decimal-precision type, not the same as BINARY_DOUBLE.
Single precision float
real
BINARY_FLOAT
real
FLOAT
Avoid for ERP quantities and costs.
Fixed length text
char(n)
CHAR(n)
char(n)
CHAR(n)
Padding behaviour in comparisons differs by engine and setting.
Variable length text
varchar(n)
VARCHAR2(n)
varchar(n)
VARCHAR(n)
Oracle length defaults to bytes unless CHAR semantics are declared, so multibyte text can overflow.
Unicode variable text
nvarchar(n)
NVARCHAR2(n)
varchar(n)
VARCHAR(n) with utf8mb4
PostgreSQL text is Unicode already when the database encoding is UTF8.
Long text
varchar(max) or nvarchar(max)
CLOB
text
LONGTEXT
Oracle CLOB needs special handling in many ETL tools.
Binary data
varbinary(max)
BLOB
bytea
LONGBLOB
Attachment tables are often the slowest part of an ERP migration.
Date only
date
DATE (contains a time part)
date
DATE
Oracle DATE stores time to the second, so comparisons with date-only values need truncating.
Date and time
datetime2(n)
TIMESTAMP(n)
timestamp
DATETIME(n)
Legacy SQL Server datetime rounds to roughly 3 milliseconds, datetime2 does not.
Date and time with zone
datetimeoffset
TIMESTAMP WITH TIME ZONE
timestamptz
TIMESTAMP (stored as UTC)
Decide on UTC versus local storage before loading, then convert once.
Time of day
time
No direct type, use DATE or INTERVAL DAY TO SECOND
time
TIME
Routing and shift data often carry time-only values.
Boolean flag
bit
NUMBER(1) or CHAR(1)
boolean
TINYINT(1) or BOOLEAN
Many ERPs use 0 and 1 or Y and N, check which one the source uses.
GUID
uniqueidentifier
RAW(16)
uuid
CHAR(36) or BINARY(16)
Byte order of GUIDs can differ between display and storage.
Auto-generated key
IDENTITY
GENERATED AS IDENTITY (12c and later) or sequence
GENERATED AS IDENTITY or serial
AUTO_INCREMENT
Reseed the generator after loading explicit key values.
XML
xml
XMLTYPE
xml
No native type, use TEXT
Validate that XML round-trips without namespace changes.
JSON
nvarchar(max) with JSON functions (native json type in recent versions)
JSON (21c and later) or VARCHAR2/CLOB
jsonb or json
JSON
jsonb reorders keys and removes duplicates.
Row version stamp
rowversion
No direct equivalent
No direct equivalent (system column xmin is not a stable stamp)
No direct equivalent
Optimistic concurrency logic must be rewritten, not just retyped.
Empty string vs NULL
Distinct
Empty string is stored as NULL
Distinct
Distinct
Moving into Oracle turns empty strings into NULL and can break NOT NULL columns and unique checks.

Mappings are common conventions, not the only valid ones. Confirm against your exact versions and any migration tool you use, and test with real row counts and boundary values.

Optional mapping review

Share your source column list and we will flag risky type mappings before the load.

Optional. The tool and every export work without this. No spam.

How to use the mapping

List the source columns with their types from the information schema, then search each concept here to choose the target type. Record the choice in your field mapping sheet along with any length or precision change. Check the gotcha column before you accept the default your migration tool proposes, since tools usually choose the widest safe type and may not match what the target ERP expects. Validate by comparing row counts, sums of numeric columns and minimum and maximum of date columns after the load.

Where mappings break

Four areas cause most problems. Oracle treats an empty string as NULL, which alters NOT NULL and unique logic. Oracle DATE carries a time part, so date joins miss rows. Length semantics differ between bytes and characters, so multibyte names such as Chinese or accented supplier names can be truncated. Finally, boolean and identity columns need logic changes rather than a type change. Build a short test set containing each of those cases and run it before the full load.

Precision for quantities and money

Pick DECIMAL or NUMERIC with an explicit scale for quantities, unit costs and exchange rates. A typical choice is scale 4 for unit cost and scale 6 for exchange rates, but follow what your target ERP defines. Floating point types introduce tiny errors that break inventory-to-ledger reconciliation after a few million transactions. If the source uses money, confirm whether any computed columns divide before multiplying, because money stores four decimals and rounds at each step.

Common mistakes

Teams often map every text column to the widest type, which hurts indexing and breaks tools that expect fixed lengths. Others forget collation, so case-insensitive matches in the source become case-sensitive in the target and duplicate part numbers appear. Document the collation and case rules next to the type table, and load a pilot slice of master data first.

Frequently Asked Questions

Why does Oracle break my empty string columns?

Oracle stores a zero-length string as NULL. After migrating, NOT NULL columns can reject rows and unique checks can behave differently. Replace empty strings with an agreed placeholder or relax the constraint before loading.

Should I map SQL Server money to decimal?

Usually yes. A decimal with explicit scale, such as 19,4, keeps the same range and avoids money rounding in intermediate calculations. Whatever you choose, keep it consistent across all tables.

Is datetime2 the same as Oracle TIMESTAMP?

They are close equivalents. Both hold fractional seconds. Oracle DATE is different because it carries a time part to the second, so it is not the same as SQL Server date.

How do I map uniqueidentifier to Oracle?

RAW(16) is the usual choice. Be aware that display and byte order of GUIDs can differ between systems, so test a sample by joining on the converted value before loading.

If you are moving ERP data between databases and want help building the mapping and reconciliation checks, we can review your column list with you.

Talk to an ExpertExplore More Free Tools

Related Tools

Developer Tools

ERP Data Dictionary Worksheet

An editable table to document ERP tables and columns with type, length, null rate, fill rate and owner.

Developer Tools

ERP Cutover Reconciliation SQL Generator

Fill in your table and column names and get ready-to-run comparison queries for legacy versus new ERP balances.

Developer Tools

SQL Isolation Levels and Locking Behavior Reference

Compare isolation levels across SQL Server, Oracle, PostgreSQL and MySQL with what each allows and how it affects ERP reports and postings.

Developer Tools

ERP Reporting Query Quality Assessment

Answer 10 questions about how your team writes, tests, documents and runs SQL reports on ERP data, and get a score with recommendations.

More Developer Tools tools

See all
Back to All Tools