Integer

PostgreSQL provides three integer types for storing whole numbers: SMALLINT, INTEGER, and BIGINT. Choosing the right type balances storage efficiency, value range, and query performance.

Integer type comparison

• SMALLINT: 2 bytes — range -32,768 to +32,767. Good for small enumerations, ages, page counts.
• INTEGER (INT, INT4): 4 bytes — range -2,147,483,648 to +2,147,483,647. The default choice for most integer columns.
• BIGINT (INT8): 8 bytes — range -9,223,372,036,854,775,808 to +9,223,372,036,854,775,807. Use only when INTEGER range is insufficient.

Note: PostgreSQL does not support unsigned integers. Use a CHECK constraint to enforce non-negative values when needed.

SMALLINT: page counts and small ranges

CREATE TABLE books (
  book_id SERIAL PRIMARY KEY,
  title   VARCHAR(255) NOT NULL,
  pages   SMALLINT NOT NULL CHECK (pages > 0)
);

The CHECK (pages > 0) constraint ensures only positive page counts are accepted, complementing the type's range limitation.

INTEGER: city populations and general use

CREATE TABLE cities (
  city_id    SERIAL PRIMARY KEY,
  city_name  VARCHAR(255) NOT NULL,
  population INT NOT NULL CHECK (population >= 0)
);

INT is a synonym for INTEGER. The 4-byte range covers over 2 billion — sufficient for most real-world integer values.

BIGINT: large-scale counters and IDs

CREATE TABLE events (
  event_id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  event_name VARCHAR(255) NOT NULL,
  occurred_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Use BIGINT for primary keys on tables expected to grow beyond ~2 billion rows, or for storing Unix timestamps in milliseconds.

Production tips

• Default to INTEGER for new columns — it covers nearly all practical integer values with minimal storage.
• Use BIGINT proactively for auto-increment primary keys on high-volume tables to avoid painful migrations later when INTEGER hits its limit.
• Use SMALLINT only when storage is a genuine concern and the value is provably bounded — for example, days of the week (0–6) or HTTP status codes.
• PostgreSQL does not support unsigned integers — enforce non-negative constraints with CHECK (col >= 0) where needed.

Reference: PostgreSQL documentation — Integer Types.