Integer types (smallint, integer, bigint) in PostgreSQL

Introduction

Integer types store whole numbers without decimals. They help save space and keep data organized.

When you need to store small numbers like ages or counts (use smallint).

When you want to store regular whole numbers like IDs or quantities (use integer).

When you expect very large numbers like big counters or financial data (use bigint).

Syntax

PostgreSQL

column_name smallint;
column_name integer;
column_name bigint;

smallint stores numbers from -32,768 to 32,767.

integer stores numbers from -2,147,483,648 to 2,147,483,647.

bigint stores numbers from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

Examples

Use smallint for age because ages are usually small numbers.

PostgreSQL

CREATE TABLE people (
  age smallint
);

Use integer for product IDs as they can be larger but not extremely big.

PostgreSQL

CREATE TABLE products (
  product_id integer
);

Use bigint for transaction counts that can grow very large over time.

PostgreSQL

CREATE TABLE transactions (
  transaction_count bigint
);

Sample Program

This creates a table with three columns using different integer types, inserts one row, and selects it.

PostgreSQL

CREATE TABLE example_numbers (
  small_num smallint,
  regular_num integer,
  big_num bigint
);

INSERT INTO example_numbers VALUES (32000, 2000000000, 9000000000000000000);

SELECT * FROM example_numbers;

Important Notes

Choose the smallest integer type that fits your data to save storage space.

Using a smaller type than needed can cause errors if numbers exceed the range.

PostgreSQL automatically uses 4 bytes for integer and 8 bytes for bigint.

Summary

smallint is for small whole numbers.

integer is the common choice for whole numbers.

bigint is for very large whole numbers.