Numeric Types

Introduction

Postgres is famous for being a feature-rich database, supporting a large list of data types that can be divided into several groups with distinct functions. First, we will explore a very commonly used group, Numeric Types, which includes the following data types:

smallint: 2 bytes, (+/-) 32,767
integer or int: 4 bytes, (+/-) 2,147,483,646 (~2.1 billion)
bigint: 8 bytes, (+/-) 9,223,372,036,854,775,807 (~9.22 million billion)
numeric: 10 bytes, (+/-) 10^131071
- Used to store fixed-precision decimal numbers, never suffering from implicit rounding errors
- Highly suitable for financial, monetary and accounting data
- Flexible size
  - Up to 131,072 digits before the decimal point and 16,383 digits after the decimal point
  - 100% absolute accuracy based on the numeric(Precision, Scale) configuration
    - Precision: The total count of digits that can be stored (including digits both before and after the decimal point, excluding negative/positive signs or decimal points), ranging from 0-1000
    - Scale: The maximum count of digits after the decimal point, ranging from 0-1000
    - The maximum number of digits for the integer part (before the decimal point) will be {Precision} - {Scale}
    - For example, when using NUMERIC(10, 2)
      - The maximum number of digits for the integer part will be {Precision} - {Scale} = 10 - 2 = 8 digits
      - Maximum input value that can be passed:
        Max Positive: 99999999.99 (8 nines in the integer part and 2 nines in the decimal part)
        Min Negative: -99999999.99
Floating-point numbers
- Used to store inexact decimal numbers, based on the IEEE 754 standard
- Offers extremely fast hardware calculation speed, but can cause small rounding errors in the last digits
- Best used for physical measurement data, science, GPS coordinates, etc.
- Includes:
  - real: Single precision floating-point number
    - Around 3.4 x 10^38
    - Accurate to approximately 6 decimal digits
  - double precision: Double precision floating-point number
    - Around 1.7 x 10^308
    - Stores data much more accurately than real, around 15 decimal digits
Auto-incrementing number sequences
- This is not a native storage data type, but rather syntactic sugar for PostgreSQL to automatically create an integer column combined with a Sequence Generator to assign auto-incrementing IDs, including:
  - smallserial: Creates a smallint + Sequence column
  - serial: Creates an integer + Sequence column
  - bigserial: Creates a bigint + Sequence column
money
- The money data type is used to store monetary amounts, automatically adding financial formatting such as currency symbols (like $) and thousands separators based on the server locale settings
- Characteristics: 8 bytes, range is (-/+)92,233,720,368,547,758.08 (around 92 million billion currency units)
- Precision: Fixed at 2 decimal digits (depending on the lc_monetary database setting)
- Although created for currency storage and sounding convenient, this is one of the least recommended data types in practice. The DBA community and PostgreSQL core team both advise using numeric or bigint instead for the following reasons:
  - Locale Dependency: If you back up data from a server configured with one locale (such as using $) and restore it to a server with a different locale (such as using vnđ), the displayed data or decimal separator calculations (. vs ,) may be skewed or fail to parse
  - Difficulty working with Backend (ORM/API): Because Postgres returns the money type as a formatted string (such as $100.00 rather than the number 10000), the data received during backend processing will be a String type and must be parsed into a number before calculations can be made
  - Loss of precision during multiplication / division:
    - When dividing two money values, Postgres returns a double precision floating-point number
    - When dividing a money type by an integer, it truncates the decimal part
    - Cannot store amounts smaller than a cent, such as Crypto requiring 8-18 decimal digits, or transaction fees of $0.0001
  - Does not store currency type
    - The money column does not store currency code information (USD, EUR, VND...), storing only an 8 bytes number and appending the currency symbol according to system configuration
    - Therefore, it cannot support global systems with Multi-currency requirements
- The standard real-world approach to handling Multi-currency management across multiple currencies in the same database is to use a decoupled design, storing the numerical value separately (e.g., numeric(15, 4)) and storing the ISO 4217 currency code separately (e.g., char(3))

Detail

Create the table as follows:

CREATE TABLE test (
  col_smallint SMALLINT,
  
  col_integer INTEGER,
  col_int INT,
  
  col_bigint BIGINT,

  col_numeric NUMERIC,
  col_numeric_10_2 NUMERIC(10, 2),
  
  col_real REAL,
  col_double DOUBLE PRECISION,
  col_money MONEY,
 
  col_small_serial SMALLSERIAL,
  col_serial SERIAL,
  col_bigserial BIGSERIAL,

  col_generated_default INT GENERATED BY DEFAULT AS IDENTITY,
  col_generated_always INT GENERATED ALWAYS AS IDENTITY,

  col_virtual_generated_columns_default NUMERIC GENERATED ALWAYS AS (col_bigint * (1 + col_integer - col_smallint) / col_real),
  col_virtual_generated_columns_virtual NUMERIC GENERATED ALWAYS AS (col_bigint * (1 + col_integer - col_smallint) / col_real) VIRTUAL,
  col_virtual_generated_columns_stored NUMERIC GENERATED ALWAYS AS (col_bigint * (1 + col_integer - col_smallint) / col_real) STORED
)

