SQL Reference ยท Constraints and Data Types
SQL integer types (INTEGER, BIGINT, SMALLINT)
Reference for SQL integer types: INTEGER, SMALLINT, BIGINT, TINYINT, their ranges, overflow, integer division, SQLite's flexible integers and dialect differences.
Integer types store whole numbers with no fractional part. The standard defines SMALLINT, INTEGER (or INT) and BIGINT, which differ in how many bytes a value takes and so in the range they cover. Use them for counts, quantities, ages, identifiers and anything else that is never fractional.
Integers are exact, small and fast to compare and index, which is why they are the usual type for keys. Choose the smallest type that is safe for the data, and move to BIGINT for values that can pass two billion, such as event ids on a busy system.
CREATE TABLE counters ( id BIGINT PRIMARY KEY, hits INTEGER NOT NULL DEFAULT 0 );
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
SMALLINT INTEGER -- also written INT BIGINT TINYINT -- not standard: MySQL, SQL Server
| Type | Size | Range (signed) |
|---|---|---|
TINYINT |
1 byte | -128 to 127 (SQL Server: 0 to 255) |
SMALLINT |
2 bytes | -32,768 to 32,767 |
INTEGER / INT |
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 |
Behavior: a value outside the range raises an error in PostgreSQL, SQL Server and (in strict mode) MySQL. Arithmetic on two integers produces an integer, so 7 / 2 is 3 in most dialects, with the exceptions listed below.
Dialect differences
| Dialect | Notes |
|---|---|
| SQLite | Stores integers in 1 to 8 bytes as needed, so SMALLINT and BIGINT are just names (see type affinity). Results beyond 64 bits become REAL. INTEGER PRIMARY KEY is the row id. |
| PostgreSQL | smallint, integer, bigint with overflow errors. No TINYINT and no unsigned types. / on integers truncates toward zero. |
| MySQL | Adds TINYINT, MEDIUMINT (3 bytes) and the UNSIGNED modifier. / returns a decimal, and DIV is integer division. The number in INT(11) only affects display width in old versions. |
| SQL Server | tinyint (unsigned), smallint, int, bigint. / on integers truncates. |
| Oracle | Has NUMBER(p, 0). INTEGER is an alias for NUMBER(38), and / returns a decimal. |
-- MySQL SELECT 7 DIV 2; -- 3 SELECT 7 / 2; -- 3.5000 -- PostgreSQL SELECT 7 / 2; -- 3 SELECT 7 / 2.0; -- 3.5000000000000000
Usage notes
- Integer division truncates in SQLite, PostgreSQL and SQL Server. Cast one operand (
7 / 2.0, orCAST(x AS REAL)) to get a fractional result. The value-7 / 2is-3, not-4, in SQLite. - Plan for growth. A 4-byte key runs out at about 2.1 billion rows. Switching a busy table to
BIGINTlater is slow. - Money as integers works if you count the smallest unit (cents) and document it. Divide by 100 only when displaying.
- Phone numbers, postal codes and card numbers are text, not integers: leading zeros matter and arithmetic on them is meaningless.
- Overflow can be silent. SQLite switches to
REALand loses precision. Check large totals. COUNT()andSUM()of integers return integers in most systems, andSUMmay widen to a larger type (bigint,numeric).AVG()returns a decimal or float.- Booleans are often stored as
0and1integers where a boolean type is missing.