SQL DECIMAL

What does the SQL DECIMAL data type do?

DECIMAL stores exact numeric values with user-defined precision and scale.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL DECIMAL: Supported: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite (as NUMERIC affinity), Snowflake, BigQuery, DB2.

SQL DECIMAL Full Explanation

DECIMAL (also written as NUMERIC in many dialects) is an exact numeric data type that keeps all digits of a number without rounding errors. It lets you declare the total number of digits (precision) and how many of those digits appear to the right of the decimal point (scale). Because values are stored as fixed-point numbers, DECIMAL is the preferred choice for financial data, scientific measurements, and any scenario where fractional rounding cannot be tolerated. If scale is omitted, most engines default it to 0. If both precision and scale are omitted, the database falls back to an implementation-specific default (often DECIMAL(10,0)). Values that exceed the declared precision are rejected with an error, and assignments with more fractional digits than the declared scale are rounded or blocked depending on the dialect.

SQL DECIMAL Syntax

DECIMAL[(precision [, scale])]
-- or
NUMERIC[(precision [, scale])]

SQL DECIMAL Parameters

precision (integer) - total number of significant digits allowed (1-38 in most systems)
scale (integer) - number of digits to the right of the decimal point (0-precision)

Example Queries Using SQL DECIMAL

-- 1. Create a table for invoices with precise currency amounts
CREATE TABLE invoices (
  id SERIAL PRIMARY KEY,
  amount DECIMAL(12,2) NOT NULL
);

-- 2. Insert a value that fits precision and scale
INSERT INTO invoices (amount) VALUES (12345.67);

-- 3. Attempt to insert a value that violates precision (fails)
INSERT INTO invoices (amount) VALUES (123456789012.34);

-- 4. Define a column without explicit scale (defaults to 0)
CREATE TABLE parts (
  part_id INT,
  quantity DECIMAL(5)
);

Expected Output Using SQL DECIMAL

invoices table is created with amount stored to two decimal places.
Row is inserted successfully.
Database raises numeric overflow or precision exceeded error.
parts.quantity behaves like an integer with 5-digit capacity.

Use Cases with SQL DECIMAL

Storing currency, interest rates, or tax rates.
Tracking scientific measurements that require exactness.
Representing inventory quantities that include fractions (e.g., weight in kilograms).
Avoiding binary floating-point rounding issues in aggregations.

Common Mistakes with SQL DECIMAL

Forgetting that DECIMAL without scale defaults to an integer.
Declaring too small a precision, causing overflows on inserts.
Assuming DECIMAL is faster than FLOAT; exactness often trades off some performance.
Mixing DECIMAL and FLOAT in calculations, which may coerce to floating-point.

First Introduced In

SQL-92 standard

Frequently Asked Questions

What is the default scale for DECIMAL?

If you omit scale, most databases default it to 0, making DECIMAL behave like an integer with the specified precision.

How is DECIMAL stored internally?

Engines typically store DECIMAL as a scaled integer or packed BCD. Implementation details differ, but all preserve exact digits.

When should I prefer NUMERIC over DECIMAL?

In the SQL standard they are synonyms. Some databases (e.g., PostgreSQL) treat them identically, so choose whichever is conventional in your codebase.

Does DECIMAL support negative numbers?

Yes, DECIMAL columns store signed values by default. Use CHECK constraints if you need only positive values.