Numeric types

The following table shows the various numeric types:

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 isn't sufficient.

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 are 1 and 0, respectively. Thus, the fractional part is always truncated. Unlike in the C language, PostgreSQL uses the round-half-to-even algorithm when casting a double value to INT:

postgres=# SELECT
        CAST(5.9 AS INT) AS "CAST (5.9 AS INT)",
        CAST(5.1 AS INT) AS "CAST(5.1 AS INT)",
        CAST(-23.5 AS INT) AS "CAST(-23.5 AS INT)" ,
        5.5::INT AS "5.5::INT";

 CAST (5.9 AS INT) | CAST(5.1 AS INT) | CAST(-23.5 AS INT) | 5.5::INT 
-------------------+------------------+--------------------+----------
                 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 of definition for a numeric or 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 5 and a scale of 3. If a numeric type is used to define a column type without precision or scale, the column can store any number with any precision and scale.

If precision isn't required, don't use the numeric and decimal types. Operations on numeric types are slower than floats and double precision.

Floating-point and double precision are inexact; this means that the values in some cases can't be represented in the internal binary format, and are stored as approximations. The full documentation about numeric data types can be found at https://www.postgresql.org/docs/current/static/datatype-numeric.html.

Serial types, namely smallserial, serial, and bigserial, are wrappers on top of smallint, integer, and bigint, respectively. serial types aren't true data types. They're often used as surrogate keys, and by default, they aren't allowed to have a null value. The serial type utilizes the sequences behind the scene. A sequence is a database object that's 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 as serial:

CREATE TABLE customer (
  customer_id SERIAL
);

The preceding code create a sequence and a table. It will set the default value of the customer_id column to the sequence's next function. Finally, it will change the ownership of the sequence to the column. To verify this, let's describe the created objects:

postgres=# \d customer
 Table "public.customer"
 Column      | Type    | Collation | Nullable | Default 
-------------+---------+-----------+----------+-----------------------------------------------
 customer_id | integer |           | not null | nextval('customer_customer_id_seq'::regclass)
postgres=# \d customer_customer_id_seq
 Sequence "public.customer_customer_id_seq"
 Type    | Start | Minimum | Maximum    | Increment | Cycles? | Cache 
---------+-------+---------+------------+-----------+---------+-------
 integer | 1     | 1       | 2147483647 | 1         | no      | 1
Owned by: public.customer.customer_id

The preceding code will generate the following code behind the scenes:

CREATE SEQUENCE customer_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 the serial type, 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.