SQL Data Types Across Dialects: The Migration Cheat Sheet

When you're moving a database from PostgreSQL to MySQL, or from Oracle to SQL Server, the first thing that breaks is usually data types. Not CREATE TABLE syntax. Not constraint names. Types.

A BOOLEAN in PostgreSQL becomes TINYINT(1) in MySQL. A TEXT in SQLite has no length limit, but in MySQL you must choose between TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. Oracle doesn't have a native BOOLEAN at all — you use NUMBER(1) and pray your ORM understands the convention.

This guide maps the most common SQL data types across five major dialects, highlights the traps, and links to a free interactive reference you can use during your next migration.

Why Type Mappings Matter

Schema migration tools focus on structure: tables, columns, indexes, constraints. But the actual data inside those columns depends entirely on type compatibility. A migration that changes NUMERIC(19,4) to MONEY might look safe, but rounding behavior differs between SQL Server and PostgreSQL. A UUID stored as CHAR(36) in MySQL takes 36 bytes; in PostgreSQL as UUID it takes 16 bytes. These differences accumulate into performance problems and subtle data corruption.

Numeric Types

Trap: SQLite has no dedicated BIGINT. It uses dynamic typing, so an INTEGER column can store a 64-bit value, but there's no enforced size limit. If your application relies on overflow behavior, SQLite will silently accept larger values.

String Types

Trap: MySQL's VARCHAR(255) with utf8mb4 uses up to 4 bytes per character, so the actual character limit can be as low as 63 if you're using the maximum byte length. PostgreSQL's VARCHAR(n) is character-based, not byte-based.

Boolean: Five Dialects, Five Answers

There is no standard Boolean type across SQL dialects:

• PostgreSQL: Native BOOLEAN with true/false literals.
• MySQL: BOOLEAN is an alias for TINYINT(1). true becomes 1, false becomes 0.
• SQL Server: BIT (0, 1, or NULL). No native Boolean literals in standard T-SQL.
• SQLite: No Boolean type. Use INTEGER with 0 or 1.
• Oracle: No Boolean type. Convention is NUMBER(1) with CHECK constraint.

If you're migrating from PostgreSQL to any other dialect and your ORM doesn't handle the mapping, every WHERE is_active = true query will break.

Date and Time

Temporal types are surprisingly consistent at the surface level — DATE, TIME, TIMESTAMP exist in most dialects — but precision and timezone handling differ:

• PostgreSQL: TIMESTAMP has microsecond precision. TIMESTAMPTZ stores timezone-aware values.
• MySQL: DATETIME has microsecond precision (5.6+). TIMESTAMP is timezone-aware but limited to 2038 on 32-bit systems.
• SQL Server: DATETIME2 has 100ns precision. DATETIMEOFFSET stores timezone.
• SQLite: Stores datetimes as ISO-8601 text strings. No native date arithmetic — use built-in functions.
• Oracle: DATE includes time to the second. TIMESTAMP is deprecated for new designs.

JSON: The Moving Target

JSON support has been added to every major database in the last decade, but implementations vary wildly:

• PostgreSQL: JSONB is binary, indexable, and the gold standard for SQL JSON.
• MySQL: Native JSON type (binary storage) with limited indexing via generated columns.
• SQL Server: JSON functions on NVARCHAR(MAX). Native JSON type coming in future versions.
• SQLite: JSON stored as TEXT. JSON1 extension provides functions but no native type.
• Oracle: Native JSON support in 12c+ with IS JSON constraint on VARCHAR2/CLOB.

The Interactive Reference

We've built a free, searchable SQL Data Types Reference that maps every common type across all five dialects. It's designed for the moment when you're staring at a pg_dump output and need to know what MySQL type to use.

Open SQL Data Types Reference →

How to Avoid Type Migration Errors

1. Audit every column type before writing migration scripts. Don't assume INTEGER means the same thing everywhere.
2. Check precision and scale on DECIMAL/NUMERIC columns. Rounding behavior differs.
3. Verify Boolean handling in your ORM or query builder. This is the #1 silent breakage.
4. Test timezone behavior on TIMESTAMP columns. What PostgreSQL stores as UTC, MySQL might store as local time.
5. Use a schema diff tool that understands dialect differences. SchemaLens compares schemas semantically, not just textually, and generates migration scripts in your target dialect.