Use the following queries to verify data types:

SMALLINT

INSERT INTO test(col_smallint) VALUES (-32768);
INSERT INTO test(col_smallint) VALUES (-32769);
INSERT INTO test(col_smallint) VALUES (32767);
INSERT INTO test(col_smallint) VALUES (32768);

INTEGER

INSERT INTO test(col_integer) VALUES (-2147483648);
INSERT INTO test(col_integer) VALUES (-2147483649);
INSERT INTO test(col_integer) VALUES (2147483646);
INSERT INTO test(col_integer) VALUES (2147483647);

BIGINT

INSERT INTO test(col_bigint) VALUES (-9223372036854775808);
INSERT INTO test(col_bigint) VALUES (-9223372036854775809);
INSERT INTO test(col_bigint) VALUES (9223372036854775807);
INSERT INTO test(col_bigint) VALUES (9223372036854775808);

NUMERIC

INSERT INTO test(col_numeric) VALUES (-1e131071);
INSERT INTO test(col_numeric) VALUES (-1e131072);
INSERT INTO test(col_numeric) VALUES (1e131071);
INSERT INTO test(col_numeric) VALUES (1e131072);

INSERT INTO test(col_numeric) VALUES (-1e-16383);
INSERT INTO test(col_numeric) VALUES (-1e-16384);
INSERT INTO test(col_numeric) VALUES (1e-16383);
INSERT INTO test(col_numeric) VALUES (1e-16384);

INSERT INTO test(col_numeric_10_2) VALUES (-99999999.99);
INSERT INTO test(col_numeric_10_2) VALUES (-100000000);
INSERT INTO test(col_numeric_10_2) VALUES (99999999.99);
INSERT INTO test(col_numeric_10_2) VALUES (100000000);

SELECT
  pg_column_size('1e131071'::numeric) AS bytes_numeric_max,
  pg_column_size(99999999.99::numeric(10, 2)) AS bytes_numeric_10_2;

CREATE TABLE test (
 col_numeric NUMERIC(1001)
)
CREATE TABLE test (
 col_numeric NUMERIC(1001, 1001)
)

Statement 1: -1e131071 is -10^131071 and -1e-16383 is -10^-16383 (16,383 decimal places)

This demonstrates using NUMERIC with decimals after the comma

Statement 2: NUMERIC(10, 2)

Query 3: Used to check the size of NUMERIC

Statement 4: You can use this statement to check the min/max precision and scale supported by NUMERIC

REAL

INSERT INTO test(col_real) VALUES (-3.4028235e38);
INSERT INTO test(col_real) VALUES (-3.4028236e38);
INSERT INTO test(col_real) VALUES (3.4028235e38);
INSERT INTO test(col_real) VALUES (3.4028236e38);

INSERT INTO test(col_real) VALUES (-1e-45);
INSERT INTO test(col_real) VALUES (-1e-46);
INSERT INTO test(col_real) VALUES (1e-45);
INSERT INTO test(col_real) VALUES (1e-46);

INSERT INTO test(col_real) VALUES ('NaN');
INSERT INTO test(col_real) VALUES ('-Infinity');
INSERT INTO test(col_real) VALUES ('Infinity');

Statement 1: This is an INSERT of integer values where -3.4028235e38 is -3.4028235 * 10^38

Statement 2: This is an INSERT of decimal values where -1e-45 is -10^-45

Statement 3: Also supports values like NaN, Infinity and -Infinity

DOUBLE PRECISION

INSERT INTO test(col_double) VALUES (-1.7976931348623158e308);
INSERT INTO test(col_double) VALUES (-1.7976931348623159e308);
INSERT INTO test(col_double) VALUES (1.7976931348623158e308);
INSERT INTO test(col_double) VALUES (1.7976931348623159e308);

INSERT INTO test(col_double) VALUES (-1e-323);
INSERT INTO test(col_double) VALUES (-1e-324);
INSERT INTO test(col_double) VALUES (1e-323);
INSERT INTO test(col_double) VALUES (1e-324);

INSERT INTO test(col_double) VALUES ('NaN');
INSERT INTO test(col_double) VALUES ('-Infinity');
INSERT INTO test(col_double) VALUES ('Infinity');

