Introduction to PostgreSQL numeric Data Type

The PostgreSQL numeric data type is used to store numbers with arbitrary precision. The precision and scale of this data type can be adjusted as needed. Since its precision is not limited by hardware, it can store very large or very small values. This data type is useful in scenarios where high precision decimals need to be retained.

Syntax

The syntax for defining the numeric data type is as follows:

NUMERIC(precision, scale)

Where precision represents the total number of digits, and scale represents the number of digits after the decimal point.

For example, the following statement creates a column of numeric data type with a precision of 5 and a scale of 2:

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    amount NUMERIC(5, 2)
);

Use Cases

The PostgreSQL numeric data type is primarily used in scenarios where high precision decimals are required, such as in finance, scientific computing, and other fields. In these fields, complex calculations are often performed, and using the numeric data type can avoid calculation errors due to precision inaccuracies.

Examples

Here are two examples of using the numeric data type:

Example 1

In this example, we create a table called sales with two columns: id and amount. The data type of the amount column is numeric, with a total of 5 digits and 2 digits after the decimal point.

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    amount NUMERIC(5, 2)
);

Then we insert some data:

INSERT INTO sales (amount) VALUES (123.45);
INSERT INTO sales (amount) VALUES (678.90);
INSERT INTO sales (amount) VALUES (999.99);

Now, we can query the sales table and calculate the total sum of all amounts:

SELECT SUM(amount) FROM sales;

The result is:

1797.34

Example 2

In this example, we create a table called circle with a numeric column called radius. The radius column stores the radius of a circle.

CREATE TABLE circle (
    id SERIAL PRIMARY KEY,
    radius NUMERIC
);

Then we insert some data:

INSERT INTO circle (radius) VALUES (3.14);
INSERT INTO circle (radius) VALUES (6.28);

Now, we can query the circle table and calculate the circumference and area of all circles:

SELECT id, 2 * pi() * radius AS circumference, pi() * radius^2 AS area FROM circle;

The result is:

 id | circumference | area
----+---------------+------------
  1 | 19.7392088022 | 31.0062767
  2 | 39.4784176044 | 78.545534
(2 rows)

We can see that the circumference and area calculated using the numeric data type are very accurate and can meet the requirements of most mathematical calculations.

Conclusion

numeric is a very useful data type in PostgreSQL that can store high-precision numeric values and perform accurate mathematical calculations. Due to its high precision, it is commonly used in scenarios that require high-precision calculations, such as storing financial data, scientific computations, and physics computations. However, due to its larger storage space requirements, storage space and computational efficiency need to be considered. In practical applications, it is necessary to weigh factors such as precision, storage space, and computational efficiency to choose the most suitable data type for your business scenario.