MySQL AUTO_INCREMENT → PostgreSQL Migration

Quick answer

Replace AUTO_INCREMENT with GENERATED ALWAYS AS IDENTITY. This handles new rows correctly, but if you load existing rows with their original IDs, the sequence itself will still start from 1 and must be synced to the actual max ID or the next insert will collide.

Why it breaks

MySQL's AUTO_INCREMENT is a column attribute that both generates new values and tracks the current counter together. PostgreSQL splits this into two objects: an identity column (or a plain column backed by a sequence) and the sequence object itself, which does not know about rows that were inserted with an explicit ID during data load — only rows inserted through the identity column advance it automatically.

Real examples

CREATE TABLE orders (
  id INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
);

CREATE TABLE orders (
  id INTEGER GENERATED ALWAYS AS IDENTITY,
  PRIMARY KEY (id)
);

-- After bulk-loading 10,000 existing orders with explicit ids 1..10000

SELECT setval(pg_get_serial_sequence('orders', 'id'), (SELECT MAX(id) FROM orders));
-- Without this, the next INSERT without an explicit id starts at 1 again and collides with an existing row.

Safe migration options

• Use GENERATED ALWAYS AS IDENTITY for new schemas — it is the modern, SQL-standard PostgreSQL syntax.
• GENERATED BY DEFAULT AS IDENTITY (or the older SERIAL) if your load process needs to insert explicit ID values during migration itself, since ALWAYS rejects explicit values unless OVERRIDING SYSTEM VALUE is used.
• After loading data, always discover the sequence name with pg_get_serial_sequence() rather than guessing it — never hardcode a sequence name.

PostgreSQL solution

CREATE TABLE ... (id BIGINT GENERATED ALWAYS AS IDENTITY, ...). The identity clause replaces AUTO_INCREMENT directly in the column definition.

Validation

References

• MySQL Manual — AUTO_INCREMENT (v8.0) — checked 2026-07-30
• PostgreSQL Manual — Identity Columns (v17) — checked 2026-07-30

Last verified 2026-07-30.