# So You Want to Ditch MySQL for PostgreSQL. Let's Talk. *SQLMarrow Academy · Migration Guide · 9 min read* --- Migrating from MySQL to PostgreSQL isn't just a copy-paste job — it's a mindset shift. Here's everything you need to make the move without losing your mind (or your data). ## THE "WHY" ### Why are people even migrating? MySQL is great. It's fast, battle-tested, and runs half the internet. But PostgreSQL — Postgres to friends — has been quietly winning the hearts of developers who care about correctness, standards compliance, and features that actually make you a better SQL writer. Think full JSON/JSONB support, window functions that don't feel like an afterthought, better subquery handling, and a standards-compliant SQL engine that doesn't quietly bend the rules when you're not looking. Plus: it's open source with no Oracle-sized asterisk attached. 💡 PostgreSQL has consistently topped the "most loved database" charts in developer surveys — not because of hype, but because once you use it, going back feels weird. --- ## THE DIFFERENCES THAT BITE YOU ### MySQL vs PostgreSQL: Where things get spicy Before you run a single migration command, you need to know where these two databases quietly disagree — because when they do, they really disagree. | MySQL quirks | PostgreSQL equivalents | |---|---| | Case-insensitive string comparisons by default | Case-sensitive by default (use ILIKE or citext) | | AUTO_INCREMENT for serial IDs | SERIAL or GENERATED AS IDENTITY | | Backtick quoting (`table_name`) | Double-quote quoting ("table_name") | | ENUM stored as strings, not integers | Real ENUM type (or just use TEXT + CHECK) | | Zero-datetime values like 0000-00-00 | No zero dates — use NULL | | TINYINT(1) for booleans | Native BOOLEAN type | ⚠️ **The silent killer:** MySQL lets you insert '2023-02-30' and silently adjusts it. PostgreSQL will reject it outright. Your data may be hiding errors you never knew existed. --- ## STEP BY STEP ### How to actually migrate MySQL to PostgreSQL There are tools, and then there's understanding what those tools do. Both matter. Here's a practical path through the migration process: 1. **Audit your MySQL schema** Export the schema first (`mysqldump --no-data`). Review every column type, every constraint, every ENUM, every default value. This is your map — and maps have landmines. 2. **Use pgloader or a dedicated converter** `pgloader` is the most popular open-source MySQL to PostgreSQL converter. It handles type mapping, charset conversion, and can migrate data live. Run it in a test environment first. Always. 3. **Translate your SQL queries** Your app code has MySQL-specific SQL. `IFNULL` becomes `COALESCE`. `DATE_FORMAT` becomes `TO_CHAR`. `LIMIT X, Y` becomes `LIMIT X OFFSET Y`. Go through every query — especially subqueries. 4. **Migrate data in batches** Avoid one giant dump. Migrate in chunks, verify row counts, run checksums. If you have millions of rows, streaming with `pgloader`'s live mode is your friend. 5. **Test, then cut over** Run your app against the PostgreSQL database in parallel. Log errors. Fix them. Only cut over DNS/connection strings when you're confident. Have a rollback plan ready. ✅ **Pro move:** Use `pgloader`'s `--dry-run` flag first. It'll surface type conflicts, missing indexes, and encoding issues before touching your production data. --- ## SQL SKILL UPGRADE ### Subqueries: where Postgres really shines One of the quiet wins of migrating to PostgreSQL is that your SQL gets better. Postgres has stellar support for correlated subqueries, lateral joins, and CTEs — all things MySQL was historically wobbly on. Here's a practical example. Say you want the most recent order for each customer: #### MYSQL (OLD HABITS) ```sql SELECT * FROM orders o1 WHERE order_date = ( SELECT MAX(order_date) FROM orders o2 WHERE o2.customer_id = o1.customer_id ); ``` #### POSTGRESQL (CLEANER, FASTER) ```sql SELECT DISTINCT ON (customer_id) * FROM orders ORDER BY customer_id, order_date DESC; ``` The `DISTINCT ON` syntax is pure Postgres magic — no subquery needed. And it's fast because Postgres can use your index directly. This is the kind of thing that makes developers fall in love. #### LATERAL SUBQUERY — POSTGRES SUPERPOWER ```sql SELECT c.name, o.total FROM customers c JOIN LATERAL ( SELECT total FROM orders WHERE customer_id = c.id ORDER BY created_at DESC LIMIT 1 ) o ON TRUE; -- reference outer query inside subquery ``` 💡 `LATERAL` joins let a subquery reference columns from the outer query — something that's awkward or impossible in MySQL without correlated subqueries and hacks. It's a game-changer for "top N per group" queries. --- ## TOOLS OF THE TRADE ### MySQL to PostgreSQL converter options You don't have to migrate by hand. Here's your toolkit: - **pgloader** - Open source, battle-tested - Handles type coercions automatically - Supports live migration (no downtime) - Command-line based, scriptable - **AWS DMS** - Great for cloud migrations - Continuous replication mode - GUI-driven, good for teams - Costs money — plan accordingly - **db-migrate / Flyway** - Schema migration frameworks - Version your migrations as code - Great for ongoing changes post-move - **mysqldump + manual** - Full control over every step - Good for small databases - Tedious for large schemas - You'll learn a lot doing it --- ## COMMON GOTCHAS ### The stuff nobody warns you about Everyone covers the basics. Here's the stuff that trips up people who thought they covered the basics: ⚠️ **Sequences don't auto-sync.** After migrating data, your `SERIAL`/`SEQUENCE` values may start from 1 again — causing primary key conflicts on your first `INSERT`. Always reset sequences after data import using `setval()`. ⚠️ **Identifier casing.** MySQL on Windows is case-insensitive for identifiers. PostgreSQL lowercases everything not in double quotes. `SELECT UserID` becomes `userid` in Postgres. Your ORMs might hate this. ⚠️ **GROUP BY is strict.** MySQL lets you SELECT columns not in GROUP BY. PostgreSQL won't. Every column in SELECT that isn't aggregated must be in GROUP BY — which is actually the correct SQL standard behavior. ✅ The strictness feels annoying at first, but it's catching real bugs in your queries. Postgres is basically a better SQL teacher. --- ## WRAPPING UP ### Is it worth it? Every time. The migration from MySQL to PostgreSQL is a few days of focused work for a database that will reward you for years. Better subqueries. Real boolean types. JSONB that's actually fast. A query planner that's genuinely trying to help you. The community is thriving, the extensions ecosystem (PostGIS, pg_vector, timescaledb...) is wild, and you'll write better SQL just by using it. Start with a non-critical database, get comfortable, then move the big ones. The hardest part isn't the migration. It's deciding to start. --- *Need to convert your MySQL schema or queries to PostgreSQL right now? Check out our free, interactive [MySQL to PostgreSQL Converter](https://sqlmarrow.com/convert/mysql-to-postgresql) for instant query translation, detailed type mappings, and step-by-step instructions.*