SELECT 0.1::numeric + 0.2::numeric
SELECT 0.1::real + 0.2::real
SELECT 0.1::double precision + 0.2::double precision

SELECT 0.1::real + 0.2::numeric
SELECT 0.1::double precision + 0.2::real
SELECT 0.1::double precision + 0.2::numeric

Statement 1: This is an INSERT of integer values where -1.7976931348623158e308 is -1.7976931348623158 * 10^308

Statement 2: This is an INSERT of decimal values where -1e-323 is -10^-323

Statement 3: Also supports values like NaN, Infinity and -Infinity

Statement 4: When using DOUBLE PRECISION, be careful with calculations as results may contain precision inaccuracies

You can see that this does not happen with NUMERIC or REAL, only DOUBLE PRECISION

Note that when calculating between different data types, Postgres implicitly casts to the larger type. If cast to DOUBLE PRECISION, calculation results will experience inaccuracies like this:

MONEY

INSERT INTO test(col_money) VALUES (-92233720368547758.08);
INSERT INTO test(col_money) VALUES (-92233720368547758.09);
INSERT INTO test(col_money) VALUES (92233720368547758.07);
INSERT INTO test(col_money) VALUES (92233720368547758.08);

INSERT INTO test(col_money) VALUES (-0.001);
INSERT INTO test(col_money) VALUES (-0.01);
INSERT INTO test(col_money) VALUES (0.001);
INSERT INTO test(col_money) VALUES (0.01);

Statement 1: Min/max and negative/positive values supported by the MONEY type

Statement 2: The MONEY type can only INSERT values where:

Min Negative is -0.01
Min Positive is 0.01
If you INSERT a smaller value, the stored result will be 0

SMALLSERIAL, SERIAL and BIGSERIAL

INSERT INTO test(col_small_serial) VALUES (1000);
INSERT INTO test(col_serial) VALUES (1001);
INSERT INTO test(col_bigserial) VALUES (1002);

SELECT setval(
    pg_get_serial_sequence('test', 'col_small_serial'), 
    (SELECT MAX(col_small_serial) FROM test)
);

Statement 1: When using SERIAL types:

If you do not INSERT a value, it auto-increments
You can also INSERT an arbitrary value, after which auto-increment continues from the previous counter position
- For example, if the current sequence value is 100
- You manually INSERT 1000
- If you omit inserting a value for that column next, the generated value will be 101

Query 2: Used to reset the counter to the maximum value in that column

Identity Column

INSERT INTO test(col_generated_default) VALUES (100);
INSERT INTO test(col_generated_always) VALUES (100);
INSERT INTO test (col_generated_always) OVERRIDING SYSTEM VALUE VALUES (100);

Using GENERATED is a more effective alternative to using SERIAL

Using GENERATED BY DEFAULT works similarly to SERIAL, still allowing arbitrary value INSERTs
Using GENERATED ALWAYS enforces stricter rules, preventing arbitrary value INSERTs to avoid data misalignment risks
To override column values when using GENERATED ALWAYS, use OVERRIDING SYSTEM VALUE

Virtual Generated Columns

INSERT INTO test(col_integer, col_bigint, col_real) VALUES (1, 2, 3);
INSERT INTO test(col_smallint, col_integer, col_bigint, col_real) VALUES (1, 2, 3, 4);

SELECT
  col_virtual_generated_columns_default,
  col_virtual_generated_columns_virtual,
  col_virtual_generated_columns_stored
FROM test

CREATE INDEX idx_test_col_virtual_generated_columns_default ON test(col_virtual_generated_columns_default);
CREATE INDEX idx_test_col_virtual_generated_columns_virtual ON test(col_virtual_generated_columns_virtual);
CREATE INDEX idx_test_col_virtual_generated_columns_stored ON test(col_virtual_generated_columns_stored);

Using Virtual Generated Columns is a new feature introduced in Postgres version 18 that automatically calculates values based on other columns. If any referenced value is NULL, the final calculated result is also NULL

The default behavior when using GENERATED ALWAYS is VIRTUAL, meaning omitting this keyword still defaults to VIRTUAL
- Values are not stored on disk. Every time you execute a SELECT query, Postgres calculates the expression on-the-fly
- Read speed (SELECT) is slower due to CPU calculation overhead per query
- Write speed (INSERT/UPDATE) is faster since no extra column value is written to disk
- Consumes no extra disk storage space
Using STORED (supported since Postgres 12):
- Values are calculated and saved directly to disk upon row INSERT or UPDATE
- SELECT speed is very fast as values are read directly from disk
- Write speed (INSERT/UPDATE) is slightly slower because Postgres evaluates the formula before writing to disk
- Consumes additional disk space
- Allows creating an Index directly on STORED columns