Skip to content
Tutorials Center
My learning

Topics

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.

SQL
CREATE TABLE counters (
  id BIGINT PRIMARY KEY,
  hits INTEGER NOT NULL DEFAULT 0
);

Try it

SQL Integer division, remainders and big values ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL SQLite uses 64-bit integers and switches to REAL on overflow

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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.
SQL
-- 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, or CAST(x AS REAL)) to get a fractional result. The value -7 / 2 is -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 BIGINT later 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 REAL and loses precision. Check large totals.
  • COUNT() and SUM() of integers return integers in most systems, and SUM may widen to a larger type (bigint, numeric). AVG() returns a decimal or float.
  • Booleans are often stored as 0 and 1 integers where a boolean type is missing.