duplicate key value violates unique constraint "users_pkey"

Laravel app, just moved from MySQL to PostgreSQL. The full message as Laravel shows it:

SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "users_pkey"

Why it happens

You copied rows from MySQL with their ids, but the Postgres sequence behind each id column still starts at 1. The first new insert reuses id 1, 2, 3... If your ids have gaps, it fails some inserts and not others, so it looks random.

Fix

-- once per table that has a serial/identity id: SELECT setval(pg_get_serial_sequence('users','id'), COALESCE((SELECT MAX(id) FROM users), 0) + 1, false);

Tested on Postgres 17.11: after the setval, the next insert gets MAX(id)+1. Running it again is safe. If the key column is named differently, change 'id'.

The tested fix for every table at once (scripts, a Laravel command, before/after checks): Laravel Postgres Error Fix Pack ($19), item 01.

Several errors at once? Paste your log into the error matcher. Raw SQL with MySQL syntax: the free SQL translator. Not migrated yet: the free schema risk check finds this kind of problem before the switch. Planning the whole move: Migration Kit ($49, $34.30 with LAUNCH30 until Nov 5).

Other errors after MySQL to Postgres

Published with deed.page