SERIAL

PostgreSQL's SERIAL pseudo-type provides a convenient shorthand for creating auto-incrementing integer columns. It is commonly used for primary key columns where you want the database to assign unique integer IDs automatically. Under the hood, PostgreSQL creates a SEQUENCE object, sets the column default to nextval(), and adds a NOT NULL constraint — all in one step.

How SERIAL works

The statement:

CREATE TABLE table_name (
  id SERIAL
);

is exactly equivalent to:

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;

The three SERIAL variants and their ranges:

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

Important: SERIAL does not automatically make the column a primary key. You must add PRIMARY KEY explicitly.

SERIAL examples

Creating a table with a SERIAL primary key:

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

Omit the column in INSERT or use DEFAULT to let PostgreSQL assign the next value:

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

SELECT * FROM fruits;

 id |  name
----+--------
  1 | Apple
  2 | Orange
(2 rows)

Retrieve the generated value immediately after insert:

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

 id
----
  3
(1 row)

Get the current sequence value:

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

 currval
---------
       3
(1 row)

Add a SERIAL column to an existing table:

ALTER TABLE baskets ADD COLUMN id SERIAL PRIMARY KEY;

SERIAL tips

• For new tables, prefer the SQL-standard GENERATED ALWAYS AS IDENTITY syntax over SERIAL — it offers better control and is safer against accidental overwrites.
• Sequence values are not transaction-safe: if a transaction that consumed a value is rolled back, that value is permanently skipped, creating gaps in the sequence. This is normal and expected.
• Use BIGSERIAL for high-volume tables to avoid hitting the 2-billion row limit of regular SERIAL.
• When copying a table with CREATE TABLE AS or pg_dump, the SERIAL default is preserved but you may need to reset the sequence with setval() after bulk inserts.

Reference: PostgreSQL documentation — Serial Types.