SQL NUMERIC

What does the SQL NUMERIC data type do?

NUMERIC defines an exact, fixed-point numeric data type with user-specified 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 NUMERIC: PostgreSQL, MySQL (as DECIMAL/NUMERIC), SQL Server, Oracle, SQLite, Snowflake, Redshift, BigQuery

SQL NUMERIC Full Explanation

NUMERIC (often interchangeable with DECIMAL) stores exact fixed-point numbers by allocating a fixed total number of digits (precision) and a subset of those digits to the right of the decimal point (scale). Because values are stored as integers plus an implicit decimal separator, arithmetic on NUMERIC avoids the rounding errors common in floating-point types such as FLOAT or DOUBLE. NUMERIC is ideal for monetary amounts, inventory counts, and other values that require predictable accuracy. The valid precision range and the internal storage size vary by database, but most systems follow the SQL standard maximum of 38 digits. If a value inserted into a NUMERIC column exceeds the declared precision or scale, the statement errors or, in some dialects, silently rounds, so explicit precision-scale choices are important for data integrity.

SQL NUMERIC Syntax

column_name NUMERIC
column_name NUMERIC(precision)
column_name NUMERIC(precision, scale)

SQL NUMERIC Parameters

precision (INTEGER) - Total number of significant digits (1-38, database dependent)
scale (INTEGER) - Number of digits to the right of the decimal point (0-precision)

Example Queries Using SQL NUMERIC

-- Create a table with various NUMERIC columns
CREATE TABLE invoices (
  id            SERIAL PRIMARY KEY,
  subtotal      NUMERIC(12,2),  -- up to 10 digits left and 2 right
  tax_rate      NUMERIC(5,4),   -- 0.0000 to 9.9999
  total_amount  NUMERIC(14,2)
);

-- Insert precise monetary values
INSERT INTO invoices (subtotal, tax_rate, total_amount)
VALUES (12345.67, 0.0875, 13484.53);

-- Aggregation retains precision
SELECT SUM(total_amount) AS company_revenue
FROM   invoices;

Expected Output Using SQL NUMERIC

The table is created with fixed-precision columns
The INSERT succeeds because each value fits the declared precision and scale
The SELECT returns an exact monetary total with two decimal places

Use Cases with SQL NUMERIC

Store currency values without floating-point rounding errors.
Keep inventory quantities where fractional units are allowed.
Represent scientific measurements requiring exact decimal precision.
Enforce business rules that limit the number of digits for regulatory or accounting compliance.

Common Mistakes with SQL NUMERIC

Omitting scale, causing the database default (often 0) and losing decimals.
Defining precision too small, leading to overflow errors on insert.
Assuming NUMERIC is faster than FLOAT; exact calculations can be slower.
Believing NUMERIC always equals DECIMAL; some dialects treat them differently in edge cases.
Forgetting that trailing zeros may be preserved or trimmed depending on client formatting, not the data type.

First Introduced In

SQL-92 standard

Frequently Asked Questions

What is the difference between NUMERIC and DECIMAL?

Both types are defined as exact fixed-point in the SQL standard. Most databases implement them identically, but check documentation for storage limits.

Do I need to specify both precision and scale?

No. If you omit both, the database uses its default (often unlimited precision). If you supply precision but not scale, scale defaults to 0, giving an integer-like column.

Why use NUMERIC instead of FLOAT?

FLOAT is approximate and can introduce rounding errors. NUMERIC guarantees exact representation within the declared precision, critical for money and counts.

How large can a NUMERIC be?

The SQL standard caps precision at 38 digits, but some databases allow more (e.g., PostgreSQL up to 1000). Always check your system limits.