MySQL to PostgreSQL migration assistant

The most impactful type differences are: **AUTO_INCREMENT** becomes GENERATED ALWAYS AS IDENTITY (PostgreSQL 10+) or SERIAL — identity columns are preferred because they prevent manual value insertion by default, avoiding sequence desync. **TINYINT(1)** should map to BOOLEAN, not SMALLINT, since MySQL commonly uses it as a boolean flag — but verify your application does not store values other than 0 and 1. **DATETIME** maps to TIMESTAMP (without time zone) and **TIMESTAMP** maps to TIMESTAMPTZ (with time zone) — but be aware that MySQL's TIMESTAMP auto-converts to UTC on storage, while PostgreSQL's TIMESTAMPTZ stores the UTC value and converts on display based on the session timezone setting. **ENUM** types exist in both systems but behave differently — MySQL enums are stored as integers internally and allow empty-string values, while PostgreSQL enums are true types that must be created separately with CREATE TYPE. **UNSIGNED** integers do not exist in PostgreSQL; use the next larger type or add a CHECK (column >= 0) constraint. **MEDIUMTEXT**, **LONGTEXT**, and **TINYTEXT** all become plain TEXT in PostgreSQL, which has no practical size limit. **DOUBLE** becomes DOUBLE PRECISION, and **FLOAT** becomes REAL.

MySQL stored procedures use a SQL/PSM-derived language, while PostgreSQL uses PL/pgSQL. The key structural differences: MySQL uses DELIMITER to change the statement terminator — PostgreSQL does not need this because function bodies are wrapped in $$ dollar-quoted strings. MySQL DECLARE statements for variables go in the BEGIN...END block — PL/pgSQL has a separate DECLARE section before BEGIN. MySQL error handlers (DECLARE EXIT HANDLER FOR SQLEXCEPTION) become PL/pgSQL EXCEPTION blocks inside BEGIN...EXCEPTION...END. MySQL OUT and INOUT parameters work similarly in PostgreSQL, but idiomatic PostgreSQL functions often return TABLE or a composite type instead. MySQL LEAVE and ITERATE (for loop control) map to EXIT and CONTINUE in PL/pgSQL. MySQL's SELECT ... INTO syntax is the same in PL/pgSQL, but SELECT without INTO inside a PL/pgSQL function is an error — use PERFORM for queries whose results you want to discard. Transaction control (COMMIT, ROLLBACK) inside PL/pgSQL requires PROCEDURE (not FUNCTION) and PostgreSQL 11+. In earlier versions, procedures cannot manage their own transactions — the caller must handle it.

**pgloader** is the most popular open-source tool for MySQL-to-PostgreSQL migrations. It reads directly from a running MySQL instance, converts schemas automatically (including data types, indexes, and foreign keys), loads data using PostgreSQL COPY for speed, and creates indexes in parallel after the data load. It handles most type conversions out of the box and allows custom transformation rules via its command language. For databases under 100 GB with a maintenance window, pgloader is usually the best choice. **pg_chameleon** provides logical replication from MySQL to PostgreSQL, allowing you to keep both databases in sync during a transition period and cut over with minimal downtime — ideal for large databases or zero-downtime requirements. **AWS Database Migration Service (DMS)** works well if both databases are in AWS and you want a managed solution, though it has limitations with complex stored procedures and certain data types. For very large databases (multi-TB), consider using **mydumper** to export data in parallel, then **pgloader** or custom scripts to load it. Always run the migration against a test environment first, then validate row counts, checksums, and application behavior before cutting over production.

Several MySQL syntax patterns need rewriting for PostgreSQL. **Identifier quoting**: MySQL uses backticks (` `), PostgreSQL uses double quotes ("), though the best practice is to use unquoted lowercase identifiers that are valid in both systems. **LIMIT with offset**: LIMIT 10, 20 in MySQL becomes LIMIT 20 OFFSET 10 in PostgreSQL — note the reversed order. **String concatenation**: MySQL's CONCAT() works in PostgreSQL, but the || operator is more idiomatic. **IFNULL**: becomes COALESCE (which is standard SQL and works in both). **GROUP_CONCAT**: becomes STRING_AGG(column, ',') in PostgreSQL, with ORDER BY supported inside the aggregate. **INSERT ... ON DUPLICATE KEY UPDATE**: becomes INSERT ... ON CONFLICT (...) DO UPDATE SET ... in PostgreSQL (9.5+). **DATE_FORMAT**: becomes TO_CHAR(timestamp, format) with different format specifiers (%Y-%m-%d becomes YYYY-MM-DD). **REPLACE INTO**: has no direct equivalent; use INSERT ... ON CONFLICT DO UPDATE instead. **SHOW TABLES**, **DESCRIBE**: use \dt in psql or query information_schema.tables and information_schema.columns. **AUTO_INCREMENT value after INSERT**: MySQL's LAST_INSERT_ID() becomes currval('sequence_name') or use INSERT ... RETURNING id`.