Counters and codes: BIGINT, INT8, INTEGER, and SMALLINT

The INTEGER and SMALLINT data types hold small whole numbers. They are suited for columns that contain counts, sequence numbers, numeric identity codes, or any range of whole numbers when you know in advance the maximum and minimum values to be stored.

Both data types are stored as signed binary integers. INTEGER values have 32 bits and can represent whole numbers from -231-1 through 231-1.

SMALLINT values have only 16 bits. They can represent whole numbers from -32,767 through 32,767.

The INT and SMALLINT data types have the following advantages:
  • They take up little space (2 bytes per value for SMALLINT and 4 bytes per value for INTEGER).
  • You can perform arithmetic expressions such as SUM and MAX and sort comparisons on them.

The disadvantage to using INTEGER and SMALLINT is the limited range of values that they can store. The database server does not store a value that exceeds the capacity of an integer. Of course, such excess is not a problem when you know the maximum and minimum values to be stored.

If you must store a broader range of values that will fill up an INTEGER, you can use BIGINT or INT8. These data types have the following advantages:
  • They hold a broad range of values. (Integers ranging from - (263 -1) through 263 -1.)
  • You can perform arithmetic expressions such as SUM and MAX and sort comparisons on them. BIGINT has storage and computational efficiency advantages over INT8.

The disadvantage of using BIGINT or INT8 is that they use more disk space than an INTEGER. The actual size depends on the word length of the platform. An INT8 or SERIAL8 value requires 10 bytes of storage. BIGINT and BIGSERIAL values require 8 bytes of storage.