💰Decimal

NEW

Also known as: Fixed-Point Decimal

A base-10 numeric type that stores exact values with defined precision and scale, suitable for financial calculations.

DESCRIPTION

Decimal types represent exact numeric values, typically with fixed precision and scale, preventing rounding errors common in floating-point arithmetic. Databases implement decimal as numeric/decimal with user-defined precision and scale. Programming languages provide decimal via libraries like Java BigDecimal or .NET decimal. Decimal is preferred for currency, accounting, and compliance reporting. It trades performance and storage for accuracy and reproducibility. Proper handling includes rounding modes, scale alignment, and overflow checking. Schema design should select precision to fit domain constraints and ensure consistent calculations across systems.

ALTERNATIVE DEFINITIONS

PostgreSQL Documentation

The types numeric and decimal are equivalent. Both are arbitrary precision numbers.

Highlights equivalence and arbitrary precision semantics.

Source

MySQL Reference Manual

The DECIMAL data type stores exact numeric data values.

Emphasizes exactness of fixed-point DECIMAL.

Source

Microsoft Learn

Fixed precision and scale numeric data type. decimal and numeric are synonyms.

Explicitly defines fixed precision/scale and synonymy in SQL Server.

Source

Oracle Java SE API

Immutable, arbitrary-precision signed decimal numbers.

Describes BigDecimal semantics for exact decimal arithmetic.

Source

RELATED TERMS

Numeric Precision Volume Money Data Type BigDecimal Rounding IEEE 754 Fixed-Point Arithmetic Numeric(precision, scale)

COMPETING TERMS

Float

KNOWLEDGE AREAS

Programming Languages Data Modeling Data Storage & Operations Software Engineering Data Engineering

TARGET AUDIENCE

Software Developer Data Engineer Data Modeler Data Architect BI Specialist Analytics Engineer

EXTERNAL SOURCES

• Wikipedia - Fixed-point arithmetic
• PostgreSQL - Numeric/decimal type specification
• MySQL - DECIMAL type details
• Microsoft - SQL Server decimal/numeric docs
• Oracle - BigDecimal API for exact decimal

TOP QUESTIONS

Why use Decimal instead of Floating Point?

Decimal avoids binary rounding errors, ensuring exact values for currency and accounting. It provides deterministic results across platforms.

Category: Relevancy

What do precision and scale mean for Decimal?

Precision is total digits; scale is digits to the right of the decimal point. For money, choose scale to match currency minor units.

Category: Requirements

What are common mistakes with Decimal?

Mixing Decimal with float in computations, failing to set rounding modes, and using insufficient precision can cause inaccuracies.

Category: Mistakes

How is Decimal represented in SQL?

SQL uses DECIMAL(p, s) or NUMERIC(p, s), where p is precision and s is scale. Some engines treat them as synonyms.

Category: Overview

Is Decimal slower than Floating Point?

Yes, Decimal arithmetic can be slower and use more storage, but it provides exactness crucial for financial applications.

Category: Risks

How should Decimal rounding be handled?

Use explicit rounding modes (e.g., half-up, bankers rounding) consistent with regulations and domain rules.

Category: Best practices

What languages support Decimal natively?

Many databases support DECIMAL; languages provide libraries like Java BigDecimal, .NET decimal, and Python’s decimal module.

Category: Overview

How to migrate floats to Decimal safely?

Recompute critical values from original sources; avoid direct cast of floats, validate with reconciliation tests and tolerances.

Category: Management

What is the biggest misunderstanding about Decimal?

That Decimal is always better; for scientific calculations, floating point with error analysis may be more appropriate.

Category: Misunderstanding