PostgreSQL Integer Type Explained
This post is part of a series on the numeric data types found in PostgreSQL. Read on to learn all about the integer type. Write a comment if you have any questions or corrections!
|smallint||2 bytes||-32,768 to 32,767|
|integer||4 bytes||-2,147,483,648 to 2,147,483,647|
|bigint||8 bytes||-9,223,372,036,854,775,808 to +9,223,372,036,854,775,807|
integer type is used to store whole numbers, i.e. numbers without decimals or fractions after them. Examples: 1, 3,305, or 100,242. Each integer type has a range of possible values, as specified in the above table. If you attempt to store a value outside of that range, PostgreSQL will throw an error.
integer type is the most common choice. According to the PostgreSQL docs, this is because it "offers the best balance between range, storage size, and performance".
smallint is used mostly when disk space is a concern, and
bigint is recommended whenever the
integer range isn't large enough. I would also add that, if you know a number will stay within the
smallint range, I don't see a reason not to use it. Save on those storage bytes!
integer can also be shortened to
More Numeric Types
Learn about the other numeric data types in one of the following posts:
What to see more of this kind of content? Let me know in the comments or reach out on Twitter!