MySQL to PostgreSQL DDL Conversion: Complete Syntax Guide

Why MySQL to PostgreSQL Migrations Fail at the DDL Stage

Most MySQL to PostgreSQL migrations stall before a single row of data moves. The blocker is DDL: your CREATE TABLE statements, data types, constraints, and default expressions are all MySQL-specific. PostgreSQL rejects them.

This guide covers every DDL translation you'll hit during a MySQL to PostgreSQL migration, with exact syntax you can use directly.

---

Data Type Conversions

Numeric Types

String Types

Handling ENUM:

-- MySQL
status ENUM('active', 'inactive', 'pending')

-- PostgreSQL option 1: CHECK constraint
status VARCHAR(10) NOT NULL CHECK (status IN ('active', 'inactive', 'pending'))

-- PostgreSQL option 2: CREATE TYPE
CREATE TYPE status_type AS ENUM ('active', 'inactive', 'pending');

Date and Time Types

Binary and JSON Types

---

Auto-Increment and Sequences

-- MySQL
CREATE TABLE member (
    member_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);

-- PostgreSQL (modern — preferred)
CREATE TABLE member (
    member_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

---

String Functions That Change

---

Index Syntax Differences

-- MySQL (inline indexes)
CREATE TABLE claim (
    claim_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    member_id INT NOT NULL,
    INDEX idx_member (member_id)
);

-- PostgreSQL (indexes are separate statements)
CREATE TABLE claim (
    claim_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    member_id INT NOT NULL
);
CREATE INDEX idx_member ON claim (member_id);

---

Case Sensitivity: The Silent Killer

PostgreSQL folds unquoted identifiers to lowercase. MySQL on macOS/Windows is case-insensitive by default.

Rule: never use quoted identifiers in PostgreSQL DDL. Convert mixed-case column names (MemberID, ClaimDate) to snake_case (member_id, claim_dt) during migration.

---

A Full MySQL → PostgreSQL Translation Example

-- ORIGINAL MySQL DDL
CREATE TABLE member (
    MemberID INT NOT NULL AUTO_INCREMENT,
    FirstName VARCHAR(100) NOT NULL,
    DateOfBirth DATE NOT NULL,
    Gender ENUM('M', 'F', 'U') NOT NULL DEFAULT 'U',
    CreatedAt DATETIME NOT NULL DEFAULT NOW(),
    UpdatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (MemberID),
    INDEX idx_plan (PlanID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- TRANSLATED PostgreSQL DDL
CREATE TABLE member (
    member_id     INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name    VARCHAR(100) NOT NULL,
    date_of_birth DATE NOT NULL,
    gender        VARCHAR(1) NOT NULL DEFAULT 'U' CHECK (gender IN ('M', 'F', 'U')),
    created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_member_plan ON member (plan_id);

Changes made: AUTO_INCREMENT → IDENTITY, mixed-case → snake_case, ENUM → CHECK constraint, DATETIME → TIMESTAMP, ENGINE/CHARSET clauses removed.

---

Automating DDL Conversion

For a schema with 50+ tables, use the DDL Converter tool to convert MySQL DDL to PostgreSQL automatically. After conversion, run the output through the SQL Linter to catch any remaining issues.

---

Migration Checklist

• AUTO_INCREMENT → GENERATED ALWAYS AS IDENTITY
• ENUM → VARCHAR + CHECK or CREATE TYPE
• TINYINT(1) → BOOLEAN
• BLOB → BYTEA
• JSON → JSONB
• Mixed-case identifiers → snake_case
• Inline indexes extracted to separate CREATE INDEX statements
• MySQL-specific functions replaced in views and stored procedures
• CHARACTER SET / ENGINE clauses removed
• Sequence starting values set to match migrated data max IDs