PostgreSQL - NUMERIC Data Type

In PostgreSQL, the NUMERIC data type is used to store numbers with a lot of digits, both before and after the decimal point. It's particularly useful for exact calculations where precision matters, such as financial data. The NUMERIC type can store numbers with a lot of digits. It's functionally similar to the DECIMAL type.

Characteristics:

Precision and Scale: The NUMERIC type can optionally take two parameters: precision and scale.
- Precision: Total number of digits in the number (both to the left and the right of the decimal point).
- Scale: Number of digits to the right of the decimal point. The scale must always be less than or equal to the precision.
For example, the number 1234.56 has a precision of 6 and a scale of 2.
Exact Numeric Value: Unlike floating-point numbers (FLOAT, REAL), which can introduce rounding errors, the NUMERIC type provides exact numeric values. This makes it suitable for financial and monetary calculations.
Storage Size: The storage size for a NUMERIC value is dependent on the precision of the value, but its range is considerably vast, making it apt for very large or precise numbers.

Syntax:

When defining a table:

CREATE TABLE table_name (
    column_name NUMERIC(precision, scale)
);

Examples:

Creating a Table with a NUMERIC Column:
```
CREATE TABLE financial_data (
    id SERIAL PRIMARY KEY,
    amount NUMERIC(20,2)
);
```
In the above example, the amount column can store numbers with up to 20 digits, 2 of which can be after the decimal point.
Inserting Data:
```
INSERT INTO financial_data (amount) VALUES (1234567890123456.78);
```
This inserts a large number with two decimal places into the amount column.

Notes:

Performance: Due to the exact nature of the NUMERIC type, operations on NUMERIC values might be slower than those on approximate numeric values like FLOAT or REAL. Thus, it's essential to choose the appropriate type based on the application's requirements.
Choosing Between NUMERIC and DECIMAL: In PostgreSQL, there's no difference between NUMERIC and DECIMAL in terms of functionality. They can be used interchangeably. The distinction exists because of the SQL standard.

In summary, the NUMERIC data type in PostgreSQL is designed for storing high-precision numbers and is especially useful in contexts like financial and monetary calculations where precision is paramount.

How to define NUMERIC column in PostgreSQL:
- Use the NUMERIC data type to define a column with fixed-point or floating-point precision.
```
CREATE TABLE your_table (
   numeric_column NUMERIC
);
```
Range of values for NUMERIC in PostgreSQL:
- The NUMERIC data type in PostgreSQL supports a wide range of values, depending on the specified precision and scale.
```
CREATE TABLE your_table (
   numeric_column NUMERIC(10, 2)
);
```
Using NUMERIC for precision and scale in PostgreSQL:
- Specify precision and scale to control the number of digits in the integer and fractional parts.
```
CREATE TABLE your_table (
   numeric_column NUMERIC(8, 4)
);
```
NUMERIC vs. DECIMAL in PostgreSQL:
- In PostgreSQL, NUMERIC and DECIMAL are interchangeable.
```
CREATE TABLE your_table (
   numeric_column NUMERIC,
   decimal_column DECIMAL
);
```
Arithmetic operations with NUMERIC in PostgreSQL:
- Perform arithmetic operations with NUMERIC data type.
```
SELECT numeric_column + 10, numeric_column * 2, numeric_column / 3
FROM your_table;
```
Casting and converting data to NUMERIC in PostgreSQL:
- Cast or convert other data types to NUMERIC.
```
SELECT CAST(text_column AS NUMERIC), CONVERT(INT, numeric_column)
FROM your_table;
```
INDEX on NUMERIC columns in PostgreSQL:
- Create indexes on NUMERIC columns to optimize query performance.
```
CREATE INDEX numeric_index ON your_table(numeric_column);
```
Using NUMERIC with CHECK constraints in PostgreSQL:
- Apply CHECK constraints to enforce specific conditions on NUMERIC values.
```
CREATE TABLE your_table (
   numeric_column NUMERIC CHECK (numeric_column >= 0)
);
```
Handling NULL values with NUMERIC in PostgreSQL:
- NUMERIC columns can store NULL values.
```
INSERT INTO your_table (numeric_column) VALUES (NULL);
```
Storing and retrieving NUMERIC values in PostgreSQL:
- Store and retrieve NUMERIC values using INSERT and SELECT statements.
```
INSERT INTO your_table (numeric_column) VALUES (123.45);
SELECT numeric_column FROM your_table;
```

NUMERIC and foreign key relationships in PostgreSQL:

Use NUMERIC in foreign key relationships.

CREATE TABLE parent_table (
   parent_id SERIAL PRIMARY KEY
);

CREATE TABLE child_table (
   child_id SERIAL PRIMARY KEY,
   numeric_column NUMERIC,
   FOREIGN KEY (numeric_column) REFERENCES parent_table(parent_id)
);

Using NUMERIC with aggregate functions in PostgreSQL:
- Utilize NUMERIC in aggregate functions like AVG, SUM, etc.
```
SELECT AVG(numeric_column), SUM(numeric_column)
FROM your_table;
```

NUMERIC and partitioning in PostgreSQL:

Leverage NUMERIC for partitioning large tables.

CREATE TABLE partitioned_table (
   numeric_column NUMERIC
) PARTITION BY RANGE (numeric_column);

Comparison of NUMERIC with other numeric data types in PostgreSQL:

Compare NUMERIC with other numeric data types like INTEGER, BIGINT, etc.

SELECT numeric_column, integer_column, bigint_column
FROM your_table
WHERE numeric_column = integer_column AND numeric_column = bigint_column;

Using NUMERIC with mathematical functions in PostgreSQL:
- Perform mathematical operations using NUMERIC in conjunction with functions.
```
SELECT SQRT(numeric_column), POWER(numeric_column, 2)
FROM your_table;
```
Formatting NUMERIC values in PostgreSQL:
- Format NUMERIC values using the TO_CHAR function.
```
SELECT TO_CHAR(numeric_column, '999,999.99')
FROM your_table;
```