DECIMAL vs NUMERIC Datatype in PostgreSQL

PostgreSQL is a powerful open-source relational database management system known for its robustness, extensibility, and adherence to SQL standards. When it comes to storing numeric data, PostgreSQL offers two main datatypes: DECIMAL and NUMERIC. While these datatypes are often used interchangeably, understanding their differences can help you make informed decisions when designing your database schema.

Advanced Relational Database: PostgreSQL, often referred to as Postgres, is a highly advanced and feature-rich relational database system. It provides a wide range of features, including support for complex queries, transactions, JSON data types, and user-defined functions, making it a popular choice for both small-scale and large-scale applications.

DECIMAL and NUMERIC Datatypes

Both DECIMAL and NUMERIC datatypes in PostgreSQL are used to store fixed-point numbers. These data types are ideal for applications where precision is crucial, such as storing financial data, where rounding errors can lead to significant discrepancies.

DECIMAL Datatype

The DECIMAL datatype in PostgreSQL is used to store numbers with a fixed precision and scale. The syntax for defining a DECIMAL column in a table is as follows:

column_name DECIMAL(precision, scale)

Here, precision represents the total number of digits that can be stored, and scale represents the number of digits that can be stored after the decimal point.

NUMERIC Datatype

The NUMERIC datatype is functionally equivalent to DECIMAL and is used to store fixed-point numbers with a specified precision and scale. The syntax for defining a NUMERIC column is the same as for DECIMAL:

column_name NUMERIC(precision, scale)

Examples

Example 1: Using DECIMAL Datatype

Let’s create a table named employees with a salary column of type DECIMAL to store the salary of employees. We’ll insert a few records to demonstrate its usage:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2)
);

INSERT INTO employees (name, salary) VALUES ('John Doe', 50000.00);
INSERT INTO employees (name, salary) VALUES ('Jane Smith', 75000.50);

Output:

| id |     name      |  salary   |
|----|---------------|-----------|
| 1  |   John Doe    | 50000.00  |
| 2  | Jane Smith    | 75000.50  |

Explanation: In this example, the salary column is defined as DECIMAL(10, 2), which means it can store up to 10 digits in total, with 2 digits after the decimal point.

Example 2: Using NUMERIC Datatype

Similarly, we can create a table named products with a price column of type NUMERIC to store the price of products. We’ll also insert some sample data:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    price NUMERIC(8, 2)
);

INSERT INTO products (name, price) VALUES ('Product A', 49.99);
INSERT INTO products (name, price) VALUES ('Product B', 99.50);

Output:

| id |   name    |  price  |
|----|-----------|---------|
| 1  | Product A |  49.99  |
| 2  | Product B |  99.50  |

Explanation: In this example, the price column is defined as NUMERIC(8, 2), allowing for a total of 8 digits with 2 digits after the decimal point.

Example 3: Querying Data

To query data from the employees table and calculate the total salary, you can use the following SQL query:

SELECT SUM(salary) AS total_salary FROM employees;

Output:

| total_salary |
|--------------|
|   125000.50  |

Explanation: This query calculates the sum of all salaries in the employees table and aliases the result as total_salary.

Comparison and Conclusion

Both DECIMAL and NUMERIC datatypes in PostgreSQL are functionally equivalent, with DECIMAL being an alias for NUMERIC. There is no performance difference between the two, so the choice between them is largely a matter of preference or convention within an organization.

In conclusion, PostgreSQL offers powerful options for storing fixed-point numbers with the DECIMAL and NUMERIC datatypes. Understanding how these datatypes work and when to use them can help you design more efficient and accurate database schemas for your applications.