SQL Data Type Converter

Convert CREATE TABLE column types, identity columns, booleans and defaults between MySQL, PostgreSQL, SQLite, SQL Server and Oracle, with notes on what needs review.

The interactive tool loads here when JavaScript runs.

About this tool

What is the SQL Data Type Converter?

The SQL Data Type Converter rewrites CREATE TABLE statements from one database's types to another's. Pick a source and target among MySQL, PostgreSQL, SQLite, SQL Server and Oracle, paste the DDL, and click Convert Schema. Each column's type is mapped through a neutral type, so all 20 source and target pairs work, not just a few.

It handles more than type names. AUTO_INCREMENT, SERIAL, IDENTITY and SQLite's INTEGER PRIMARY KEY become the target's auto-increment form. Boolean defaults are rewritten (DEFAULT 1 becomes DEFAULT TRUE in PostgreSQL, DEFAULT FALSE becomes DEFAULT 0 in SQL Server). Quoted identifiers switch between backticks and double quotes. MySQL-only pieces such as ENGINE=InnoDB, ON UPDATE CURRENT_TIMESTAMP and inline KEY definitions are removed with a note.

Anything that needs a human decision is listed as a -- Note: comment at the top of the output, for example an ENUM turned into a CHECK constraint or a type the converter does not recognise. Only CREATE TABLE column definitions are converted; other statements pass through with their identifier quotes adjusted. It runs in your browser.

  • All 20 pairs of MySQL, PostgreSQL, SQLite, SQL Server and Oracle
  • Dialect-aware FLOAT, REAL, DATE, TIMESTAMP and boolean handling
  • AUTO_INCREMENT, SERIAL, IDENTITY and INTEGER PRIMARY KEY conversion
  • Boolean and timestamp default rewriting
  • Notes for anything that needs a manual decision
  • Converted in your browser
Read the guide: SQL Data Types Across MySQL, PostgreSQL, SQL Server, SQLite and Oracle

Updated

How to use the SQL Data Type Converter

  1. Choose the Source Database and Target Database.
  2. Paste one or more CREATE TABLE statements into Input Schema.
  3. Click Convert Schema.
  4. Read the -- Note: lines at the top of Converted Schema and fix anything they mention, then click Copy.

Worked examples

MySQL to PostgreSQL

Input

