NUMERIC(precision, scale), real, DECIMAL(precision, scale), and double precision:
As you can see, the NUMERIC and DECIMAL data types are very similar. The only difference is that NUMERIC is an alias for DECIMAL. This means that you can use either data type to store fixed-point decimal numbers.
The precision and scale parameters specify the number of digits that can be stored before and after the decimal point, respectively. For example, NUMERIC(10,2) can store numbers such as 123.45, 100.00, or -56.78.
The storage size of the NUMERIC and DECIMAL data types is variable. This means that t
he amount of storage space that is used depends on the precision and scale of the data type. For example, NUMERIC(10,2) uses less storage space than NUMERIC(20,2).
The range of the NUMERIC and DECIMAL data types is also variable. This means that the range of values that can be stored depends on the precision of the data type. For example, NUMERIC(10) can store values such as 12345, 10000, or -5678.
The real data type is used to store floating-point decimal numbers. Floating-point numbers are represented by a significand and an exponent. The significand is a mantissa that stores the digits of the number, and the exponent is an integer that indicates the scale of the number. Floating-point numbers are less precise than fixed-point numbers, but they can store a wider range of values.
The double precision data type is used to store double-precision floating-point decimal numbers. Double-precision floating-point numbers are more precise than real floating-point numbers, but they also use more storage space.
Here is a table that summarizes the usage of the NUMERIC, DECIMAL, real, and double precision data types:
I hope this helps!
Here's an example of creating a table using the NUMERIC, DECIMAL, real, and double precision data types in PostgreSQL:
SQL
CREATE TABLE product_prices (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(50) NOT NULL,
unit_price_numeric NUMERIC(10,2) NOT NULL,
unit_price_decimal DECIMAL(10,2) NOT NULL,
unit_price_real REAL NOT NULL,
unit_price_double_precision DOUBLE PRECISION NOT NULL,
quantity INT NOT NULL,
total_price_numeric NUMERIC(10,2) NOT NULL,
total_price_decimal DECIMAL(10,2) NOT NULL,
total_price_real REAL NOT NULL,
total_price_double_precision DOUBLE PRECISION NOT NULL
);
This table stores information about products, including their names, unit prices, quantities, and total prices. The unit prices are stored using all four data types (NUMERIC, DECIMAL, real, and double precision) for comparison purposes. The total prices are calculated by multiplying the unit prices by the quantities and stored using the corresponding data types.
The product_id column is the primary key, which means that it is unique and not null. The product_name column stores the product's name, the quantity column stores the number of units of the product, and the total_price_* columns store the total price of the product using the corresponding data type.
I hope this helps!
Here is an example of inserting 3 records into the product_prices table:
SQL
INSERT INTO product_prices (product_name, unit_price_numeric,
unit_price_decimal, unit_price_real,
unit_price_double_precision, quantity,
total_price_numeric, total_price_decimal,
total_price_real, total_price_double_precision)
VALUES
('Laptop', 1234.56, 1234.56, 1234.56, 1234.56, 1, 1234.56, 1234.56, 1234.56, 1234.56),
('Smartphone', 567.89, 567.89, 567.89, 567.89, 2, 1135.78, 1135.78, 1135.78, 1135.78),
('Headphones', 123.45, 123.45, 123.45, 123.45, 3, 370.35, 370.35, 370.35, 370.35);
This statement inserts 3 rows into the product_prices table for a laptop,
a smartphone, and a pair of headphones.
The unit prices and total prices are calculated based on the quantity of
each product.
I hope this helps!