Reference
Cross-engine data type mapping
What every MySQL type becomes in PostgreSQL, SQL Server, Oracle and SQLite. Generated from the same mapping rules DBShifts uses to convert a schema, so the table cannot disagree with what a migration actually does.
41 types
| Source type | PostgreSQL | SQL Server | Oracle | SQLite |
|---|---|---|---|---|
| bigint | BIGINT | BIGINT | NUMBER(19) | INTEGER |
| binary | BYTEA | BINARY | RAW | BLOB |
| bit Bit/bitstring semantics vary — some engines treat it as boolean, others as bytes. | BOOLEAN | BIT | RAW(8) | INTEGER |
| blob Binary large object; maximum size differs per engine. | BYTEA | VARBINARY(MAX) | BLOB | BLOB |
| bool | BOOLEAN | BIT | NUMBER(1) | INTEGER |
| boolean | BOOLEAN | BIT | NUMBER(1) | INTEGER |
| char | CHAR | NCHAR | CHAR | TEXT |
| date | DATE | DATE | DATE | TEXT |
| datetime | TIMESTAMP | DATETIME2 | TIMESTAMP | TEXT |
| dec | NUMERIC | DECIMAL | NUMBER | NUMERIC |
| decimal | NUMERIC | DECIMAL | NUMBER | NUMERIC |
| double Binary floating point. Money in a DOUBLE will round; use DECIMAL/NUMERIC end to end. | DOUBLE PRECISION | FLOAT(53) | BINARY_DOUBLE | REAL |
| double precision | DOUBLE PRECISION | FLOAT(53) | BINARY_DOUBLE | REAL |
| enum Value list is preserved as a native enum, CHECK constraint or lookup depending on target. | TEXT | NVARCHAR(255) | VARCHAR2(255) | TEXT |
| fixed | NUMERIC | DECIMAL | NUMBER | NUMERIC |
| float Binary floating point — same rounding caveat as DOUBLE. | REAL | FLOAT | BINARY_FLOAT | REAL |
| int | INTEGER | INT | NUMBER(10) | INTEGER |
| integer | INTEGER | INT | NUMBER(10) | INTEGER |
| json JSON support and indexing differ sharply per engine. | JSONB | NVARCHAR(MAX) | CLOB | TEXT |
| longblob | BYTEA | VARBINARY(MAX) | BLOB | BLOB |
| longtext | TEXT | NVARCHAR(MAX) | CLOB | TEXT |
| mediumblob | BYTEA | VARBINARY(MAX) | BLOB | BLOB |
| mediumint | INTEGER | INT | NUMBER(7) | INTEGER |
| mediumtext | TEXT | NVARCHAR(MAX) | CLOB | TEXT |
| nchar | CHAR | NCHAR | CHAR | TEXT |
| numeric | NUMERIC | DECIMAL | NUMBER | NUMERIC |
| nvarchar | VARCHAR | NVARCHAR | VARCHAR2 | TEXT |
| real | REAL | REAL | BINARY_FLOAT | REAL |
| serial | BIGINT | BIGINT | NUMBER(19) | INTEGER |
| set No direct equivalent outside MySQL; converted to text or a related structure with the value list kept. | TEXT[] | NVARCHAR(MAX) | VARCHAR2(4000) | TEXT |
| smallint | SMALLINT | SMALLINT | NUMBER(5) | INTEGER |
| text | TEXT | NVARCHAR(MAX) | CLOB | TEXT |
| time MySQL TIME is a duration (-838h..838h), not a clock time — engines without that range need conversion. | TIME | TIME | VARCHAR2(18) | TEXT |
| timestamp Timezone semantics differ per engine; a naive target silently shifts the instant. | TIMESTAMPTZ | DATETIMEOFFSET | TIMESTAMP WITH TIME ZONE | TEXT |
| tinyblob | BYTEA | VARBINARY(255) | RAW(255) | BLOB |
| tinyint | SMALLINT | SMALLINT | NUMBER(3) | INTEGER |
| tinytext | TEXT | NVARCHAR(255) | VARCHAR2(255) | TEXT |
| uuid | UUID | UNIQUEIDENTIFIER | VARCHAR2(36) | TEXT |
| varbinary | BYTEA | VARBINARY | RAW | BLOB |
| varchar | VARCHAR | NVARCHAR | VARCHAR2 | TEXT |
| year MySQL-only. Widened to a numeric or date type elsewhere. | SMALLINT | SMALLINT | NUMBER(4) | INTEGER |
Types flagged in amber change what the value can represent. Those are the ones a row-count check will never reveal.
Frequently asked questions
What is MySQL TINYINT(1) in PostgreSQL?
BOOLEAN. MySQL has no native boolean — it stores one as TINYINT(1) — so that specific width converts to BOOLEAN while a plain TINYINT stays numeric (SMALLINT). Converting both the same way is a common and expensive mistake: it turns star ratings, counters and status codes into true/false.
How do I convert MySQL DECIMAL to PostgreSQL without losing precision?
DECIMAL maps to NUMERIC, which is exact in both engines, and precision and scale are carried across. The danger is DOUBLE or FLOAT: those are binary floating point, so money stored in them will round. If a column holds currency, keep it DECIMAL/NUMERIC on both sides — a row-count check will never reveal the difference, only a type-aware checksum will.
What happens to MySQL ENUM when migrating to another database?
The value list is preserved, but how depends on the target: PostgreSQL gets a native enum type, SQL Server and Oracle get a CHECK constraint, and SQLite gets text. The values themselves survive in every case — what changes is where the constraint lives.
Why does a TIMESTAMP change value after migration?
Because timezone semantics differ per engine. A timezone-aware source value moved into a naive target column keeps its wall-clock reading while pointing at a different instant — a SQL Server DATETIMEOFFSET of 13:45+05:30 becomes 13:45 with no offset, shifting the moment by five and a half hours. Row counts still match exactly; only a checksum that canonicalises timezones catches it.
Which type conversions actually lose data?
The ones marked below. In practice the recurring three are: floating-point types used for money, timezone-aware timestamps landing in naive columns, and engine-specific types such as MySQL's TIME (a duration from -838h to +838h, not a clock time) that have no direct equivalent. DBShifts reports each of these as a lossy conversion before the migration runs rather than after.
Stop reading the table — let it check your schema.
Point DBShifts at your database and it reports every lossy conversion in your actual schema before anything moves, then verifies each row landed with a type-aware checksum.