In PostgreSQL, the data type for decimal values is NUMERIC. The NUMERIC data type allows you to store numbers with a fixed number of digits before and after the decimal point. It is useful when you need to maintain precision and accuracy in your calculations or when dealing with financial data.
Creating a NUMERIC Column
To create a column with the NUMERIC data type, you can use the following syntax:
CREATE TABLE table_name ( column_name NUMERIC(precision, scale) );
The precision parameter specifies the total number of digits that can be stored in the column, while the scale parameter represents the number of digits to the right of the decimal point.
CREATE TABLE employees ( id INT, salary NUMERIC(10, 2) );
In this example, we have created a table called “employees” with two columns: “id” and “salary”. The “salary” column has a precision of 10 and a scale of 2, meaning it can store up to 10 digits with 2 digits after the decimal point.
Performing Arithmetic Operations on NUMERIC Values
The NUMERIC data type allows you to perform various arithmetic operations such as addition, subtraction, multiplication, and division. When performing calculations involving NUMERIC values, PostgreSQL ensures that the precision and scale are maintained.
SELECT salary * 1.1 AS new_salary FROM employees WHERE id = 1;
In this example, we are multiplying the “salary” value by 1.1 for the employee with an “id” of 1. The result is returned as “new_salary”.
Using NUMERIC in Aggregate Functions
The NUMERIC data type can also be used in aggregate functions such as SUM, AVG, MIN, and MAX. These functions allow you to perform calculations on a set of NUMERIC values.
SELECT AVG(salary) AS average_salary FROM employees;
In this example, we are calculating the average salary of all employees. The result is returned as “average_salary”.
The NUMERIC data type in PostgreSQL is used to store decimal values with precision and scale. It allows you to perform arithmetic operations while maintaining accuracy. You can create columns with the NUMERIC data type using the CREATE TABLE statement, and utilize aggregate functions for calculations involving NUMERIC values.
By understanding the NUMERIC data type in PostgreSQL, you can effectively handle and manipulate decimal values in your database.