ERROR database constraint_error ai_generated true

psycopg2.errors.UniqueViolation: ERROR: duplicate key value violates unique constraint "my_table_pkey"

ID: database/unique-violation

Also available as: JSON · Markdown
92%Fix Rate
94%Confidence
78Evidence
2023-01-01First Seen

Version Compatibility

VersionStatusIntroducedDeprecatedNotes
16 active — — —

Root Cause

An INSERT or UPDATE attempted to create a duplicate value in a column with a unique constraint or primary key. Often caused by sequence desynchronization, race conditions in concurrent inserts, or missing upsert logic.

generic

Workarounds

  1. 94% success Use INSERT ... ON CONFLICT (upsert) to handle duplicates gracefully 
    Rewrite the INSERT as: INSERT INTO my_table (id, col) VALUES (val1, val2) ON CONFLICT (id) DO UPDATE SET col = EXCLUDED.col; or ON CONFLICT DO NOTHING if duplicates should be skipped.
  2. 90% success Reset the sequence if primary key serial/identity column is out of sync 
    Run: SELECT setval('my_table_id_seq', (SELECT MAX(id) FROM my_table)); This resynchronizes the sequence with the actual maximum value in the table, which often happens after bulk data imports.

Dead Ends

Common approaches that don't work:

  1. Dropping the unique constraint to avoid the error 85% fail

    The unique constraint exists to enforce data integrity. Removing it allows duplicate data which will cause subtle and hard-to-debug application errors downstream.

  2. Catching the exception and silently ignoring it without handling the duplicate 80% fail

    Silently swallowing the error means the intended data was not inserted or updated. This causes data loss or inconsistency without any user-visible indication.

Error Chain

Leads to:
Preceded by:
Frequently confused with: