Fixing Non-Incrementing Primary Key in PostgreSQL

Problem

While inserting data into a PostgreSQL table, I got this error:

ERROR: Key (“userId“)=(1234) already exists.duplicate key value violates unique constraint “User_pkey“
SQL state: 23505 Details: Key (“userId“)=(1234) already exists.

The userId column was supposed to auto-increment, but it wasn’t.

Solution

-- Checked max userId in the table
SELECT MAX("userId") FROM "User";

-- Reset the sequence to match the max userId:
SELECT setval('user_id_seq', (SELECT MAX("userId") FROM "User"));

Result

The issue was fixed. New inserts worked correctly, and id started auto-incrementing again without conflicts.

Why Primary Key Auto-Increment Fails in PostgreSQL

This happens when you manually insert values into the userId column, which causes the sequence to fall behind. PostgreSQL then tries to use an existing ID again, triggering a duplicate key error.