PostgreSQL – NUMERIC Data Type
The numeric data type, also known as the decimal data type, is used to store arbitrary precision decimal numbers. This data type is particularly useful when you need to store numeric values with a high degree of precision, such as financial calculations or scientific computations. Here are some key points about the numeric data type:
- Precision and Scale: The
numericdata type allows you to specify both the precision (total number of digits) and the scale (number of digits to the right of the decimal point) for the value being stored. - Size: The storage size for the
numericdata type varies based on the precision and scale you specify. It’s important to note thatnumericvalues require more storage space compared to the fixed-sizeintegerorbiginttypes. - Arithmetic Operations: You can perform arithmetic operations (addition, subtraction, multiplication, division, etc.) on
numericvalues just like any other numeric type. - Numeric Functions: You can use various mathematical functions and operators with
numericvalues. - Precision Loss: Unlike floating-point types (such as
float),numericdoes not suffer from rounding errors, as it stores numbers in a way that maintains their exact decimal representation.
Here’s an example of using the numeric data type:
CREATE TABLE product_prices ( product_id serial PRIMARY KEY, product_name varchar(100), price numeric(10, 2) -- A numeric value with 10 total digits and 2 decimal places ); INSERT INTO product_prices (product_name, price) VALUES ('Widget A', 19.99), ('Widget B', 34.50); SELECT * FROM product_prices WHERE price > 30.00;In this example, a price column of type numeric is used to store product prices. The numeric data type is suitable for situations where precision is critical, such as financial calculations where rounding errors can have a significant impact.