A Guide to PostgreSQL Numeric Types: Storage, Precision, and Examples

Integer Types

• bigint
  Stores very large whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807 (8 bytes). Use this for big counters or identifiers.
• integer
  Stores whole numbers between -2,147,483,648 and 2,147,483,647 (4 bytes). This is the most common integer type for general purpose use.
• smallint
  Stores whole numbers between -32,768 and 32,767 (2 bytes). Ideal for small integer values like item IDs.

Serial Types (Special Integers)

• serial behaves like an integer, while bigserial uses bigint for larger ID ranges.
• serial and bigserial are auto-incrementing integer types that automatically generate a unique ID whenever a new row is inserted.

Floating-Point Types

• double precision (double)
  Stores numbers with a double-precision floating-point representation (8 bytes). Offers around 15-16 decimal digits of precision. Choose this for calculations requiring higher precision than real.
• real
  Stores numbers with a single-precision floating-point representation (4 bytes). Offers around 6-7 decimal digits of precision. Use this for storing numbers where exact precision isn't critical.

Arbitrary Precision Numbers

• numeric
  Stores fixed-point numbers with user-defined precision (up to 131,072 digits total). This is ideal for financial calculations or storing very precise measurements where exact values are crucial.

• Serial and bigserial are handy for unique identifiers that automatically increment.
• Use numeric for currency, exact measurements, or any scenario demanding maximum precision.
• For floating-point numbers, choose real for general calculations and double precision for higher accuracy.
• Consider the range of values you need to store. Use smallint for small positive or negative numbers, integer for most general cases, and bigint for very large numbers.

---

Table with various Numeric Types

CREATE TABLE products (
  id serial PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  price NUMERIC(8,2) NOT NULL,  -- Stores price with 2 decimal places
  quantity INTEGER NOT NULL,
  in_stock BOOLEAN DEFAULT TRUE
);

This code creates a table named "products" with columns for product ID (serial - auto-incrementing integer), name (string), price (numeric with 8 total digits and 2 decimal places), quantity (integer), and in_stock (boolean).

Inserting data with Numeric Values

INSERT INTO products (name, price, quantity)
VALUES ('T-Shirt', 19.99, 100),
       ('Laptop', 749.50, 25);

This code inserts two rows into the "products" table. Note how the price values use two decimal places as defined in the table schema.

Performing Calculations with Numeric Data

SELECT id, name, price * quantity AS total_value
FROM products;

This code retrieves all products and calculates the total value (price multiplied by quantity) for each using a numeric expression.

Using Serial Type for Auto-Incrementing ID

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_name VARCHAR(50) NOT NULL,
  order_date DATE NOT NULL
);

This code creates a table named "orders" with an auto-incrementing ID (serial), customer name (string), and order date.

---

Character Data Types for Specific Formats

• char(n) or varchar(n)
  If you need to store numbers in a specific format, like product codes with leading zeros (e.g., "000123"), you can use character data types. However, calculations become more complex as you'd have to convert them to numeric types first.

Money Data Type

• money
  PostgreSQL offers a dedicated "money" data type specifically designed for storing currency amounts. It offers some advantages like pre-defined formatting and basic arithmetic operations. However, it has a limited range compared to some numeric types like bigint.

User-Defined Types

• You can create custom data types to encapsulate specific logic related to your numeric data. This can be useful for complex calculations or specialized data structures, but it requires more development effort.

Choosing the Right Option

The best approach depends on your specific needs:

• Explore user-defined types only for very specific needs requiring custom logic.
• Use the "money" type specifically for storing currency.
• If data formatting is crucial (e.g., product codes), character data types might be suitable, but be aware of the limitations for calculations.
• If you need to perform calculations or comparisons, using numeric types (especially numeric or one of the integer/floating-point types) is generally the best choice.