Laravel on Postgres: the two bugs you get in the first hour after a MySQL migration

Laravel on Postgres: the two bugs you get in the first hour after a MySQL migration

TuskShift
New, 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:

  1. "I can't log in." The user typed Ann@Example.com; the row says ann@example.com.
  2. 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 next nextval returns exactly this", so an empty table starts at 1 instead of 2.
  • pg_get_serial_sequence finds both serial and 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 equal SELECT 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');  -- 1

That 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:

  • citext columns: 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: LIKE on 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: inserting ann@example.com next to Ann@Example.com now 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

Report Page