How to Store Decimal Values in SQL

1. Introduction

In database management, the precision and accuracy of numerical data are crucial. This is especially important when dealing with financial calculations, scientific measurements, or any application where exact figures are vital.

Storing decimal values in SQL databases is a common task. However, this requires careful consideration to ensure the data remains accurate and reliable.

In this tutorial, we’ll explore how to store decimal values in SQL databases effectively. In addition, we’ll cover the available data types, how to define them correctly, and how to avoid common pitfalls. Moreover, the examples we cover will be applicable across various SQL databases, like MySQL, PostgreSQL, and SQL Server.

2. Understanding Decimal Data Types

SQL databases provide specific data types for storing decimal values. The most commonly used data types are DECIMAL and NUMERIC. These data types are synonymous in most databases, providing a way to store exact numeric values with a specified precision and scale:

• Precision refers to the total number of digits that can be stored, both to the left and right of the decimal point.
• Scale refers to the number of digits that can be stored to the right of the decimal point.

For example, defining a column as DECIMAL(10,2) allows the storage of numbers with up to 10 digits, with 2 of those digits being after the decimal point. The remaining 8 digits would be to the left of the decimal point.

Let’s take a look at an example:

CREATE TABLE FinancialRecords (
    transaction_id INT PRIMARY KEY,
    amount DECIMAL(10,2)
);

In this case, the amount column can store values like 12345678.90 or -987654.32.

3. Storing Decimal Values in Different SQL Databases

It’s essential to define precision and scale according to the expected range of values when creating tables that require decimal storage. In this section, we’ll explore how to properly store decimal values across MySQL, PostgreSQL, and SQL Server.

Since the same query applies to these databases, we’ll use the PostgreSQL database for our illustrations:

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    total_amount DECIMAL(12,2)
);

Here, the total_amount field can store up to 12 digits, including 2 decimal places, allowing us to store large financial transactions accurately.

For example, let’s insert values for total_amount that are within the specified decimal range:

-- Inserting a value within the range
INSERT INTO Orders (order_id, total_amount) VALUES (1, 123456.78);
INSERT 0 1

-- Inserting a value exactly at the maximum limit
INSERT INTO Orders (order_id, total_amount) VALUES (2, 9999999999.99);
INSERT 0 1

SELECT * FROM orders;
 order_id | total_amount 
----------+---------------
        1 |     123456.78
        2 | 9999999999.99
(2 rows)

This result shows that the system stored both values as expected, fitting perfectly within the constraints of the DECIMAL(12,2) type.

Let’s now explore the case when the scale of an inserted value exceeds the declared scale of the DECIMAL type:

INSERT INTO Orders (order_id, total_amount) VALUES (3, 123.456);
INSERT 0 1

SELECT * FROM orders;
 order_id | total_amount 
----------+---------------
        1 |     123456.78
        2 | 9999999999.99
        3 |        123.46
(3 rows)

In this scenario, the system automatically converts the value we entered with three decimal places (123.456) to two decimal places (123.46). So, this is how an overflow in decimal places is handled.

On the other hand, let’s see how the system handles values with more than 12 integers:

-- Attempting to insert a value that exceeds the total number of digits allowed
INSERT INTO Orders (order_id, total_amount) VALUES (4, 10000000000000.00);
ERROR:  numeric field overflow
DETAIL:  A field with precision 12, scale 2 must round to an absolute value less than 10^10.

As the result shows, the system displays a numeric field overflow error message, in addition to details explaining what happened. So, when a situation like this occurs, the system doesn’t insert the value into the table.

4. Common Pitfalls and Best Practices

When dealing with decimal values, several common pitfalls can arise. These include precision loss, unintended rounding, and incorrect type selection. To avoid these issues, let’s consider two best practices:

• Always define precision and scale: Failure to define precision and scale can lead to defaults that may not suit the data, potentially leading to truncation or rounding errors.
• Test with real data: Before finalizing a schema, test the storage of various decimal values to ensure that precision and scale are adequate for all expected data.

5. Use Case

Let’s explore a use case where precision is extremely important. For instance, in the financial industry, precise storage of decimal values is essential.

As an example, when we calculate interest on savings accounts, even a tiny discrepancy can lead to significant losses over time. In a case like this, we can round the value to two decimal places and designate leading integer numbers.

So, let’s explore how we can create a table that will hold the financial data:

CREATE TABLE SavingsAccounts (
    account_id INT PRIMARY KEY,
    balance DECIMAL(15,2)
);

This setup allows us to track account balances accurately, ensuring that we record all transactions with the necessary precision and scale.

6. Conclusion

In this article, we explored the storage of decimal values in SQL, covering different databases, potential pitfalls, and real-world applications. In essence, proper implementation of these techniques will lead to more accurate and trustworthy data in any SQL-based system.