MySQL AUTO_INCREMENT to PostgreSQL Identity Column Crosswalk

PostgreSQL's SQL-standard GENERATED ALWAYS AS IDENTITY columns replace MySQL's AUTO_INCREMENT, but back the counter with a distinct sequence object and reject explicit-value inserts by default.

Close equivalent Covers the common case, but has documented behavioral differences.

MySQL feature: AUTO_INCREMENT
PostgreSQL equivalent: GENERATED ... AS IDENTITY

Main limitation: AUTO_INCREMENT is a column attribute in MySQL controlling one implicit counter tied to the column; PostgreSQL implements the same idea via a separate sequence object, created either implicitly by the legacy SERIAL shorthand or explicitly via GENERATED ... AS IDENTITY (the SQL-standard, recommended form since PostgreSQL 10).

Quick mapping

MySQL	`AUTO_INCREMENT`
PostgreSQL	`GENERATED ... AS IDENTITY`

Examples

Auto-incrementing primary key column

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255)
);

CREATE TABLE users (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email VARCHAR(255)
);

The SQL-standard IDENTITY syntax is the modern PostgreSQL equivalent; the legacy SERIAL type produces a similar result but as non-standard shorthand.

Inserting an explicit value for the auto-increment column

INSERT INTO users (id, email) VALUES (500, '[email protected]'); -- works, advances the counter

INSERT INTO users (id, email) OVERRIDING SYSTEM VALUE VALUES (500, '[email protected]');

PostgreSQL's GENERATED ALWAYS requires the explicit OVERRIDING SYSTEM VALUE clause to accept a manually supplied value for an identity column — omitting it raises an error, unlike MySQL's silent acceptance.

Caveats

Caveat

AUTO_INCREMENT is a column attribute in MySQL controlling one implicit counter tied to the column; PostgreSQL implements the same idea via a separate sequence object, created either implicitly by the legacy SERIAL shorthand or explicitly via GENERATED ... AS IDENTITY (the SQL-standard, recommended form since PostgreSQL 10).

Caveat

SERIAL is not a true PostgreSQL data type — it creates an integer column plus a sequence and a default expression. Unlike AUTO_INCREMENT, that sequence is a distinct database object that must be considered separately during dumps/restores, ownership changes, and permission grants.

Caveat

GENERATED ALWAYS AS IDENTITY rejects explicit inserts into the column unless the statement uses OVERRIDING SYSTEM VALUE; MySQL's AUTO_INCREMENT allows a normal INSERT to specify an explicit value with no special syntax, silently advancing the counter past it — a meaningful behavioral gap for data-loading scripts.

Caveat

Resetting the counter differs: MySQL uses ALTER TABLE t AUTO_INCREMENT = n; PostgreSQL uses ALTER SEQUENCE t_id_seq RESTART WITH n (or SELECT setval(...)), operating on the sequence object rather than the table.

References

MySQL Reference Manual — AUTO_INCREMENT (v8.4) — checked 2026-07-29
PostgreSQL docs — Identity Columns (v17) — checked 2026-07-29

Last verified 2026-07-29.