SERIAL Data Type | PostgreSQL Tutorial

PostgreSQL's SERIAL pseudo-type provides a convenient shorthand for creating auto-increment columns. When you declare a column as SERIAL, PostgreSQL automatically creates a sequence, sets the sequence's nextval() as the column default, and links the sequence lifetime to the column.

What SERIAL does under the hood

This single declaration:

CREATE TABLE table_name (id SERIAL);

Is equivalent to these three statements:

CREATE SEQUENCE table_name_id_seq;

CREATE TABLE table_name (
  id integer NOT NULL DEFAULT nextval('table_name_id_seq')
);

ALTER SEQUENCE table_name_id_seq OWNED BY table_name.id;

SERIAL variants and their ranges

SMALLSERIAL: 2 bytes, 1 to 32,767
SERIAL: 4 bytes, 1 to 2,147,483,647
BIGSERIAL: 8 bytes, 1 to 9,223,372,036,854,775,807

Creating a table with SERIAL primary key

CREATE TABLE fruits (
  id   SERIAL PRIMARY KEY,
  name VARCHAR NOT NULL
);

Insert rows without specifying id:

INSERT INTO fruits (name) VALUES ('Orange');
INSERT INTO fruits (id, name) VALUES (DEFAULT, 'Apple');

SELECT * FROM fruits;

id |  name
----+--------
 1 | Orange
 2 | Apple

Retrieving the generated ID

Use RETURNING to get the auto-generated value:

INSERT INTO fruits (name) VALUES ('Banana') RETURNING id;

id
----
 3

To get the current sequence value after an insert:

SELECT currval(pg_get_serial_sequence('fruits', 'id'));

currval
---------
       3

Adding a SERIAL column to an existing table

CREATE TABLE baskets (name VARCHAR(255) NOT NULL);

ALTER TABLE baskets ADD COLUMN id SERIAL PRIMARY KEY;

Production tips

For new PostgreSQL 10+ tables, prefer GENERATED ALWAYS AS IDENTITY over SERIAL — it is SQL-standard and prevents accidental manual inserts into the ID column.
Use BIGSERIAL for high-volume tables where IDs could exceed 2 billion rows over the table's lifetime.
Sequence gaps are normal — a rolled-back transaction still consumes a sequence value. Do not rely on SERIAL IDs being contiguous.
The sequence generator is not transaction-safe: two concurrent inserts will each receive a unique ID even if one rolls back, leaving a gap in the sequence.

Reference: PostgreSQL documentation — Serial Types.

Frequently Asked Questions

What does SERIAL do in PostgreSQL?

SERIAL creates a sequence object, sets its nextval() as the column default, adds a NOT NULL constraint, and links the sequence to the column so it is dropped when the column or table is dropped. It is shorthand for manually creating a sequence and setting it as a DEFAULT.

What are the differences between SMALLSERIAL, SERIAL, and BIGSERIAL?

All three are auto-increment pseudo-types with different underlying integer ranges: SMALLSERIAL (2 bytes, 1 to 32,767), SERIAL (4 bytes, 1 to 2,147,483,647), and BIGSERIAL (8 bytes, 1 to 9,223,372,036,854,775,807). Use BIGSERIAL for high-volume tables to avoid running out of IDs.

Does SERIAL automatically create a primary key?

No. SERIAL creates a NOT NULL column with an auto-increment default, but it does not automatically add a PRIMARY KEY constraint. You must explicitly add PRIMARY KEY: id SERIAL PRIMARY KEY.

How do I get the auto-generated ID after an INSERT?

Use the RETURNING clause: INSERT INTO fruits (name) VALUES ('Banana') RETURNING id; Alternatively, use currval(pg_get_serial_sequence('table_name', 'column_name')) to get the most recently generated value for the sequence.

Should I use SERIAL or GENERATED ALWAYS AS IDENTITY for new tables?

For new tables, PostgreSQL recommends using GENERATED ALWAYS AS IDENTITY (introduced in PostgreSQL 10) instead of SERIAL. Identity columns are SQL-standard, more robust, and prevent accidental manual inserts into the ID column. SERIAL remains supported for backwards compatibility.