PostgreSQL vs MySQL: Key DDL Differences You Must Know Before Migrating

Migrating a database from PostgreSQL to MySQL (or vice versa) is one of the most common database migration tasks. While both are relational databases with SQL syntax, there are significant differences in their DDL (Data Definition Language) that will cause syntax errors if you try to run PostgreSQL scripts directly in MySQL — or the other way around.

This guide covers the most important differences you need to know before migrating.

Auto-Increment: SERIAL vs AUTO_INCREMENT

The most common difference you'll hit immediately:

PostgreSQL	MySQL
SERIAL PRIMARY KEY	INT AUTO_INCREMENT PRIMARY KEY
BIGSERIAL PRIMARY KEY	BIGINT AUTO_INCREMENT PRIMARY KEY
SMALLSERIAL	SMALLINT AUTO_INCREMENT

PostgreSQL's SERIAL is a shorthand that creates an integer column with a default sequence. MySQL uses the AUTO_INCREMENT keyword appended to the column definition. You cannot use SERIAL in MySQL.

Boolean: BOOLEAN vs TINYINT(1)

PostgreSQL has a native BOOLEAN type that stores TRUE/FALSE/NULL. MySQL does not — it uses TINYINT(1) as a convention where 1 = true and 0 = false:

-- PostgreSQL
is_active BOOLEAN DEFAULT TRUE

-- MySQL equivalent
is_active TINYINT(1) DEFAULT 1

MySQL does accept BOOLEAN as a synonym for TINYINT(1), but it stores values as 0/1. Your application code needs to handle this conversion.

Text Types: TEXT vs LONGTEXT

PostgreSQL's TEXT type has unlimited length. MySQL's TEXT is limited to 65,535 bytes. When migrating from PostgreSQL to MySQL, you should use LONGTEXT to preserve the unlimited nature of the column:

PostgreSQL	MySQL	Max Length
TEXT	LONGTEXT	4 GB
VARCHAR(n)	VARCHAR(n)	65,535 bytes
N/A	TINYTEXT	255 bytes
N/A	MEDIUMTEXT	16 MB

JSON: JSONB vs JSON

PostgreSQL has two JSON types: JSON (stores text verbatim) and JSONB (stores binary-parsed JSON with indexing support). MySQL 5.7+ has a JSON type but nothing equivalent to JSONB:

-- PostgreSQL
metadata JSONB,
data JSON

-- MySQL equivalent
metadata JSON,  -- No binary JSON or GIN index support
data JSON

You lose PostgreSQL's GIN indexing on JSONB when migrating to MySQL. If you rely on @> (contains), ? (key exists), or other JSONB operators, you'll need to rewrite those queries.

UUID: Native Type vs CHAR(36)

PostgreSQL has a native UUID column type. MySQL (before 8.0) does not — the convention is to store UUIDs as CHAR(36) strings or BINARY(16) for efficiency:

-- PostgreSQL
id UUID DEFAULT gen_random_uuid() PRIMARY KEY

-- MySQL (as CHAR)
id CHAR(36) DEFAULT (UUID()) PRIMARY KEY

-- MySQL (as BINARY — more efficient)
id BINARY(16) DEFAULT (UUID_TO_BIN(UUID())) PRIMARY KEY

Timestamps: TIMESTAMP vs DATETIME

PostgreSQL's TIMESTAMP WITH TIME ZONE stores UTC and auto-converts to the session timezone. MySQL's TIMESTAMP also stores UTC but has a year-2038 problem (32-bit unix timestamp limit). For MySQL, prefer DATETIME for future-proof storage:

PostgreSQL	MySQL Equivalent	Caveat
TIMESTAMP	DATETIME	MySQL DATETIME has no timezone awareness
TIMESTAMP WITH TIME ZONE	DATETIME	Store UTC explicitly; MySQL TIMESTAMP has 2038 limit
DATE	DATE	Identical
TIME	TIME	Identical

BYTEA vs BLOB

PostgreSQL stores binary data as BYTEA. MySQL uses BLOB (or LONGBLOB for large data):

-- PostgreSQL
avatar BYTEA

-- MySQL
avatar LONGBLOB

ENUM Types

Both PostgreSQL and MySQL support ENUM, but the syntax differs:

-- PostgreSQL: create a type first (reusable)
CREATE TYPE status_type AS ENUM ('active', 'inactive', 'pending');
ALTER TABLE users ADD COLUMN status status_type;

-- MySQL: inline definition
status ENUM('active', 'inactive', 'pending')

PostgreSQL's ENUM types are reusable objects. MySQL's ENUMs are defined inline per column. When migrating from MySQL to PostgreSQL, you'll need to extract all ENUM definitions into CREATE TYPE statements first.

Double Quotes vs Backticks

PostgreSQL uses double quotes to quote identifiers: "column_name". MySQL uses backticks: `column_name`. If you have reserved words as column names, you'll need to replace the quoting characters when migrating.

Quick Migration Checklist

When converting PostgreSQL DDL to MySQL:

1. Replace SERIAL with INT AUTO_INCREMENT
2. Replace BOOLEAN with TINYINT(1)
3. Replace TEXT with LONGTEXT
4. Replace JSONB with JSON
5. Replace UUID with CHAR(36)
6. Replace BYTEA with LONGBLOB
7. Replace TIMESTAMP WITH TIME ZONE with DATETIME
8. Remove PostgreSQL-specific ENUM type definitions
9. Replace double-quote identifiers with backtick identifiers
10. Add DEFAULT CHARSET=utf8mb4 to each table

Use SchemaForge's free PostgreSQL to MySQL converter to handle all of these transformations automatically. Paste your PostgreSQL DDL and get MySQL-compatible syntax instantly — no upload, no account required.