Introduction to PostgreSQL decimal Data Type

In PostgreSQL, decimal is a fixed precision numeric data type that allows storing high-precision numbers with specified precision and scale. Due to its ability to accurately represent decimal numbers, decimal type is very useful in scenarios that require precise calculations.

decimal is a numeric data type, also known as a fixed-point number type.

Syntax

The syntax for decimal type in PostgreSQL is as follows:

DECIMAL(p, s)

Where p represents precision, which is the total number of digits including the scale, and it can range from 1 to 1000; s represents scale, which can range from 0 to p.

Use Cases

decimal type is commonly used in scenarios that require precise calculations, such as currency calculations, financial reports, etc. In these scenarios, precision and scale are crucial, and decimal type is designed for such purposes.

Example

Here are two examples of using the decimal data type in PostgreSQL to store the results of currency calculations.

First, we create a table called sales to store sales records. The table contains four columns: id, date, amount, and tax, where both amount and tax are of type decimal:

CREATE TABLE sales (
  id SERIAL PRIMARY KEY,
  date DATE,
  amount DECIMAL(10,2),
  tax DECIMAL(10,2)
);

Next, we insert some sales records:

INSERT INTO sales (date, amount, tax) VALUES
  ('2022-01-01', 1234.56, 246.91),
  ('2022-01-02', 5678.90, 1135.78),
  ('2022-01-03', 9876.54, 1975.31);

We query the sales records and calculate the total sales and total tax:

SELECT SUM(amount) AS total_sales, SUM(tax) AS total_tax FROM sales;

Output:

 total_sales | total_tax
-------------+-----------
    16790.00 |   3357.00
(1 row)

Next, we create a table called accounts to store user account information. The table contains three columns: id, name, and balance, where balance is of type decimal:

CREATE TABLE accounts (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  balance DECIMAL(20,2) DEFAULT 0.00
);

We insert some account information:

INSERT INTO accounts (name, balance) VALUES
  ('Alice', 1000.00),
  ('Bob', 2000.00),
  ('Charlie', 3000.00);

We can then use SELECT statements to query the account balances. Here’s an example of querying the balance:

SELECT balance FROM accounts WHERE id = 1;

The above example will return the balance of the account with ID 1.

Next, we demonstrate how to perform a transfer operation. Suppose we want to transfer $100 from the account with ID 1 to the account with ID 2. Here’s an example SQL statement:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

The above example starts a transaction with the BEGIN command, then performs two UPDATE statements to subtract $100 from the balance of the account with ID 1 and add $100 to the balance of the account with ID 2. Finally, the COMMIT command is used to commit the transaction.

Note that the above operations are performed within a transaction. This ensures that the transfer operation is atomic, meaning it either succeeds entirely or fails entirely. If one of the UPDATE operations fails, the entire transaction will be rolled back.

In addition to using transactions, we can also use CHECK constraints to ensure that the balance does not become negative. Here’s an example:

ALTER TABLE accounts ADD CONSTRAINT balance_check CHECK (balance >= 0);

The above example adds a CHECK constraint to the accounts table to ensure that the balance is always greater than or equal to zero. If a transfer operation would result in a negative balance, it would trigger a constraint violation error, preventing the transfer operation.

Conclusion

The decimal data type in PostgreSQL is well-suited for handling currencies and other numbers that require precise calculations. It provides useful features and constraints that ensure data accuracy and integrity.