Laravel on Postgres: "duplicate key value violates unique constraint" after importing from MySQL (sequence reset fix)
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.
You moved a Laravel app from MySQL to Postgres, the import finished, row counts match, and then the first User::create() dies with:
Want the fix scripts ready to run? The Laravel Postgres Error Fix Pack ($19) has tested scripts for this error and five others (sequence reset, TIMESTAMP shift, GROUP BY, boolean = integer, zero dates, unsigned overflow).
SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "users_pkey" DETAIL: Key (id)=(1) already exists.
The data is fine. The sequence behind the id column is still at 1. This page has the cause, a one-shot fix for every table, and how to check it worked. Tested 26 Oct 2026 on PostgreSQL 17.11 with Laravel 13.38 ($table->id() and increments()). From TuskShift (brand account; written with AI help, every command below was run by hand).
Why it happens
On pgsql, Laravel's id() and increments() create a column whose default is nextval('users_id_seq'). When rows arrive with their ids already set (a MySQL dump, CSV + COPY, an INSERT-per-row script, a seeder that copies ids), Postgres stores the ids but never touches the sequence. MySQL's AUTO_INCREMENT counter doesn't come across.
Why it looks random
A failed insert still uses up a sequence value. With ids 1, 2 and 4 in the table, five inserts in a row gave: fail, fail, 3, fail, 5. So some requests work and some 500, and it "fixes itself" for a while each time it lands in a gap. Don't wait it out: on a table with ids up to 1,000,000 that's up to a million failed writes.
Fix one table
SELECT setval(pg_get_serial_sequence('users', 'id'), COALESCE(MAX(id), 0) + 1, false) FROM users;The false means "the next nextval returns exactly this value", so an empty table starts at 1 instead of 2.
Fix every table at once
Run this once after the data load and before writes resume (psql -f, or DB::unprepared() in a one-off artisan command). It finds every column backed by a sequence (serial and identity), and sets each one to MAX + 1. It's safe to run again.
DO $$
DECLARE r record;
BEGIN
FOR r IN
SELECT c.table_schema AS s, c.table_name AS t, c.column_name AS col,
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.col, r.s, r.t);
END LOOP;
END $$;Test results: users (ids up to 1000) gave 1001 on the next insert, orders (increments(), max 42) gave 43, a GENERATED BY DEFAULT AS IDENTITY table (max 5) gave 6, an empty table gave 1, and a second run changed nothing. Change 'public' if your tables live in another schema.
Check it worked
SELECT last_value, is_called FROM users_id_seq; -- should be MAX(id)+1 and false
Did my tool already do it?
- pgloader with its default MySQL options resets sequences at the end of the load ("reset sequences" is in the default WITH clause), so you only hit this if you turned that off or loaded data another way.
- Plain dumps, CSV +
COPYand custom PHP copy scripts don't, so run the block above.
The other traps that pass row counts
Sequences are one of several things that look fine until production traffic hits: "must appear in the GROUP BY clause" errors, case-sensitive LIKE and email logins, TIMESTAMP values shifted by the server's UTC offset, unsigned ints over 2,147,483,647, zero dates. The free TuskShift checker v2 (one PHP file, read-only, MIT) scans your code and MySQL data for those in about two minutes. Also see: timestamps off by hours after the move (TIMESTAMP zone shift).
The paid TuskShift Migration Kit ($49) covers the whole move: 52 code rules, data preflight, resumable copy, a sequence-reset script, per-row checksum verify and a cutover playbook. Code LAUNCH30 takes 30% off until 5 Nov: tuskshift.gumroad.com/l/tuskshift/LAUNCH30.
Not affiliated with Laravel, MySQL, MariaDB or PostgreSQL.
Want the fix scripts ready to run? The Laravel Postgres Error Fix Pack ($19) has tested scripts for this error and five others (sequence reset, TIMESTAMP shift, GROUP BY, boolean = integer, zero dates, unsigned overflow).