Numeric vs Decimal in PostgreSQL: Key Differences and Usage

In PostgreSQL, numeric and decimal are equivalent data types used for exact numeric storage with user-defined precision and scale. Both store numbers precisely without rounding errors, making them ideal for financial calculations.

Quick Comparison

This table summarizes the main points comparing numeric and decimal types in PostgreSQL.

Feature	numeric	decimal
Type Category	Exact numeric	Exact numeric
Precision and Scale	User-defined, unlimited precision	User-defined, unlimited precision
Storage	Variable, depends on precision	Variable, depends on precision
Alias	Primary type	Alias for numeric
Use Case	Financial and precise calculations	Same as numeric
Standard Compliance	SQL standard compliant	SQL standard compliant

Key Differences

In PostgreSQL, numeric and decimal are functionally the same. Both store numbers with exact precision and scale, which means they can represent numbers without rounding errors, unlike floating-point types.

The main difference is historical and semantic: decimal is simply an alias for numeric. This means internally, PostgreSQL treats them identically, and you can use either name in your table definitions without any difference in behavior or storage.

Both types allow you to specify precision (total digits) and scale (digits after the decimal point), or you can omit these to allow any precision. This flexibility makes them perfect for storing monetary values or other exact numeric data where rounding errors are unacceptable.

Code Comparison

Here is how you define and insert values using the numeric type in PostgreSQL:

sql

CREATE TABLE prices_numeric (
  price numeric(10,2)
);

INSERT INTO prices_numeric (price) VALUES (12345.67);

SELECT price FROM prices_numeric;

Output

 price 
--------
 12345.67
(1 row)

Decimal Equivalent

The equivalent table and operations using decimal type look exactly the same:

sql

CREATE TABLE prices_decimal (
  price decimal(10,2)
);

INSERT INTO prices_decimal (price) VALUES (12345.67);

SELECT price FROM prices_decimal;

Output

 price 
--------
 12345.67
(1 row)

When to Use Which

Choose numeric or decimal interchangeably in PostgreSQL since they behave identically. Use them whenever you need exact numeric precision, such as for financial data, currency, or measurements where rounding errors are unacceptable.

There is no performance or storage difference, so pick the name that best fits your team's style or your project's standards.

Key Takeaways

numeric and decimal are identical in PostgreSQL and can be used interchangeably.

Both types store exact numbers with user-defined precision and scale, avoiding rounding errors.

They are ideal for financial and precise numeric data storage.

No performance or storage difference exists between the two types.

Choose based on naming preference or coding standards, not functionality.