Postgres Integer data types
Work with integers in Postgres
In Postgres, integer data types are used for storing numerical values without a fractional component. They are useful as identifiers, counters, and many other common data modeling tasks. Postgres offers multiple integer types, catering to different ranges of values and storage sizes.
Storage and syntax
Postgres supports three primary integer types. Choosing the appropriate integer type depends on the range of data expected.
SMALLINT: A small-range integer, occupying 2 bytes of storage. It's useful for columns with a small range of values.
INTEGER: The standard integer type, using 4 bytes of storage. It's the most commonly used since it balances storage/performance efficiency and range capacity.
BIGINT: A large-range integer, taking up 8 bytes. It's used when the range of
Note that Postgres doesn't support unsigned integers. All integer types can store both positive and negative values.
Consider a database for a small online bookstore. Here,
SMALLINT could be used for storing the number of copies of a book in stock, while
INTEGER would be appropriate for a unique identifier for each book.
The query below creates a
books table with these columns:
Postgres supports various arithmetic operations on integer types, including addition, subtraction, multiplication, and division.
Note that the division of integers does not yield a fractional result; it truncates the result to an integer.
Sequences and auto-Increment
Postgres also provides
SERIAL, which is a pseudo-type for creating auto-incrementing integers, often used for primary keys. It's effectively an
INTEGER that automatically increments with each new row insertion.
There is also
SMALLSERIAL for auto-incrementing
SMALLINT columns, respectively.
For example, we can create an
orders table with an auto-incrementing
This query returns the following:
order_id column gets a unique integer value for each new order.
- Data integrity: Integer types strictly store numerical values. Attempting to insert non-numeric data, or a value outside the range of that particular type will result in an error.
- Performance: Choosing the correct integer type (
BIGINT) based on the expected value range can optimize storage efficiency and performance.
Last updated on