Laravel on Postgres: the two bugs you get in the first hour after a MySQL migration
TuskShiftNew, free, in your browser: paste your SHOW CREATE TABLE and see what breaks on Postgres (int unsigned overflow, TIMESTAMP shift, _ci collations, sequences, tinyint(1), ENUM/SET, zero dates, FULLTEXT). Nothing is uploaded.
Update 26 Oct: checker v2 is out (adds TIMESTAMP time-zone shift, increments() headroom and bigint unsigned checks): free checker v2 + full migration checklist.
Already on Postgres and hitting errors? The Laravel Postgres Error Fix Pack ($19) has a re-runnable fix and short guide for the six errors that show up first: duplicate key on insert (sequence reset), created_at off by hours, GROUP BY 42803, boolean = integer, zero dates, unsigned overflow. Each fix was run on PostgreSQL 17 + MariaDB 11.8 before release.
Your MySQL-to-Postgres copy finished, row counts match on every table, the test suite is green, and you flip DB_CONNECTION to pgsql. Within the hour you have two kinds of bug report:
- "I can't log in." The user typed
Ann@Example.com; the row saysann@example.com. SQLSTATE[23505]: Unique violation: duplicate key value violates unique constraint "orders_pkey"on the first new order.
Neither shows up in tests, because test databases are seeded fresh and tests use lowercase fixtures. Both are fixable in a few minutes if you know to look. Here is what is happening and the exact SQL, run against PostgreSQL 16 while writing this.
1. Every id insert fails after a bulk copy
When you load rows with their original ids (pgloader, a CSV COPY, a hand-rolled chunked copy), Postgres writes the id values you give it. It never touches the sequence behind bigserial / GENERATED ... AS IDENTITY. The sequence still says 1. The first INSERT without an id asks the sequence for 1, and row 1 already exists.
MySQL hides this because AUTO_INCREMENT moves itself past whatever you insert. Postgres doesn't.
Fix it in one pass after the data load and before you open writes. This resets every sequence-owned column in the schema to MAX(id) + 1:
DO $$
DECLARE r record;
BEGIN
FOR r IN
SELECT c.table_schema, c.table_name, c.column_name,
pg_get_serial_sequence(format('%I.%I', c.table_schema, c.table_name), c.column_name) AS seq
FROM information_schema.columns c
WHERE c.table_schema = 'public'
AND pg_get_serial_sequence(format('%I.%I', c.table_schema, c.table_name), c.column_name) IS NOT NULL
LOOP
EXECUTE format('SELECT setval(%L, COALESCE((SELECT MAX(%I) FROM %I.%I), 0) + 1, false)',
r.seq, r.column_name, r.table_schema, r.table_name);
END LOOP;
END $$;With ids 1, 2 and 500 loaded, the next insert gets 501. Notes:
setval(..., false)means "the nextnextvalreturns exactly this", so an empty table starts at 1 instead of 2.pg_get_serial_sequencefinds bothserialand identity columns, so you don't need a list of sequence names (pgloader and Laravel name them differently).- Run it again if you do a final delta sync after a dry run. The sequences are only correct for the data that was there when you ran it.
- Check it took:
SELECT last_value FROM orders_id_seq;should equalSELECT MAX(id) FROM orders;once the next insert happens.
2. Email logins become case-sensitive
MySQL's default collations (utf8mb4_unicode_ci, utf8mb4_0900_ai_ci) are case-insensitive, so WHERE email = 'ann@example.com' matches Ann@Example.com, and a UNIQUE index on email blocks both from existing at once. Postgres compares bytes. On a copied table:
SELECT count(*) FROM users WHERE email = 'ann@example.com'; -- 0
SELECT count(*) FROM users WHERE lower(email) = lower('ann@example.com'); -- 1That breaks Auth::attempt(), password reset lookups, "email already taken" validation, and anything else that matches on a user-typed string. Same story for where('name', 'like', "%{$q}%"): LIKE is case-sensitive on Postgres, so search quietly returns less.
Before you copy, check for rows that would collide once case matters. On MySQL:
SELECT LOWER(email), COUNT(*) FROM users GROUP BY LOWER(email) HAVING COUNT(*) > 1;
On a _ci collation that returns nothing (the unique index prevented duplicates), but run it after any data cleanup and on every column with a _ci unique index you plan to make case-insensitive on Postgres.
On Postgres, pick one:
citextcolumns:CREATE EXTENSION citext; ALTER TABLE users ALTER COLUMN email TYPE citext;Equality and unique indexes become case-insensitive and your Eloquent code doesn't change. Downside:LIKEon citext is still worth testing, and the extension has to exist in every environment, including CI.- A functional unique index plus lowercase lookups:
CREATE UNIQUE INDEX users_email_lower ON users (lower(email));, then normalise input (Str::lower($request->email)) in your login and registration paths. More code changes, no extension. Verified: insertingann@example.comnext toAnn@Example.comnow fails with a unique violation, just as it did on MySQL.
For search, Laravel 11+ has ->whereLike('name', "%{$q}%"), which compiles to ilike on Postgres and like on MySQL. On older versions, branch on DB::getDriverName().
Find the rest before go-live
These two are the noisiest, but most of the others are the same kind of thing: they pass a row count. Zero dates, NUL bytes in text, tinyint(1) columns holding a 2, unsigned ints over 2,147,483,647, TIMESTAMP values shifting by the server's offset, foreign keys dropped by a data-only pgloader run.
There's a free single-file checker for the common ones here: 2-minute MySQL to Postgres readiness check. The full toolkit (scanner, read-only preflight, resumable copy, per-row checksum verification, sequence and FK scripts, cutover playbook) is the TuskShift Migration Kit.
Fixing errors on a live app right now: Error Fix Pack, $19.
Related error pages: duplicate key value violates unique constraint after import · timestamps off by hours after migration · must appear in the GROUP BY clause
Not affiliated with Laravel, PostgreSQL, MySQL or MariaDB.
Doing this migration for real? The TuskShift Migration Kit has the full runbook, data-validation SQL and cutover checklist. Launch price: 30% off with code LAUNCH30 until Nov 5 → https://tuskshift.gumroad.com/l/tuskshift/LAUNCH30 . Also want metrics on Timescale? Kit + Timescale Metrics Kit bundle: https://tuskshift.gumroad.com/l/fmfnj