What Is Numeric Data Type in PostgreSQL?

The Numeric data type in PostgreSQL is used to store numbers with a large number of decimal places or significant digits. It is commonly used for financial and scientific calculations where precision is crucial. The numeric data type provides the ability to store numbers with up to 131,072 digits before the decimal point and up to 16,383 digits after the decimal point.

The numeric data type in PostgreSQL provides precise storage and calculations for decimal numbers, unlike the floating-point data types that may introduce rounding errors. When using the numeric data type, you can rely on accurate results for your calculations.

To define a column with the numeric data type in PostgreSQL, you can use the following syntax:

CREATE TABLE example_table (
id SERIAL PRIMARY KEY,
value NUMERIC(precision, scale)
);

The precision parameter represents the total number of digits that can be stored, while the scale parameter represents the number of digits allowed after the decimal point. For example, if you define a column with NUMERIC(10, 2), it can store numbers with up to 10 digits and two decimal places.

You can perform various mathematical operations on columns of numeric data type such as addition (+), subtraction (-), multiplication (*), division (/), and modulus (%).

Example:

Let’s consider an example where we have a table called “products” with two columns: “price” and “quantity”. Both columns are defined as numeric data types.

• Create Table:

• CREATE TABLE products (
      id SERIAL PRIMARY KEY,
      price NUMERIC(10, 2),
      quantity NUMERIC(5)
  );

• Insert Data:

• INSERT INTO products (price, quantity)
  VALUES (9.99, 10),
         (19.99, 5),
         (4.99, 20);

• Perform Calculation:

• SELECT price * quantity AS total_value
  FROM products;

In the example above, we first create a table called “products” with two columns: “price” and “quantity”. The “price” column is defined as NUMERIC(10, 2), allowing up to 10 digits with two decimal places. The “quantity” column is defined as NUMERIC(5), allowing up to five digits.

We then insert some sample data into the table and perform a calculation to get the total value of each product by multiplying the price and quantity columns.

The numeric data type in PostgreSQL provides precise storage for decimal numbers, ensuring accurate calculations for financial and scientific applications. By using the numeric data type correctly, you can avoid rounding errors and maintain precision throughout your calculations.

In conclusion, the numeric data type in PostgreSQL is a powerful tool for storing and manipulating decimal numbers with high precision. It allows you to perform accurate calculations without worrying about rounding errors or loss of precision. Use the numeric data type when working with financial or scientific data that requires exact results.