CREATE TABLE `users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `status` ENUM('active','banned') NOT NULL DEFAULT 'active',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Output

CREATE TABLE "users" (
  "id" BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  "is_active" BOOLEAN NOT NULL DEFAULT TRUE,
  "status" VARCHAR(255) NOT NULL DEFAULT 'active' CHECK ("status" IN ('active','banned')),
  "created_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY ("id")
);

INT UNSIGNED widens to BIGINT because PostgreSQL has no unsigned integers and the top half of the range would not fit in INTEGER. TINYINT(1) is read as a boolean, and the ENUM becomes a VARCHAR with a CHECK constraint, with notes for the ENUM and the removed ENGINE option.

PostgreSQL to SQL Server

Input

CREATE TABLE t (id SERIAL PRIMARY KEY, ok BOOLEAN DEFAULT false, j JSONB, u UUID);

Output

CREATE TABLE t (
  id INT IDENTITY(1,1) PRIMARY KEY,
  ok BIT DEFAULT 0,
  j NVARCHAR(MAX),
  u UNIQUEIDENTIFIER
);

SERIAL becomes an IDENTITY column, the boolean default becomes 0, JSON is stored as NVARCHAR(MAX) and UUID maps to UNIQUEIDENTIFIER.

Background and insight

Every database implements the SQL standard types a little differently, and the same name can mean different things. FLOAT is a 4-byte float in MySQL but an 8-byte double in SQL Server and PostgreSQL unless you give a precision of 24 or less. REAL is 4 bytes in PostgreSQL and SQL Server, 8 bytes in SQLite, and a synonym for DOUBLE in MySQL by default. Oracle's DATE stores a time of day as well. The converter reads each type in the context of its source database for exactly this reason.

Booleans are the other usual trap. MySQL's BOOLEAN is TINYINT(1), SQL Server uses BIT, Oracle used NUMBER(1) until 23ai added a native BOOLEAN, and SQLite stores 0 and 1 in an INTEGER. PostgreSQL has a real BOOLEAN that will not accept DEFAULT 1, so a schema copied from MySQL fails until the default is rewritten as TRUE.

Auto-increment columns changed shape over the years. PostgreSQL's SERIAL is shorthand for a sequence; since PostgreSQL 10 the standard GENERATED BY DEFAULT AS IDENTITY is preferred. Oracle gained identity columns in 12c. SQLite only auto-increments a column declared exactly INTEGER PRIMARY KEY. MySQL TIMESTAMP is converted to UTC and back and only covers 1970 to 2038, which is why the converter maps it to timestamptz-style types and DATETIME to plain timestamps.

Common use cases

  • Migrating MySQL to PostgreSQL

    Convert table definitions before loading data, then review the notes for ENUMs and ON UPDATE timestamps.

  • Moving a prototype off SQLite

    Turn a loosely typed SQLite schema into properly sized PostgreSQL or MySQL columns.

  • Supporting several databases

    Keep one schema and generate the variants for each database your product supports.

  • Answering "what is X in Oracle?"

    Paste a single-column table to see the equivalent type in every target.

Tips and limitations

  • Convert the schema, load the data with a proper migration tool (pgloader, AWS DMS, SSMA), then compare row counts and spot-check values.
  • Text lengths in SQL Server NVARCHAR and Oracle VARCHAR2 have hard limits (4,000); longer columns become NVARCHAR(MAX) or CLOB.
  • Check every CHECK constraint the converter adds for ENUMs; you may prefer a lookup table or a PostgreSQL enum type.
  • Indexes declared inline with KEY or INDEX are dropped for non-MySQL targets. Recreate them with CREATE INDEX.
  • Views, triggers, functions and stored procedures are not converted. They need rewriting by hand.

Sources and further reading

  1. Numeric Types, PostgreSQL Global Development Group. REAL, double precision, FLOAT(p) and SERIAL
  2. float and real (Transact-SQL), Microsoft. FLOAT(n) storage sizes in SQL Server
  3. Datatypes In SQLite, SQLite. Type affinity, REAL and Boolean storage
  4. Identity Columns, PostgreSQL Global Development Group. GENERATED BY DEFAULT AS IDENTITY
  5. Data Types, Oracle. Oracle DATE, NUMBER and BOOLEAN

Frequently asked questions

What is the PostgreSQL equivalent of MySQL AUTO_INCREMENT?

An identity column: id INTEGER GENERATED BY DEFAULT AS IDENTITY, available since PostgreSQL 10. SERIAL still works and creates a sequence behind the scenes, but identity columns are the SQL standard form and are easier to manage with permissions and dumps.

How do I store booleans in MySQL, SQL Server and Oracle?

MySQL uses BOOLEAN, which is really TINYINT(1). SQL Server uses BIT with 0 and 1. Oracle traditionally uses NUMBER(1) with a CHECK constraint, and Oracle 23ai adds a native BOOLEAN. PostgreSQL has a true BOOLEAN that rejects integer defaults.

Is MySQL DATETIME the same as PostgreSQL TIMESTAMP?

Yes, both store a date and time with no time zone. MySQL TIMESTAMP is different: it converts to UTC on write and back to the session zone on read, and only covers 1970 to 2038, so it is closer to PostgreSQL TIMESTAMPTZ. The converter maps them that way.

Why did my INT UNSIGNED become BIGINT?

PostgreSQL, SQL Server, SQLite and Oracle have no unsigned integers. An unsigned INT goes up to about 4.29 billion, which does not fit in a signed 4-byte INTEGER, so the converter widens it to the next size to keep every existing value valid.

What happens to MySQL ENUM columns in PostgreSQL?

They become VARCHAR(255) with a CHECK (column IN (...)) constraint, and a note is added. That is the most portable form. In PostgreSQL you could instead create an enum type with CREATE TYPE, or use a lookup table with a foreign key.

Does the converter migrate data or stored procedures?

No. It converts CREATE TABLE column definitions and passes other statements through with identifier quotes adjusted. Use a migration tool such as pgloader or SQL Server Migration Assistant for data, and rewrite procedures, triggers and views by hand.

Is there an in-depth guide to the SQL Data Type Converter?

Yes. “SQL Data Types Across MySQL, PostgreSQL, SQL Server, SQLite and Oracle” covers the background, how it works, common pitfalls and worked examples. Read the full guide.