SERIAL Data Type in PostgreSQL

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 products (id SERIAL);

Is equivalent to these three statements:

CREATE SEQUENCE products_id_seq;

CREATE TABLE products (
  id INTEGER NOT NULL DEFAULT nextval('products_id_seq')
);

ALTER SEQUENCE products_id_seq OWNED BY products.id;

Syntax

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

Practical Example

Create an orders table with a SERIAL primary key and insert rows:

CREATE TABLE orders (
  id          SERIAL PRIMARY KEY,
  product_sku VARCHAR(50)  NOT NULL,
  quantity    INTEGER      NOT NULL CHECK (quantity > 0),
  placed_at   TIMESTAMPTZ  NOT NULL DEFAULT now()
);

-- Insert without specifying id
INSERT INTO orders (product_sku, quantity)
VALUES ('SKU-101', 3);

INSERT INTO orders (product_sku, quantity)
VALUES ('SKU-202', 1);

SELECT * FROM orders;

 id | product_sku | quantity |           placed_at
----+-------------+----------+----------------------------
  1 | SKU-101     |        3 | 2026-04-07 10:00:00+00
  2 | SKU-202     |        1 | 2026-04-07 10:00:01+00

Use RETURNING to get the generated ID immediately after insert:

INSERT INTO orders (product_sku, quantity)
VALUES ('SKU-303', 2)
RETURNING id;

 id
----
  3

Retrieving the Current Sequence Value

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

 currval
---------
       3

Modern Alternative: GENERATED ALWAYS AS IDENTITY

For PostgreSQL 10 and later, the SQL-standard identity column syntax is preferred:

CREATE TABLE shipments (
  id          INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id    INTEGER     NOT NULL,
  shipped_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

Identity columns prevent accidental manual inserts into the ID column. Attempting INSERT INTO shipments (id, order_id) VALUES (99, 1) raises an error unless you use OVERRIDING SYSTEM VALUE.

Testing with Vela

Sequence gaps are normal in PostgreSQL — a rolled-back transaction still consumes a sequence value. Before deploying logic that depends on sequential IDs (batch processing, gap detection, audit trails), test it on a database branch under realistic concurrent insert conditions to verify your application handles gaps correctly.

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. Never rely on SERIAL IDs being contiguous.
• The sequence generator is not transaction-safe by design: two concurrent inserts each receive a unique ID even if one rolls back, leaving a gap.
• Inspect a table’s underlying sequence with \d+ table_name or SELECT * FROM information_schema.sequences.