Menu

PostgreSQL Integer Types: SMALLINT, INTEGER, and BIGINT

Compare PostgreSQL SMALLINT, INTEGER, and BIGINT ranges and storage sizes; choose a type for columns and identity keys.

Updated on

PostgreSQL provides three signed integer types: SMALLINT, INTEGER, and BIGINT. They differ in storage size and the range of values they can store.

Type Aliases Storage Range
SMALLINT INT2 2 bytes -32,768 to 32,767
INTEGER INT, INT4 4 bytes -2,147,483,648 to 2,147,483,647
BIGINT INT8 8 bytes -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

Choose a type whose range safely covers expected values and growth. BIGINT uses more storage than INTEGER, including in indexes, but that alone does not mean it will make every query slower. Values outside a type’s range cause an error.

Declare an integer column

Use the type directly in CREATE TABLE:

CREATE TABLE inventory (
    item_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    quantity INTEGER NOT NULL DEFAULT 0,
    retry_count SMALLINT NOT NULL DEFAULT 0
);

INT is an alias for INTEGER. PostgreSQL integer types are signed; there is no unsigned integer type.

Generate integer identifiers

For new tables, use an identity column when PostgreSQL should generate values from an associated sequence:

CREATE TABLE events (
    event_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    event_name TEXT NOT NULL
);

GENERATED ALWAYS asks PostgreSQL to generate the value. Use GENERATED BY DEFAULT AS IDENTITY when inserts may also provide an explicit value.

SMALLSERIAL, SERIAL, and BIGSERIAL are legacy shorthand that create an integer column with a sequence-backed default. They correspond to SMALLINT, INTEGER, and BIGINT, respectively; they are not separate integer storage types. Use an identity column for new schema designs, and choose a sufficiently wide integer type for the expected number of generated values.

Further reading