PostgreSQL Numeric Types

Introduction

When designing a database, choosing the right data type for your columns is essential for both data integrity and performance. PostgreSQL offers a rich set of numeric data types that can handle everything from small integers to high-precision decimal values.

In this guide, we'll explore the various numeric types available in PostgreSQL, their storage requirements, ranges, and when to use each one. By understanding these options, you'll be able to make informed decisions when designing your database schema.

Basic Numeric Types

PostgreSQL provides several numeric types that can be categorized into two main groups:

1. Integer types: Store whole numbers without fractional components
2. Floating-point and decimal types: Store numbers with fractional components

Let's explore each category in detail.

Integer Types

PostgreSQL offers four types of integers, each with different storage sizes and ranges:

Type	Storage Size	Range	Use Case
smallint	2 bytes	-32,768 to 32,767	Small-range integer values
integer	4 bytes	-2,147,483,648 to 2,147,483,647	General-purpose integer values
bigint	8 bytes	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807	Large-range integer values
serial	4 bytes	1 to 2,147,483,647	Auto-incrementing integer values
bigserial	8 bytes	1 to 9,223,372,036,854,775,807	Auto-incrementing large integer values

Example: Creating Tables with Integer Types

sql

CREATE TABLE product (
    product_id serial PRIMARY KEY,
    category_id smallint NOT NULL,
    quantity integer NOT NULL,
    total_sales bigint
);

In this example:

• product_id is a serial type that automatically increments with each new record
• category_id is a smallint since we don't expect to have many categories
• quantity is an integer for general-purpose counting
• total_sales is a bigint to accommodate potentially large sales numbers

When to Use Each Integer Type

• Use smallint when you're certain the values will be within its range (-32,768 to 32,767), such as for days in a month or hours in a year.
• Use integer for most counting needs - it's the default choice for whole numbers.
• Use bigint when dealing with very large numbers, like population counts or financial calculations involving cents.
• Use serial or bigserial for auto-incrementing primary keys.

Floating-Point Types

PostgreSQL offers two floating-point types that conform to the IEEE 754 standard:

Type	Storage Size	Range	Precision
real	4 bytes	6 decimal digits precision	Single precision
double precision	8 bytes	15 decimal digits precision	Double precision

Example: Working with Floating-Point Types

sql

CREATE TABLE sensor_reading (
    reading_id serial PRIMARY KEY,
    temperature real,
    precise_measurement double precision
);

INSERT INTO sensor_reading (temperature, precise_measurement)
VALUES (23.5, 23.5123456789012);

SELECT * FROM sensor_reading;

Output:

 reading_id | temperature | precise_measurement 
------------+-------------+---------------------
          1 |        23.5 |      23.5123456789012

Important Considerations with Floating-Point Types

Floating-point types can have precision issues due to how they're stored in binary format. For example:

sql

SELECT 0.1::real + 0.2::real;

Output:

 ?column?
----------
 0.30000001

Notice how the result isn't exactly 0.3. This is because floating-point numbers can't exactly represent some decimal fractions in binary. For financial calculations or any scenario where exact decimal representation is required, you should use the numeric type instead.

Decimal Types

PostgreSQL provides the numeric (or decimal) type for storing exact decimal values:

Type	Storage	Range	Use Case
numeric(p,s)	Variable	Up to 131,072 digits before decimal point; up to 16,383 digits after decimal point	Exact decimal calculations

Where:

• p is the precision (total number of significant digits)
• s is the scale (number of digits after the decimal point)

Example: Working with Numeric Type

sql

CREATE TABLE financial_transaction (
    transaction_id serial PRIMARY KEY,
    amount numeric(10,2) NOT NULL,
    tax_rate numeric(5,4) NOT NULL,
    calculated_tax numeric(10,2)
);

INSERT INTO financial_transaction (amount, tax_rate, calculated_tax)
VALUES (199.99, 0.0875, 199.99 * 0.0875);

SELECT * FROM financial_transaction;

Output:

 transaction_id | amount  | tax_rate | calculated_tax 
----------------+---------+----------+----------------
              1 | 199.99  | 0.0875   | 17.50

When to Use Numeric vs. Floating-Point

• Use numeric when:

  • You need exact decimal representation (e.g., financial calculations)
  • You're working with values where precision is critical
  • You need to control the exact number of decimal places

• Use floating-point types when:

  • Performance is more important than exact precision
  • You're working with scientific calculations
  • The data naturally has some imprecision (e.g., sensor readings)

Money Type

PostgreSQL also provides a special money type for currency values:

Type	Storage Size	Range
money	8 bytes	-92,233,720,368,547,758.08 to +92,233,720,368,547,758.07

Example: Using the Money Type

sql

CREATE TABLE product_price (
    product_id integer PRIMARY KEY,
    price money NOT NULL
);

INSERT INTO product_price VALUES (1, '$99.99');
INSERT INTO product_price VALUES (2, '€50.00');

SELECT * FROM product_price;

Output (depends on your locale settings):

 product_id |  price  
------------+---------
          1 | $99.99
          2 | $50.00

Considerations with Money Type

The money type has some important characteristics to be aware of:

