SQL Data Type Mapping: SQL Server, Oracle, PostgreSQL, MySQL
Moving data between databases, or between an ERP and a data warehouse, fails most often on data types. This reference lists the common equivalents across SQL Server, Oracle, PostgreSQL and MySQL with the traps that cause silent data loss.
| Concept | SQL Server | Oracle | PostgreSQL | MySQL | Note |
|---|---|---|---|---|---|
Variable text (Unicode) | nvarchar(n) | NVARCHAR2(n) | varchar(n) | varchar(n) utf8mb4 | PostgreSQL and MySQL store Unicode via the database or column encoding |
Variable text (non-Unicode) | varchar(n) | VARCHAR2(n) | varchar(n) | varchar(n) | Oracle sizes are bytes by default unless CHAR semantics are set |
Fixed text | char(n) | CHAR(n) | char(n) | char(n) | Padded with spaces; compare behavior differs between systems |
Long text | varchar(max) / nvarchar(max) | CLOB | text | longtext | Avoid the deprecated SQL Server text and ntext types |
Small integer | smallint | NUMBER(5) | smallint | smallint | Oracle has no separate integer storage types |
Integer (32 bit) | int | NUMBER(10) | integer | int | |
Big integer (64 bit) | bigint | NUMBER(19) | bigint | bigint | |
Exact decimal | decimal(p,s) | NUMBER(p,s) | numeric(p,s) | decimal(p,s) | Use for quantities, prices and cost; never float |
Currency amount | decimal(19,4) (or money) | NUMBER(19,4) | numeric(19,4) | decimal(19,4) | Check rounding rules and currency decimals in your ERP |
Floating point (double) | float(53) | BINARY_DOUBLE | double precision | double | Approximate; unsuitable for money |
Boolean / flag | bit | NUMBER(1) | boolean | tinyint(1) | Many ERPs store flags as 0/1 integers or Y/N text; map to the actual convention |
Date only | date | DATE (includes time) | date | date | Oracle DATE always carries a time part to the second |
Date and time | datetime2(n) | TIMESTAMP(n) | timestamp(n) | datetime(n) | Legacy SQL Server datetime has about 3 ms precision |
Date and time with zone | datetimeoffset | TIMESTAMP WITH TIME ZONE | timestamptz | timestamp (stored as UTC) | MySQL has no direct zone offset type |
Binary data | varbinary(max) | BLOB | bytea | longblob | Check how ERP attachments are stored before migrating |
GUID / UUID | uniqueidentifier | RAW(16) | uuid | char(36) or binary(16) | Byte order differs between systems; verify matches |
Auto-generated key | IDENTITY(1,1) | GENERATED AS IDENTITY (12c+) or sequence | GENERATED ALWAYS AS IDENTITY | AUTO_INCREMENT | Reseed to the max value after bulk loads |
XML | xml | XMLTYPE | xml | longtext | MySQL has no native XML column type |
JSON | nvarchar(max) with ISJSON check | JSON (newer versions) or CLOB | jsonb | json | Newer SQL Server releases add a native json type |
Empty string | Distinct from NULL | Treated as NULL | Distinct from NULL | Distinct from NULL | A classic Oracle migration trap for required text columns |
String concatenation | + or CONCAT() | || | || | CONCAT() | In Oracle, || with NULL treats it as empty text |
Current date and time | SYSDATETIME() / GETDATE() | SYSTIMESTAMP / SYSDATE | now() | NOW() | |
Row limit | TOP n / OFFSET FETCH | FETCH FIRST n ROWS ONLY (12c+) | LIMIT n | LIMIT n | Older Oracle versions use ROWNUM |
Null replacement | ISNULL() / COALESCE() | NVL() / COALESCE() | COALESCE() | IFNULL() / COALESCE() | COALESCE is portable across all four |
A planning aid, not a conversion tool. Behavior depends on version and settings (collation, character set, NLS settings, sql_mode), so test a sample load and compare row counts, control totals and edge values such as maximum lengths, zero dates and negative numbers.
Migration mapping review
We can check your ERP column mapping before a migration or reporting build. Optional.
Optional. The tool and every export work without this. No spam.
How to use the table
Search for the concept you need, such as date and time or currency amount, and read across to the database you are targeting. The note column flags behavior that differs even when the type names look similar. Use the export button to take the table into Excel and add your own column list, marking the source type, the target type and the transformation required for each ERP column. That becomes the core of your migration mapping document.
Where mappings go wrong
The riskiest differences are not the names but the semantics. Oracle treats an empty string as NULL, so required text fields can fail on load. Oracle DATE carries a time part, which changes how date comparisons behave. Money stored as float rounds incorrectly after many operations. Text lengths measured in bytes rather than characters truncate accented names. A flag stored as Y or N does not map to a boolean without a transformation.
- Check empty string versus NULL handling
- Check character versus byte length
- Check time zone handling for timestamps
- Check identity and sequence values after loading
Validating a mapping
Load a representative sample, not just a clean one. Include maximum length text, zero and null dates, negative numbers, very large quantities and special characters. Then compare row counts, control totals of key numeric columns, minimum and maximum dates and the count of NULL values per column between source and target. Differences point directly to the mapping that needs fixing. Keep the comparison queries so they can be run again at cutover.
ERP specific notes
Packaged ERPs often have their own conventions on top of the database types: SAP stores dates and numbers as character fields in many tables, JD Edwards uses Julian style dates, and some systems store flags and amounts in unexpected types. Always map from the ERP's data dictionary rather than from the column type alone, and prefer the vendor's supported export, API or integration layer for production moves.
Frequently Asked Questions
What is the Oracle equivalent of SQL Server datetime2?
TIMESTAMP(n) is the closest match for datetime2(n), holding fractional seconds. Oracle DATE has second precision only and includes a time part, so it suits plain datetime columns without fractions.
What is the PostgreSQL equivalent of SQL Server bit?
boolean. SQL Server bit stores 0, 1 or NULL; PostgreSQL boolean stores true, false or NULL. Cast explicitly when moving data and check how your application reads the value.
Why do my Oracle imports reject empty strings?
Oracle treats a zero length string as NULL, so a NOT NULL column fails if the source holds empty text. Replace empty strings with a default such as a single space, or relax the constraint where the business allows.
Should money use decimal or money types?
Use exact decimal with fixed scale, for example decimal(19,4), across systems. It behaves the same everywhere and avoids the rounding quirks of SQL Server money in multiplication and division.
If you are planning an ERP database migration or a read-only data layer on top of your ERP, we can review the mapping with you.
Related Tools
Excel to SQL INSERT Statement Generator
Build INSERT statements from spreadsheet rows, with an Excel formula that creates the value lists and a rollback-safe wrapper.
Developer ToolsSQL Date Range Filter Generator
Builds a correct half-open date range WHERE clause for four SQL dialects, so month-end and timestamp reports stop dropping the last day.
Developer ToolsERP Data Extract PowerShell to CSV Script Generator
Creates a scheduled-task-ready PowerShell script that runs a read-only SQL query and writes a timestamped CSV with a simple log.
ERP OperationsSQL Server Index Maintenance Checklist for ERP Databases
A practical checklist for index rebuilds, reorganizes, statistics updates and verification on SQL Server hosted ERPs.
More Developer Tools tools
See all- SAP Sales Order ABAP SELECT Generator (VBAK, VBAP, VBFA)
- SAP Sales Order Create BAPI ABAP Generator (BAPI_SALESORDER_CREATEFROMDAT2)
- SAP ABAP Open Items SELECT Generator
- SAP ACDOCA SQL Query Generator
- SAP QM Inspection Lot ABAP SELECT Generator
- SAP Quality Notification Pareto SQL Query Generator
- SAP QM Usage Decision BAPI Skeleton
- ABAP Custom Exception Class Generator
- ABAP Unit Test Class Generator