How PostgreSQL Implements Auto‑Increment: Serial Types, Sequences, and Custom Solutions

PostgreSQL Auto‑Increment Overview

Unlike MySQL’s auto_increment keyword, PostgreSQL achieves auto‑increment through sequences combined with column default values.

Serial Data Types

PostgreSQL provides three serial types— smallserial, serial, and bigserial. When a table is created with one of these types, PostgreSQL automatically creates an associated sequence and sets the column’s default to nextval('table_name_id_seq'::regclass). The types map to: smallserial →

smallint

serial

→

integer

bigserial

→

bigint

Example 1: Using serial

create table biz_test(
    id serial primary key,
    name varchar
);

Inspecting the table:

Table "public.biz_test"
 Column |   Type   | Modifiers
--------+----------+-------------------------------
 id     | integer  | not null default nextval('biz_test_id_seq'::regclass)
 name   | varchar  |
Indexes:
    "biz_test_pkey" PRIMARY KEY, btree (id)

Insert a row to verify auto‑increment:

insert into biz_test(name) values('Tom');
INSERT 0 1
select * from biz_test;
 id | name
----+------
  1 | Tom

Example 2: Manual Sequence Creation

Step 1: Create a sequence.

create sequence biz_test_id_seq;
CREATE SEQUENCE

Step 2: Create the table and set the column default to the sequence.

create table biz_test(
    id integer primary key default nextval('biz_test_id_seq')
);
CREATE TABLE

Resulting table definition:

Table "public.biz_test"
 Column |  Type   | Modifiers
--------+---------+-------------------------------
 id     | integer | not null default nextval('biz_test_id_seq'::regclass)
Indexes:
    "biz_test_pkey" PRIMARY KEY, btree (id)

If the column was created without a default, you can add the auto‑increment later:

ALTER TABLE ONLY public.biz_test
    ALTER COLUMN id SET DEFAULT nextval('public.biz_test_id_seq'::regclass);

Creating a Sequence with Custom Parameters

CREATE SEQUENCE public.biz_test_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

START WITH

defines the initial value, and INCREMENT BY sets the step size.

Querying Index Information

PostgreSQL uses different catalog tables to list indexes. Example queries:

select * from pg_indexes where tablename='biz_test';
-- returns schema, table, index name, tablespace, index definition

select * from pg_statio_all_indexes where relname='biz_test';
-- returns statistics about index reads and hits

The article demonstrates that understanding how different databases implement the same feature expands problem‑solving perspectives.