1. It's tied to the locale settings of the database
2. It handles currency symbols automatically
3. It has fixed precision (2 decimal places)
4. It's more efficient for storage and calculations than numeric

However, the money type can cause issues with internationalization and when performing complex calculations. Many database experts recommend using numeric(precision,2) instead for better portability.

Practical Examples

Let's look at some real-world scenarios where you would use different numeric types.

Example 1: E-Commerce Database

sql

CREATE TABLE products (
    product_id serial PRIMARY KEY,
    name varchar(100) NOT NULL,
    price numeric(10,2) NOT NULL,
    stock_quantity integer NOT NULL,
    weight real,
    average_rating real
);

CREATE TABLE orders (
    order_id bigserial PRIMARY KEY,
    user_id integer NOT NULL,
    total_amount numeric(12,2) NOT NULL,
    tax_amount numeric(10,2) NOT NULL,
    discount_percentage numeric(5,2)
);

CREATE TABLE order_items (
    order_item_id serial PRIMARY KEY,
    order_id bigint REFERENCES orders(order_id),
    product_id integer REFERENCES products(product_id),
    quantity smallint NOT NULL,
    unit_price numeric(10,2) NOT NULL
);

In this e-commerce example:

• We use serial and bigserial for auto-incrementing IDs
• numeric(10,2) for prices to ensure exact decimal representation
• integer for stock quantities
• real for weights and ratings where exact precision isn't critical
• smallint for order item quantities since they're typically small numbers

Example 2: Scientific Data Collection

sql

CREATE TABLE experiment_results (
    result_id serial PRIMARY KEY,
    experiment_number smallint NOT NULL,
    temperature double precision NOT NULL,
    pressure double precision NOT NULL,
    reaction_time numeric(8,3) NOT NULL,
    catalyst_amount numeric(5,4) NOT NULL
);

INSERT INTO experiment_results 
(experiment_number, temperature, pressure, reaction_time, catalyst_amount)
VALUES 
(1, 98.6543, 1.01325, 120.500, 0.0025),
(2, 105.3241, 2.50668, 90.750, 0.0050);

SELECT * FROM experiment_results;

Output:

 result_id | experiment_number | temperature | pressure | reaction_time | catalyst_amount 
-----------+-------------------+-------------+----------+---------------+-----------------
         1 |                 1 |    98.6543  |  1.01325 |       120.500 |          0.0025
         2 |                 2 |   105.3241  |  2.50668 |        90.750 |          0.0050

In this scientific example:

• double precision is used for temperature and pressure readings where high precision is needed
• numeric(8,3) for reaction time to get exact decimal representation with 3 decimal places
• numeric(5,4) for catalyst amount where very small precise values need to be stored

Performance Considerations

When working with numeric types, keep these performance considerations in mind:

1. Storage space: Integer types require less storage than floating-point types, which require less than numeric types
2. Computation speed: Calculations on integer types are faster than floating-point, which are faster than numeric
3. Indexing: Indexes on smaller types (like smallint) perform better than on larger types

Common Pitfalls and How to Avoid Them

1. Integer Overflow

When a calculation results in a value outside the range of the data type, overflow occurs:

sql

-- This will cause overflow
SELECT 32767::smallint + 1;

To avoid this, use a larger integer type or check for potential overflow conditions.

2. Floating-Point Precision Issues

As mentioned earlier, floating-point types can have precision issues:

sql

SELECT 0.1::float + 0.2::float = 0.3::float;

Output:

 ?column? 
----------
 f

To avoid this, use the numeric type for exact decimal calculations:

sql

SELECT 0.1::numeric + 0.2::numeric = 0.3::numeric;

Output:

 ?column? 
----------
 t

3. Using the Wrong Type for the Job

A common mistake is using real or double precision for monetary values:

sql

-- DON'T DO THIS for financial data
CREATE TABLE bad_financial_records (
    amount double precision
);

-- DO THIS instead
CREATE TABLE good_financial_records (
    amount numeric(10,2)
);

Summary

PostgreSQL offers a versatile range of numeric types to handle different requirements:

• Integer types (smallint, integer, bigint) for whole numbers
• Auto-incrementing integers (serial, bigserial) for sequence-generated values
• Floating-point types (real, double precision) for scientific calculations
• Exact decimal types (numeric, decimal) for financial and precise calculations
• Money type for currency values with fixed precision

Choosing the right numeric type depends on:

1. The range of values you need to store
2. Whether you need exact decimal precision
3. Performance considerations
4. Storage requirements

By selecting appropriate numeric types for your PostgreSQL database, you'll ensure data integrity while optimizing for performance and storage efficiency.

Exercises

1. Create a table to store product information with appropriate numeric types for:

   • Product ID
   • Price
   • Weight
   • Stock quantity
   • Discount percentage

2. Write SQL queries to calculate:

   • The total value of inventory (price × quantity)
   • A 15% discount on all prices
   • The average price of products

3. Experiment with floating-point precision by calculating the sum of 0.1 ten times using both float and numeric types. Compare the results.

Additional Resources

• PostgreSQL Official Documentation on Numeric Types
• PostgreSQL Precision Calculator - A tool to help visualize numeric ranges
• Floating Point Math in PostgreSQL