Integer types
smallint, integer and bigint store whole numbers of different sizes.
In this page:
Syntax
column_name smallint
column_name integer
column_name bigint
column_name SERIALInteger types
smallint uses 2 bytes (-32,768 to 32,767), integer 4 bytes (about plus or minus 2.1 billion) and bigint 8 bytes (about plus or minus 9.2 quintillion).
PostgreSQL raises an error when a value is out of range instead of wrapping. Use identity columns or serial types for auto-incrementing ids, and bigint for ids that may exceed 2 billion.
Note:
Use bigint for ids in tables that may grow very large.
Example: Integer types
shop=# SELECT 2147483647::integer AS int_max, 9223372036854775807::bigint AS bigint_max;
int_max | bigint_max
------------+---------------------
2147483647 | 9223372036854775807
(1 row)
shop=# SELECT 2147483648::integer;
ERROR: integer out of range
shop=# SELECT 7 / 2 AS int_division, 7 / 2.0 AS decimal_division, 7 % 2 AS remainder;
int_division | decimal_division | remainder
--------------+------------------+-----------
3 | 3.5000 | 1⚠️ Run this in your own terminal or Node.js environment.
Related Topics
{# common_mistakes/chapter_summary/browser_support: on Hindi pages the
view already swaps in the hi_ translation fields (or blanks these
out if untranslated), so this renders correctly for both languages
without a lang_code check here. #}
Common Mistakes
- Using integer for ids that overflow
- Expecting silent wraparound
- Using floating types for counts
Chapter Summary
- smallint 2 bytes, integer 4 bytes, bigint 8 bytes
- Out-of-range values raise errors
- Use identity for auto ids
- Choose the narrowest fitting size
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: