Numeric data type

Numerical data are divided into exact and approximate, integer and real. Bit values are a separate category.

Exact integers

TypeMemory sizeRange
TINYINT1 bytefrom -128 to 127 (from -27 to 27-1)
from 0 to 255 (from 0 to 28-1)
SMALLINT2 bytefrom -32768 to 32767 (from -215 to 215-1)
from 0 to 65535 (from 0 to 216-1)
MEDIUMINT3 bytefrom -223 to 223-1
from 0 to 224-1
INT
INTEGER
(aliases)
4 bytefrom -231 to 231-1
from 0 to 232-1
BIGINT8 bytefrom -263 to 263-1
from 0 to 264-1

Integers can be declared with the keyword UNSIGNED. In this case, the elements of this column it will not be possible to assign negative values, and the valid range, which takes on the type doubles. So, type TINYINT can take values from -128 to 127, and TINYINT UNSIGNED — from 0 to 255.

Exact real numbers

TypeRange
DEC[(M,D)]
DECIMAL[(M,D)]
(aliases)
Depends on M and D parameters

Type DECIMAL stores exact real values data. It is used when accuracy is critical. For example, when storing financial data.

Usage example:

MySQL 8.1
CREATE TABLE Users (
    ...
    salary DECIMAL(5,2)
);

This example declares that the salary column will store numbers, having a maximum of 5 digits, 2 of which are reserved for decimal part. That is, this column will store values in the range from -999.99 to 999.99.

The syntax DECIMAL is equivalent to DECIMAL(M) and DECIMAL(M,0). By default, the parameter M is 10.

The whole part and the part after the point are stored as 2 separate integers. On Based on this fact, the amount of memory consumed can be easily calculated. So for DECIMAL(5,2) the integer part contains 3 digits and takes 2 byte, part after the point have 2 digits - 1 byte is enough. Total for storage will be spent 3 byte.

Bit numbers

TypeMemory sizeRange
BIT[(M)]M bitFrom 1 to 64 bit, depend on the M parameter
BOOL
BOOLEAN
(aliases)
1 bitOr 0, or 1

Type BIT(M) stores a sequence of bits a given length. By default, the length is 8 bit. If assigned the value in a column of this type uses less than M bit, then zeros are padded on the left. For example, when trying to write the value b'101' to BIT(6) will eventually be stored b'000101'.

Approximate numbers

TypeMemory sizeRange
FLOAT[(M, D)]4 byteMinimum value ±1.17·10-39
Maximum value ±3.4·1038
REAL[(M, D)]
DOUBLE[(M, D)]
(aliases)
8 byteMinimum value ±2.22·10-308
Maximum value ±1.79·10308

Numeric floating point data types can also have a parameter UNSIGNED. As with integer types, this attribute prevents negative storage in the marked column values, but, unlike integer types, the maximum interval for column values remains the same.

Integer numbers

TypeMemory sizeRange
SMALLINT2 bytesfrom -32768 to 32767
INT
INTEGER
(aliases)
4 bytesfrom -2147483648 to 2147483647
BIGINT8 bytesfrom -9223372036854775808 to 9223372036854775807

Auto-increment types

TypeMemory sizeRange
SMALLSERIAL2 bytesfrom 1 to 32767
SERIAL4 bytesfrom 1 to 2147483647
BIGSERIAL8 bytesfrom 1 to 9223372036854775807

The SERIAL types are pseudo-types for creating auto-incrementing columns. SERIAL is equivalent to INTEGER with an automatically created sequence.

Exact real numbers

TypePrecisionRange
DECIMAL[(precision, scale)]
NUMERIC[(precision, scale)]
(aliases)
User-definedUp to 131072 digits before decimal point and up to 16383 digits after

Type NUMERIC stores exact real values data. It is used when accuracy is critical. For example, when storing financial data.

Usage example:

MySQL 8.1
CREATE TABLE Users (
    ...
    salary NUMERIC(10,2)
);

This example declares that the salary column will store numbers having a maximum of 10 digits, 2 of which are reserved for the decimal part. That is, this column will store values in the range from -99999999.99 to 99999999.99.

Approximate numbers

TypeMemory sizePrecisionRange
REAL4 bytes6 digitsfrom 1E-37 to 1E+37
DOUBLE PRECISION8 bytes15 digitsfrom 1E-307 to 1E+308

Floating point types are used for approximate calculations. PostgreSQL also supports special values: Infinity, -Infinity and NaN (not a number).