PostgreSQL | Data Types | Numeric Types (integer, decimal, double precision, etc.)
This page explains how to use numeric types among the data types available in PostgreSQL. Numeric types include integer data types (smallint, integer, bigint), exact numeric types (numeric, decimal), and floating-point data types (real, double precision). Auto-incrementing types are explained on the next page.
Integer Types (smallint, integer, bigint)
First, here are the integer data types. There are three data types with different ranges of values.
| Type | Size | Range | Alias |
|---|---|---|---|
| smallint | 2 bytes | -32768 to +32767 | int2 |
| integer | 4 bytes | -2147483648 to +2147483647 | int, int4 |
| bigint | 8 bytes | -9223372036854775808 to +9223372036854775807 | int8 |
Because these are integers, they cannot handle numbers with decimal points. If you store a number with a decimal point, it is converted to an integer and stored. Each data type has a fixed range of values that can be stored, and an error occurs if you try to store a value outside that range.
For example, create the following table.
mydb=# create table numtest1 (num1 smallint, num2 integer, num3 bigint);
CREATE TABLE
mydb=#
Values can be stored if they are within the range of each column’s data type. For example, store the following data.
mydb=# insert into numtest1 values (8000, 350000, 4000000000);
INSERT 0 1
mydb=#
If you try to store a value outside the range, an error occurs. For example, the num1 column is smallint, so the range of values it can store is -32768 to +32767. Try to store 50000, which exceeds this range.
mydb=# insert into numtest1 values (50000, 50000, 50000);
ERROR: smallint out of range
mydb=#
Because a value outside the range that can be stored in the num1 column was inserted, the error “smallint out of range” occurred.
Exact Numeric Types (numeric, decimal)
Next are exact numeric types.
| Type | Size | Description | Range | Alias |
|---|---|---|---|---|
| numeric | variable | user-specified precision, exact | up to 131072 digits before the decimal point and up to 16,383 digits after it | decimal |
The numeric and decimal types can store very large numbers and perform exact calculations without rounding error. However, processing is much slower than integer and floating-point data types, so use care.
Specify the numeric type in one of the following forms.
NUMERIC(precision, scale) NUMERIC(precision) NUMERIC
precision specifies the maximum total number of digits, and scale specifies the number of digits after the decimal point. For example, numeric(5, 2) can store values with two digits after the decimal point and five total digits of precision, so the range of values that can be stored is -999.99 to 999.99.
If scale is omitted, it is treated as 0. If both precision and scale are omitted, the maximum possible precision and number of decimal digits are used.
For example, create the following table.
mydb=# create table numtest2 (num numeric(5, 2));
CREATE TABLE
mydb=#
Values can be stored if they are within the range of the column’s data type. For example, store the following three values.
mydb=# insert into numtest2 values (45), (34.25), (-752.4);
INSERT 0 3
mydb=#
If you try to store a value that exceeds the integer-part range, an error occurs. The num column is numeric(5, 2), so the range of values it can store is -999.99 to +999.99. Try to store 1500.2, which exceeds this range.
mydb=# insert into numtest2 values (1500.2);
ERROR: numeric field overflow
DETAIL: A field with precision 5, scale 2 must round to an absolute value less than 10^3.
mydb=#
Because a value outside the range that can be stored in the num column was inserted, a “numeric field overflow” error occurred.
If the number of digits after the decimal point exceeds the specified range, it does not become an error; the value is converted and stored with the specified number of digits. For example, insert the following two values.
mydb=# insert into numtest2 values (32.245),(-8.5224);
INSERT 0 2
mydb=# select * from numtest2;
num
---------
45.00
34.25
-752.40
32.25
-8.52
(5 rows)
mydb=#
32.245 was stored as 32.25, and -8.5224 was stored as -8.52.
Floating-Point Data Types (real, double precision)
Finally, here are floating-point data types. There are two data types with different ranges of values.
| Type | Size | Description | Range | Alias |
|---|---|---|---|---|
| real | 4 bytes | variable precision, inexact | at least 6 decimal digits of precision, roughly 1E-37 to 1E+37 | float4 |
| double precision | 8 bytes | variable precision, inexact | at least 15 decimal digits of precision, roughly 1E-307 to 1E+308 | float8 |
Floating-point values can introduce errors when stored and retrieved, so they are not suitable for exact calculations.
For example, create the following table.
mydb=# create table numtest3 (num1 real, num2 double precision);
CREATE TABLE
mydb=#
Values can be stored if they are within the range of the column’s data type. For example, store the following data.
mydb=# insert into numtest3 values (15.775, 812.5532245);
INSERT 0 1
mydb=# select * from numtest3;
num1 | num2
----------+--------------------
15.775 | 812.5532245
(1 row)
mydb=#
If you try to store a value with more digits than each data type can handle, it may be rounded when stored. For example, store the following data.
mydb=# insert into numtest3 values (9.4475658, 52.75120024568652456);
INSERT 0 1
mydb=# select * from numtest3;
num1 | num2
----------+--------------------
15.775 | 812.5532245
9.447566 | 52.751200245686526
(2 rows)
mydb=#
9.4475658 was stored as 9.447566, and 52.75120024568652456 was rounded and stored as 52.751200245686526.
If you try to store a value outside the range, an error occurs. For example, try to store the following values.
mydb=# insert into numtest3 values (4.8e50, 4.8e50);
ERROR: "480000000000000000000000000000000000000000000000000" is out of range for type real
mydb=#
Because a value outside the range that can be stored was inserted, an out-of-range error occurred.
–
This page explained how to use numeric types among the data types available in PostgreSQL.