Use identity primary keys

Most tables need a numeric identifier that the database assigns by itself. In PostgreSQL, the modern way to get one is an identity column.

Define a generated key like this:

CREATE TABLE notes (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  title TEXT NOT NULL
);

Identity columns use a sequence underneath but keep the generation rule attached to the column definition. You never name the sequence, never wire up a default by hand, and the rule shows up clearly in \d notes.

Older tutorials teach SERIAL for this. It still works, and you will meet it in existing schemas, but it is a PostgreSQL shorthand that expands into a separate sequence plus a column default. Identity is the SQL-standard syntax, and it is what the PostgreSQL documentation recommends for new tables.

Use BIGINT, not INT. The cost is four bytes per row. The alternative is discovering, years in, that a busy table ran out of 32-bit identifiers.

ALWAYS or BY DEFAULT

The generation rule comes in two strictness levels.

GENERATED BY DEFAULT fills the column when you leave it out, but accepts a value if you supply one. That flexibility helps when importing existing rows that already carry identifiers.

GENERATED ALWAYS rejects manual values outright:

INSERT INTO notes (id, title) VALUES (500, 'Manual id');

ERROR:  cannot insert a non-DEFAULT value into column "id"
HINT:  Use OVERRIDING SYSTEM VALUE to override.

That error is a feature. It stops application code from quietly inserting identifiers the database did not assign, which later collide with generated ones. My advice is GENERATED ALWAYS for new tables, and BY DEFAULT only when you have an import that genuinely needs it.

Verify the generation

Insert a row and read back the assigned key in one statement:

INSERT INTO notes (title)
VALUES ('Plan the week')
RETURNING id;

 id
----
  1

Run it again and you get 2. Do not expect the numbers to be gapless, though: identifiers can skip values after rolled-back inserts, and that is normal. The sequence machinery behind this, including what happens to gaps and how imports can leave a sequence behind the table’s real maximum, gets its own lesson next.