Duplicate key: PostgreSQL 23505 vs MySQL 1062
The same problem — a unique/primary key collision — reported differently by the two databases, with different upsert syntax and sequence fixes.
PG 23505
PostgreSQL: ERROR: duplicate key value violates unique constraint
An INSERT/UPDATE would create a duplicate in a UNIQUE or PRIMARY KEY column — or the id sequence is behind the existing data.
Causes & fixes →
MySQL 1062
MySQL ERROR 1062 (23000): Duplicate entry for key
An INSERT/UPDATE would create a duplicate value in a PRIMARY or UNIQUE key.
Causes & fixes →| PG 23505 | MySQL 1062 | |
|---|---|---|
| Message | duplicate key value violates unique constraint "…" | Duplicate entry '…' for key '…' |
| Upsert | INSERT … ON CONFLICT (col) DO UPDATE | INSERT … ON DUPLICATE KEY UPDATE |
| Id out of sync fix | SELECT setval(pg_get_serial_sequence('t','id'), MAX(id)) FROM t | ALTER TABLE t AUTO_INCREMENT = max+1 |
| SQLSTATE | 23505 | 23000 |
Rule of thumb
Read which key collided; if it is the id, fix the sequence/auto-increment; otherwise use an upsert or show “already exists”.