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 ofINTEGER
is insufficient.
Note that Postgres doesn't support unsigned integers. All integer types can store both positive and negative values.
Example usage
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:
Other examples
Integer operations
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 BIGSERIAL
and SMALLSERIAL
for auto-incrementing BIGINT
and SMALLINT
columns, respectively.
For example, we can create an orders
table with an auto-incrementing order_id
column:
This query returns the following:
The order_id
column gets a unique integer value for each new order.
Additional considerations
- 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 (
SMALLINT
,INTEGER
,BIGINT
) based on the expected value range can optimize storage efficiency and performance.
Resources
Need help?
Join our Discord Server to ask questions or see what others are doing with Neon. Users on paid plans can open a support ticket from the console. For more detail, see Getting Support.