PostgreSQL Sequence Reset After AUTO_INCREMENT Migration

The post-load step every AUTO_INCREMENT → IDENTITY migration needs: sync the sequence to the actual maximum loaded ID, discovered safely rather than guessed.

MEDIUM

Quick answer

After bulk-loading rows that carried their original explicit IDs, the PostgreSQL sequence behind an identity column still starts at 1 — it must be reset with setval() to the actual maximum loaded ID, or the next application insert will collide with an existing row.

Why it breaks

PostgreSQL's identity column sequence only advances when a row is inserted through that column's own generator. Bulk-loading rows with explicit IDs (as any migration from MySQL AUTO_INCREMENT data must do, to preserve foreign-key relationships) never touches the sequence at all — so immediately after loading, the sequence is still at its starting value while the table is full of much higher IDs.

Real examples

Full discovery-then-sync sequence

-- MySQL: AUTO_INCREMENT counter is stored with the table itself, nothing to sync separately

-- 1. Discover the sequence name (never guess it):
SELECT pg_get_serial_sequence('orders', 'id');

-- 2. Sync it to the actual data:
SELECT setval(pg_get_serial_sequence('orders', 'id'), COALESCE((SELECT MAX(id) FROM orders), 1));

Two-step process: discovery first (since the exact sequence name is an implementation detail you should not assume), then the sync itself.

Guarding against an empty table

-- N/A — MySQL AUTO_INCREMENT has no equivalent empty-table edge case to guard against

SELECT setval(pg_get_serial_sequence('orders', 'id'), COALESCE((SELECT MAX(id) FROM orders), 1));

COALESCE(..., 1) keeps the sync working even when the table has zero rows, where MAX(id) would otherwise be NULL.

Safe migration options

Always discover the sequence name with pg_get_serial_sequence('table_name', 'column_name') — never hardcode a guessed name like table_name_column_name_seq, since identity-column sequence names aren't guaranteed to follow that pattern in every PostgreSQL version/configuration.
Run the sync as the very last step of loading each table, after all rows (including any with high explicit IDs) are in place — running it mid-load risks a race if application writes start before every row has landed.
For an empty table, guard against MAX(id) being NULL — setval() with a NULL target either errors or misbehaves depending on the call form, so default to 1 explicitly when the table has no rows yet.

PostgreSQL solution

SELECT setval(pg_get_serial_sequence('orders', 'id'), COALESCE((SELECT MAX(id) FROM orders), 1));

Validation

Before cutover

After running the sync, execute SELECT nextval(pg_get_serial_sequence('orders', 'id')); once (in a throwaway transaction you roll back, or accepting the one-time gap) and confirm it returns a value strictly greater than the actual MAX(id) — then insert one real test row through the application path and confirm it succeeds without a duplicate-key error.

Scan your own schema

This page documents detector rule AUTO_INCREMENT in the DevEquiv analyzer — paste or upload your real MySQL/MariaDB schema to find every occurrence of this (and 19 other documented issues) automatically, with a prioritized readiness report.

Analyze a schema free

References

PostgreSQL Manual — Sequence Manipulation Functions (v17) — checked 2026-07-30
MySQL Manual — AUTO_INCREMENT (v8.0) — checked 2026-07-30

Last verified 2026-07-30.