## NUMERIC(precision, scale), real, DECIMAL(precision, scale), and double precision:

Data Type

Precision

Scale

Storage Size

Range

NUMERIC(precision, scale)

User-specified

User-specified

Variable

Up to 131,072 digits before the decimal point; up to 16,383 digits after the decimal point

DECIMAL(precision, scale)

User-specified

User-specified

Variable

Up to 131,072 digits before the decimal point; up to 16,383 digits after the decimal point

real

4 bytes

Not applicable

4 bytes

Approximately 6 decimal digits of precision

double precision

8 bytes

Not applicable

8 bytes

Approximately 15 decimal digits of 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:

Data Type

Usage

NUMERIC(precision, scale)

Store fixed-point decimal numbers with a specified precision and scale.

DECIMAL(precision, scale)

Store fixed-point decimal numbers with a specified precision and scale.

NUMERIC(precision)

Store fixed-point decimal numbers with a specified precision and a scale of 0.

DECIMAL(precision)

Store fixed-point decimal numbers with a specified precision and a scale of 0.

NUMERIC

Store fixed-point decimal numbers with a precision of 10 and a scale of 0.

DECIMAL

Store fixed-point decimal numbers with a precision of 10 and a scale of 0.

real

Store floating-point decimal numbers with approximately 6 decimal digits of precision.

double precision

Store double-precision floating-point decimal numbers with approximately 15 decimal digits of precision.

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!