Auto-Generated Ids: IDENTITY and SERIAL

Most tables have an id column, and every new row needs a new id. Instead of choosing the numbers yourself, you can let PostgreSQL generate them automatically: 1, 2, 3 and so on.

In MySQL, this is called AUTO_INCREMENT. PostgreSQL has two ways to do it: identity columns and SERIAL.


Syntax (Identity Column)

CREATE TABLE table_name (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  other_column datatype
);

This is the modern, standard way, and the one we recommend.


Example: Auto-Generated Ticket Ids

CREATE TABLE tickets (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title VARCHAR(100)
);

INSERT INTO tickets (title) VALUES ('Login is slow');
INSERT INTO tickets (title) VALUES ('Page not found');

SELECT * FROM tickets;

Output:

CREATE TABLE
INSERT 0 1
INSERT 0 1
 id |     title      
----+----------------
  1 | Login is slow
  2 | Page not found
(2 rows)
  • You did not give an id. PostgreSQL filled in 1, then 2.
  • Each new row gets the next number.

To see the new id right away, add RETURNING (from the Manage Data section):

INSERT INTO tickets (title) VALUES ('Button missing')
RETURNING id;

Output:

 id 
----
  3
(1 row)

INSERT 0 1

GENERATED ALWAYS protects the ids

With GENERATED ALWAYS, PostgreSQL does not let you choose an id yourself:

INSERT INTO tickets VALUES (10, 'Typo on home page');

Output:

ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

This keeps the ids safe from mistakes. If you write GENERATED BY DEFAULT AS IDENTITY instead, PostgreSQL generates an id only when you don't give one.


SERIAL

You will also see SERIAL in many older examples:

CREATE TABLE tickets (
  id SERIAL PRIMARY KEY,
  title VARCHAR(100)
);

It also generates 1, 2, 3 and so on. For new tables, prefer the identity column.


Key Points

  • The id column is usually also the PRIMARY KEY.
  • The numbers go up by 1 each time.
  • Ids are not reused. If you delete row 2, the next new row still gets the next number.

In the exercise, create a table with an auto-generated id.