SQL DEC

What is the SQL DEC data type?

DEC defines an exact numeric column 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 DEC: PostgreSQL, MySQL, SQL Server, Oracle, SQLite (as alias), Snowflake, MariaDB, BigQuery (INTEGER/NUMERIC)

SQL DEC Full Explanation

DEC is a standards-compliant synonym for DECIMAL that stores exact (not floating-point) numbers. You supply a precision, which is the total number of significant digits, and an optional scale, which is how many of those digits appear to the right of the decimal point. If scale is omitted, it defaults to 0. Internally, databases use fixed-length or variable-length packed representations to guarantee exactness, making DEC ideal for currency, inventory counts, and other calculations that cannot tolerate rounding error. Precision limits vary by vendor (e.g., up to 38 in PostgreSQL and Snowflake, 65 in MySQL, 38 in SQL Server). Values that exceed the defined precision raise an error. When scale is greater than precision, most systems throw a syntax error. DEC columns participate fully in indexes, constraints, and arithmetic expressions without implicit conversion to floating types.

SQL DEC Syntax

DEC[(precision[, scale])]

SQL DEC Parameters

precision (integer) - Total number of digits (required)
scale (integer) - Digits to the right of the decimal point (optional, default 0)

Example Queries Using SQL DEC

-- Define a money column with two decimal places
CREATE TABLE invoices (
  id          SERIAL PRIMARY KEY,
  total_due   DEC(10,2)
);

-- Insert exact numeric values
INSERT INTO invoices (total_due) VALUES (12345.67), (89.01);

-- Arithmetic preserves exactness
SELECT id, total_due * 1.05 AS total_with_tax
FROM invoices;

Expected Output Using SQL DEC

The table is created with an exact numeric column that stores values up to 10 digits, 2 of which are after the decimal point
Insert succeeds, and the SELECT returns values multiplied exactly by 1
05 without binary rounding error

Use Cases with SQL DEC

Storing monetary amounts where rounding cannot occur
Quantities, stock levels, or part counts that must be exact
Ratios or percentages that need fixed precision
Regulatory or audit-compliant data that prohibits floating-point error

Common Mistakes with SQL DEC

Confusing precision with scale (DEC(5,2) allows 999.99, not 99999)
Omitting parentheses when precision is required by the dialect
Expecting DEC to handle very large numbers beyond vendor limits
Using DEC for scientific data where floating point is more appropriate

First Introduced In

SQL-92

Frequently Asked Questions

What is the difference between DEC and DECIMAL?

DEC is merely a shorthand alias for DECIMAL. Both keywords behave identically in every major SQL database.

How are precision and scale interpreted?

Precision is the total number of digits. Scale is the subset of those digits that appear after the decimal point. For example, DEC(8,3) can store 9999.999.

Can I omit precision and scale entirely?

Some databases allow bare DEC, defaulting precision to vendor-specific maximums and scale to 0. Explicit precision is recommended for portability.

Does DEC prevent rounding errors?

Yes. DEC uses a fixed-point representation, so calculations maintain exact values within the declared precision, avoiding binary rounding drift seen with FLOAT.