NUMERIC and DECIMAL data types in PostgreSQL: Explained by examples

Introduction

Understanding data types is crucial for database schema design, and PostgreSQL offers robust options for storing precise numerical data with its NUMERIC and DECIMAL types. This tutorial illustrates their utility through hands-on examples.

Understanding the Basics

Both the NUMERIC and DECIMAL data types are used to store exact numeric values, where precision is of paramount importance, such as financial calculations. Here’s a basic example of defining these data types in a table:

CREATE TABLE financial_records (
    id SERIAL PRIMARY KEY,
    price NUMERIC(10, 2),
    tax_rate DECIMAL(5, 4)
);

The NUMERIC(10, 2) and DECIMAL(5, 4) definitions specify the maximum number of digits allowed and the number of digits after the decimal point, respectively.

Inserting Data

Insert data into the financial_records table with the correct precision:

INSERT INTO financial_records (price, tax_rate) VALUES (1234567.89, 0.0750);

PostgreSQL will round or throw an error if the inserted value exceeds the specified precision and scale.

Arithmetic with Precision

Calculate values on-the-fly with precision guaranteed:

SELECT id, price, tax_rate, (price * tax_rate) AS tax_amount FROM financial_records;

The tax_amount will be calculated with the NUMERIC type’s precision.

Comparing NUMERIC and DECIMAL

Discover the similarities and differences:

SELECT price::DECIMAL(10, 2), price::NUMERIC(10, 2) FROM financial_records;

Both casts will yield the same result, showcasing the interchangeability of DECIMAL and NUMERIC in PostgreSQL.

Handling Rounding and Precision

Control rounding behavior using database functions:

SELECT ROUND(price * tax_rate, 2) AS rounded_tax_amount FROM financial_records;

Another advanced operation is to modify the scale and precision of an existing data column:

ALTER TABLE financial_records
    ALTER COLUMN tax_rate TYPE DECIMAL(10, 6);

This would widen the tax_rate column to accept higher precision and scale.

Performance Considerations

NUMERIC and DECIMAL columns come with a performance cost. Compare the performance impacts with regular integer types:

EXPLAIN ANALYZE SELECT SUM(price) FROM financial_records;

This explains the query plan and can help you gauge performance overhead.

Advanced Example: Using NUMERIC in Functions

Create a custom function to calculate taxed price:

CREATE OR REPLACE FUNCTION calculate_taxed_price(real_price NUMERIC, tax_rate NUMERIC)
RETURNS NUMERIC AS $
BEGIN
    RETURN (real_price * (1 + tax_rate));
END;
$ LANGUAGE plpgsql;

Use the function in a query:

SELECT id, price, calculate_taxed_price(price, tax_rate) AS taxed_price FROM financial_records;

The original price and the price including the tax are calculated with precision.

Conclusion

The NUMERIC and DECIMAL types in PostgreSQL are indispensable for applications requiring high-precision arithmetic. This tutorial provided the stepping stone to understanding and effectively using these data types, with examples stretching from the basics to advanced use cases.