How to define an auto increment primary key in PostgreSQL

To define a primary key that auto increments in PostgreSQL, the modern way is an identity column:

CREATE TABLE cars (
  id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  brand VARCHAR(30) NOT NULL,
  model VARCHAR(30) NOT NULL,
  year  CHAR(4) NOT NULL
);

PostgreSQL assigns the id automatically on insert. Verify it by inserting a row and reading back the generated value in the same statement:

INSERT INTO cars (brand, model, year)
VALUES ('Ford', 'Fiesta', '2022')
RETURNING id;

 id
----
  1

GENERATED ALWAYS rejects manual values: inserting an explicit id fails with cannot insert a non-DEFAULT value into column "id", which protects you from hand-picked identifiers that later collide with generated ones. Use GENERATED BY DEFAULT instead when you import rows that already carry their ids.

I use BIGINT rather than INT for new tables. The cost is four bytes per row; the benefit is never running a busy table out of 32-bit identifiers.

The SERIAL syntax

Before identity columns, the way to do this was the SERIAL type with the PRIMARY KEY constraint, like this:

CREATE TABLE cars (
  id    SERIAL PRIMARY KEY,
  brand VARCHAR(30) NOT NULL,
  model VARCHAR(30) NOT NULL,
  year  CHAR(4) NOT NULL
);

SERIAL still works and you will see it in lots of existing schemas and tutorials. It is a PostgreSQL-specific shorthand that creates a separate sequence and wires it up as the column default. Identity columns do the same job with SQL-standard syntax and keep the generation rule attached to the column, which is why the PostgreSQL documentation recommends them for new tables.

Either way, do not expect the generated numbers to be gapless. A rolled-back insert consumes a value and leaves a hole, and that is normal.

The MySQL equivalent

In MySQL / MariaDB this is equivalent to

CREATE TABLE cars (
  id    INT(11) NOT NULL AUTO_INCREMENT,
  brand VARCHAR(30) NOT NULL,
  model VARCHAR(30) NOT NULL,
  year CHAR(30) NOT NULL,
  PRIMARY KEY (`id`)
);