2026-09-29 · 6 min read · Rahul Chitturi
- sql
- postgresql
- schema
A PostgreSQL identity column draws new values from a sequence that the column owns. GENERATED BY DEFAULT lets an insert supply an explicit value. GENERATED ALWAYS rejects that value unless the statement overrides system generation.
Duplicate-key errors after a restore, and permission errors on a table that clearly allows INSERT, often come from the sequence rather than from the row itself.
Identify the column and its sequence
Record the table, column, identity type, data type, sequence name, owner, last value, and whether the sequence is called when the statement omits the column. serial and identity both use sequences, but identity is the standard form and its privileges are tied to the table.
- Check OVERRIDING SYSTEM VALUE and OVERRIDING USER VALUE.
- Compare the sequence position with the current maximum key.
- Note whether a copy or import inserted explicit keys.
- Confirm the session role can use the identity sequence.
Explain gaps before resetting anything
Rolled-back transactions, failed inserts, and cached sequence values leave gaps. Those gaps are normal and are not evidence that rows disappeared. Restarting a sequence to fill them can collide with existing keys or with another writer.
After pg_restore, logical replication, or a failover import, set the sequence only when you have measured it against the live maximum and taken a lock that prevents concurrent inserts.
Make the next insert deterministic
Use Flashman's SQL formatter for catalog queries, the timestamp converter when you correlate import jobs, the JSON formatter for a redacted row sample, and diff to compare schema definitions across environments.
Test default inserts, explicit keys, ALWAYS without an override, a restore that includes the maximum key, and a role that can insert into the table but should not call the sequence directly.