PostgreSQL vs MySQL: Schema Migration Gotchas

If you have ever moved a schema from PostgreSQL to MySQL—or vice versa—you have probably stared at an error message that made absolutely no sense. "Syntax error near 'AUTO_INCREMENT'". "Type 'serial' does not exist". The databases look similar on the surface, but their DDL dialects diverge in subtle, maddening ways.

Here are eight gotchas that break migrations when you switch between PostgreSQL and MySQL, and how to avoid them.

1. Auto-increment syntax is completely different

PostgreSQL uses SERIAL or GENERATED ALWAYS AS IDENTITY. MySQL uses AUTO_INCREMENT. SQLite uses INTEGER PRIMARY KEY AUTOINCREMENT. These are not interchangeable.

2. Boolean vs TINYINT(1)

PostgreSQL has a native BOOLEAN type. MySQL accepts BOOLEAN in CREATE TABLE, but silently rewrites it to TINYINT(1). This is not just cosmetic—ORMs and drivers behave differently depending on which type they see.

-- PostgreSQL
CREATE TABLE posts (
  published BOOLEAN DEFAULT false
);

-- MySQL (what you actually get)
CREATE TABLE posts (
  published TINYINT(1) DEFAULT 0
);

If you diff a PostgreSQL schema against a MySQL schema that was originally created from the same logical design, SchemaLens will flag this as a type change even though semantically they are identical. This is exactly why semantic diffing matters: you need to know whether a change is meaningful or just dialect noise.

3. VARCHAR length enforcement

MySQL requires a length on VARCHAR (e.g., VARCHAR(255)). PostgreSQL also supports length limits, but you can use TEXT everywhere without performance penalty. MySQL historically stored TEXT off-row, which had performance implications, though InnoDB has improved this.

When migrating from PostgreSQL to MySQL, you have to decide: convert every TEXT to VARCHAR(65535)? Or keep TEXT and accept the storage differences? SchemaLens flags these mismatches so you can review them one by one.

4. Quoted identifiers: backticks vs double quotes

MySQL uses backticks for identifiers: `user name`. PostgreSQL uses double quotes: "user name". SQL Server uses square brackets: [user name]. SQLite accepts double quotes (or backticks in some modes).

If you copy-paste a MySQL schema into a PostgreSQL console without converting quotes, you will get cryptic errors. A good schema diff tool normalizes identifiers before comparing them, so `users` and "users" are treated as the same table.

5. ALTER TABLE capabilities diverge wildly

Not all ALTER TABLE operations are created equal. Here is a quick compatibility matrix:

SQLite is the most restrictive: dropping a column, renaming a column, or changing a type all require recreating the entire table with the new structure and copying data over. SchemaLens handles this by generating the full table-recreation script for SQLite instead of a naive ALTER TABLE that would fail.

6. Default values and function expressions

PostgreSQL allows rich expressions in defaults: DEFAULT NOW(), DEFAULT GEN_RANDOM_UUID(), DEFAULT CURRENT_TIMESTAMP. MySQL supports CURRENT_TIMESTAMP and a few others, but not arbitrary expressions in DEFAULT clauses (this changed slightly in MySQL 8.0.13, but it is still limited).

-- PostgreSQL (works)
CREATE TABLE users (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- MySQL (fails without workaround)
CREATE TABLE users (
  id CHAR(36) DEFAULT (UUID()) PRIMARY KEY,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

7. Foreign key constraint naming

In MySQL, foreign key constraints must be uniquely named per database, not per table. In PostgreSQL, constraint names only need to be unique per table. If you dump two PostgreSQL schemas with the same FK constraint name on different tables and import them into MySQL, the second import will fail.

SchemaLens detects constraint name collisions during diffing and warns you before you generate a migration that MySQL will reject.

8. Case sensitivity: tables, columns, and data

PostgreSQL folds unquoted identifiers to lowercase. MySQL preserves case on Windows but folds to lowercase on Linux (unless configured with lower_case_table_names=0). This means a table named Users in MySQL might become users on a Linux production server, but stay Users on your MacBook.

The safest approach: use lowercase, snake_case identifiers everywhere, and quote nothing unless you absolutely have to. SchemaLens normalizes unquoted identifiers to lowercase before diffing, so case differences do not create false positives.

How to catch these before they break production

1. Dump both schemas with the same tool (pg_dump --schema-only or mysqldump --no-data).
2. Run a semantic diff, not a line-by-line text diff. Line diffs drown you in syntax noise.
3. Review type changes manually—some are dialect equivalents (BOOLEAN vs TINYINT), others are real semantic shifts (INT → BIGINT).
4. Generate the migration in your target dialect, review it in staging, and run it.
5. Verify by diffing the post-migration schema against your expected schema.

Written by the SchemaLens team. We build tools that make database migrations less terrifying. Read more articles →