AUTO_INCREMENT vs sequences vs identity columns

MySQL and PostgreSQL both hand out auto-numbered primary keys, but they do it with different machinery. Knowing the difference matters most during a MySQL to PostgreSQL migration, because the target you pick decides whether your imported rows load cleanly and whether the next insert collides with an existing ID. Here is how the three mechanisms actually work.

MySQL: AUTO_INCREMENT

AUTO_INCREMENT is an attribute on a single integer column, and a table can have exactly one. The counter lives inside the table's storage engine, not as a separate object:

CREATE TABLE users (
  id     BIGINT NOT NULL AUTO_INCREMENT,
  email  VARCHAR(255) NOT NULL,
  PRIMARY KEY (id)
);

You can read the next value from information_schema.TABLES and force it with ALTER TABLE users AUTO_INCREMENT = 1000;. Two things surprise people: the sequence is not gap-free (a rolled-back transaction or a bulk insert burns numbers permanently), and before MySQL 8.0 InnoDB kept the counter only in memory — after a restart it was recomputed as MAX(id) + 1, which could re-issue IDs freed by deleted rows. MySQL 8.0 persists the counter across restarts, closing that long-standing bug.

PostgreSQL: SERIAL

SERIAL is not a real data type. It is shorthand that Postgres expands at table-creation time into a plain integer column, a standalone sequence object, and a column default that pulls from it:

CREATE TABLE users (id SERIAL PRIMARY KEY, email TEXT);

-- is rewritten to roughly:
CREATE SEQUENCE users_id_seq;
CREATE TABLE users (
  id     INTEGER NOT NULL DEFAULT nextval('users_id_seq'),
  email  TEXT
);
ALTER SEQUENCE users_id_seq OWNED BY users.id;

A sequence is a first-class object: it has its own name, its own permissions, and it can be shared or called by hand with nextval(). Use BIGSERIAL when you expect more than ~2.1 billion rows, since plain SERIAL is a 32-bit integer. The catch that bites migrators: the default only fires when you omit the column. Insert an explicit id and the sequence is not advanced, so a later auto-generated insert can reuse a value and fail with a duplicate-key error.

PostgreSQL: GENERATED AS IDENTITY

Since PostgreSQL 10 the SQL-standard identity column is the recommended choice. It is still backed by a sequence internally, but that sequence is managed as part of the column — it is created and dropped with the table and needs no separate grants:

CREATE TABLE users (
  id     BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  email  TEXT
);

There are two flavours, and the difference matters for migrations:

• GENERATED ALWAYS AS IDENTITY — Postgres always supplies the value. An explicit insert into the column is rejected unless you add OVERRIDING SYSTEM VALUE.
• GENERATED BY DEFAULT AS IDENTITY — Postgres fills the column only when you leave it out, and accepts explicit values otherwise. This behaves like AUTO_INCREMENT and is what you want when loading rows that already carry their own IDs.

Which to target when converting

A dump you migrate from MySQL contains explicit id values for every existing row, so the target column must accept them. That rules out GENERATED ALWAYS for a straight data load. The clean mapping is:

• MySQL AUTO_INCREMENT → PostgreSQL BIGINT GENERATED BY DEFAULT AS IDENTITY (or BIGSERIAL on older servers).
• PostgreSQL IDENTITY / SERIAL → MySQL BIGINT AUTO_INCREMENT.

Whichever you pick, the backing sequence still has to be moved past the highest imported ID or the first fresh insert will collide — see how to reset PostgreSQL sequences after importing data.

SwapSQL maps AUTO_INCREMENT to a modern identity column and sets the sequence for you automatically. Convert your MySQL database now →