PostgreSQL Sequence Error: Manual Inserts Disrupt Auto-Increment

When manually inserting data with explicit ID values into a PostgreSQL database, the auto-increment sequence doesn't automatically adjust. This can lead to conflicts when your app then tries to insert new rows using auto-generated IDs.

For example, in a Laravel application using a pages table, you might encounter this error:

Illuminate\Database\UniqueConstraintViolationException

SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "pages_pkey"
DETAIL: Key (id)=(4) already exists.

This error occurs because the sequence is trying to use an ID that already exists in the table due to a previous manual insert.

Solution

1. Check the current sequence value:

   SELECT last_value, is_called FROM pages_id_seq;

2. Update the sequence to the maximum existing ID:

   SELECT setval('pages_id_seq', (SELECT COALESCE(MAX(id), 0) FROM pages));

3. Verify the update:

   SELECT last_value, is_called FROM pages_id_seq;

Best Practices

1. Avoid manual inserts with explicit IDs when possible.
2. If manual inserts are necessary, always update the sequence afterwards.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com