PostgreSQL Auto Increment: SERIAL vs IDENTITY Guide

PostgreSQL auto increment means the database fills in a new, unique number for you every time you add a row. You do not type the number yourself. In PostgreSQL there is no keyword called AUTO_INCREMENT. Instead you use either SERIAL (the older way) or GENERATED ... AS IDENTITY (the modern way). This guide explains both in plain words, with small examples you can copy into psql.

You need PostgreSQL installed and a way to run commands, such as psql. If you have not done that yet, start with our guide on creating a table in PostgreSQL first.

What is auto increment?

Imagine a ticket machine at a bakery. Each person gets the next number: 1, 2, 3, and so on. Nobody picks their own number, and no two people get the same one. Auto increment works the same way for the rows in a table.

This is useful for an id column. An id is a number that identifies one row, like a student number. Each row needs a different id, and typing them by hand is slow and easy to get wrong.

Option 1: SERIAL (the older, popular way)

SERIAL is a shortcut. When you use it, PostgreSQL makes an integer column and a hidden counter (called a sequence) behind the scenes. Here is a table of students.

CREATE TABLE students (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

Line 1 creates a table named students. Line 2 adds a column called id. SERIAL makes it count up on its own, and PRIMARY KEY says every id must be unique and never empty. Line 3 adds a name column that must have a value. Remember to end the command with a semicolon.

Option 2: GENERATED AS IDENTITY (the modern way)

Since PostgreSQL 10, there is a standard way to do the same thing. It follows the official SQL rules, so it is the better choice for new tables.

CREATE TABLE teachers (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL
);

The only change is the id line. INTEGER is the data type, a whole number. GENERATED ALWAYS AS IDENTITY tells PostgreSQL to create the number itself. PRIMARY KEY works as before.

Insert rows without giving an id

Now add rows. Leave the id out and PostgreSQL fills it in.

INSERT INTO students (name) VALUES ('Ana');
INSERT INTO students (name) VALUES ('Ben');
SELECT * FROM students;

The first two lines each add one student, and we only give the name. The third line shows all rows. You will see Ana with id 1 and Ben with id 2.

INSERT INTO students (name)
VALUES ('Cara')
RETURNING id;

RETURNING id asks PostgreSQL to tell you the id it just made. This is handy when your app needs to know the new row's number. Here it returns 3.

SERIAL vs IDENTITY: which should you use?

For a beginner, the simple rule is: use IDENTITY for new tables, and do not worry if you see SERIAL in older tutorials. They behave almost the same day to day.

ALWAYS vs BY DEFAULT

There are two kinds of identity columns. GENERATED ALWAYS stops you from setting the id by hand, which protects you from mistakes. GENERATED BY DEFAULT lets you set your own id if you really want to.

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

INSERT INTO courses (id, title) VALUES (100, 'SQL Basics');

BIGINT is a bigger whole number type, good for tables that may grow very large. Because we used BY DEFAULT, the insert with id 100 works. With ALWAYS, PostgreSQL would show an error. One warning: after you insert your own id, the counter does not know about it, so it may later try to use the same number and fail. Letting the database choose is safest.

Why are there gaps in my numbers?

Sometimes you will see ids like 1, 2, 4, 5. The number 3 is missing. This is normal. The counter never goes backward. If an insert fails, or you delete a row, that number is not reused. Gaps do not hurt anything, and you should not rely on ids having no gaps.

How to reset the counter

While learning, you may want to empty a table and start from 1 again.

TRUNCATE TABLE students RESTART IDENTITY;

TRUNCATE removes every row from the table very quickly, and RESTART IDENTITY sets the counter back to 1. Be careful: this deletes all your data, so only use it on practice tables.

Add auto increment to an existing table

If your table already exists without it, you can change the column. This works when the column is a whole number type.

ALTER TABLE notes
  ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY;

ALTER TABLE changes a table that already exists. ALTER COLUMN id picks the column, and ADD GENERATED ALWAYS AS IDENTITY turns on the counter. If the table already has rows, check the counter starts after your highest id.

Common beginner mistakes

• Writing AUTO_INCREMENT, which is MySQL and does not work in PostgreSQL.
• Typing an id by hand when the column is GENERATED ALWAYS.
• Expecting ids to have no gaps.
• Forgetting PRIMARY KEY, so ids are not guaranteed to be unique.
• Using TRUNCATE on a table that holds real data.