Integer Data Types in PostgreSQL

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. All three types reject fractional inputs — only whole numbers are accepted.

Syntax

column_name SMALLINT       -- 2 bytes
column_name INTEGER        -- 4 bytes; aliases: INT, INT4
column_name BIGINT         -- 8 bytes; alias: INT8

Practical Example

Use all three integer types in an e-commerce schema:

-- SMALLINT for bounded small values
CREATE TABLE product_ratings (
  product_id  INTEGER   NOT NULL,
  user_id     BIGINT    NOT NULL,
  rating      SMALLINT  NOT NULL CHECK (rating BETWEEN 1 AND 5),
  PRIMARY KEY (product_id, user_id)
);

-- INTEGER for standard quantities
CREATE TABLE orders (
  id              SERIAL PRIMARY KEY,
  product_id      INTEGER NOT NULL,
  quantity        INTEGER NOT NULL CHECK (quantity > 0),
  unit_price_cents INTEGER NOT NULL CHECK (unit_price_cents > 0)
);

-- BIGINT for high-volume event identifiers
CREATE TABLE events (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id    INTEGER     NOT NULL,
  event_type  VARCHAR(50) NOT NULL,
  occurred_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Insert data and verify type enforcement:

INSERT INTO product_ratings (product_id, user_id, rating)
VALUES (42, 100001, 5);

-- Attempting to insert a value out of SMALLINT range:
INSERT INTO product_ratings (product_id, user_id, rating)
VALUES (43, 100002, 6);
-- ERROR: integer out of range (value 6 exceeds CHECK, not type range)

-- Attempting to insert a value exceeding SMALLINT storage:
INSERT INTO product_ratings (product_id, user_id, rating)
VALUES (44, 100003, 32768);
-- ERROR: smallint out of range

Inspect the column definitions:

\d+ orders

No Unsigned Integers — Use CHECK Instead

PostgreSQL does not have unsigned integer types. Enforce non-negative values with a CHECK constraint:

ALTER TABLE orders
ADD CONSTRAINT chk_quantity_positive CHECK (quantity > 0);

Testing with Vela

Before migrating an INTEGER primary key column to BIGINT on a production table, create a database branch, apply the ALTER TABLE ... ALTER COLUMN TYPE BIGINT migration, and run your full application test suite — ORM queries, foreign key joins, and index scans — against the branch to confirm nothing breaks before touching the live database.

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 a painful migration when INTEGER hits its 2.1 billion 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.
• Monitor sequence current values on high-write tables: SELECT last_value FROM table_id_seq; to catch impending overflow before it becomes an incident.