The following table shows the various numeric types:
|
Name |
Comments |
Size |
Range |
|
smallint |
SQL equivalent: Int2 |
2 bytes |
-32,768 to +32,767 |
|
Int |
SQL equivalent: Int4 Integer is an alias for int |
4 bytes |
-2,147,483,648 to +2,147,483,647 |
|
Bigint |
SQL equivalent: Int8 8 bytes |
8 bytes |
-9,223,372,036,854,775,808 to +9,223,372,036,854,775,807 |
|
Numeric or decimal |
No difference in PostgreSQL |
Variable |
Up to 131,072 digits before the decimal point; up to 16,383 digits after the decimal point |
|
real |
Special values: -Infinity, Infinity, NaN |
4 bytes |
Platform-dependent, at least six-digit precision. Often, the range is 1E-37 to 1E+37. |
|
Double |
Special values: -Infinity, Infinity, NaN |
8 bytes |
Platform dependent, at least 15-digit precision. Often, the range is 1E-307 to 1E+308. |
PostgreSQL supports various mathematical operators and functions, such as geometric functions and bitwise operations. The smallint data type can be used to save disk space, while bigint can be used if the integer range is not sufficient.
Serial types, namely smallserial, serial, and bigserial are wrappers on top of smallint, int, and biginteger respectively. Serial types are often used as surrogate keys, and by default, they are not allowed to have a null value. The serial type utilizes the sequences behind the scene. A sequence is a database object that is used to generate sequences by specifying the minimum, maximum, and increment values. For example, the following code creates a table customer with a customer_id column:
CREATE TABLE customer (
customer_id SERIAL
);
This will generate the following code behind the scenes:
CREATE SEQUENCE custome_customer_id_seq;
CREATE TABLE customer (
customer_id integer NOT NULL DEFAULT nextval('customer_customer_id_seq')
);
ALTER SEQUENCE customer_customer_id_seq OWNED BY customer.Customer_id;
When creating a column with type serial, remember the following things:
- A sequence will be created with the name tableName_columnName_seq. In the preceding example, the sequence name is customer_customer_id_seq.
- The column will have a Not Null constraint.
- The column will have a default value generated by the nextval() function.
- The sequence will be owned by the column, which means that the sequence will be dropped automatically if the column is dropped.
Similar to the C language, the result of an integer expression is also an integer. So, the results of the mathematical operations 3/2 and 1/3 is 1 and 0 respectively. Thus, the fractional part is always truncated. Unlike in C, PostgreSQL uses round half to even algorithm when casting a double value to int:
postgres=# SELECT CAST (5.9 AS INT) AS rounded_up, CAST(5.1 AS INTEGER) AS rounded_down, CAST (-23.5 AS INT) as round_negative , 5.5::INT AS another_syntax;
rounded_up | rounded_down | round_negative | another_syntax
------------+--------------+----------------+----------------
6 | 5 | -24 | 6
(1 row)
postgres=# SELECT 2/3 AS "2/3", 1/3 AS "1/3", 3/2 AS "3/2";
2/3 | 1/3 | 3/2
-----+-----+-----
0 | 0 | 1
(1 row)
The numeric and decimal types are recommended for storing monetary and other amounts where precision is required. There are three forms for defining a numeric or a decimal value:
- Numeric (precision, scale)
- Numeric (precision)
- Numeric
Precision is the total number of digits, while scale is the number of digits of the fraction part. For example, the number 12.344 has a precision of five and a scale of three. If a numeric type is used to define a column type without precision or scale, then the column can store any number with any precision and scale.
If precision is not required, do not use the numeric and decimal types. Operations on numeric types are slower than floats and double precision.
Floating point and double precision are inexact; that means that the values in some cases cannot be represented in the internal binary format, and are stored as approximation. The full documentation about numeric data types can be found at https://www.postgresql.org/docs/10/static/datatype-numeric.htm