Skip to main content

DECIMAL Type

The DECIMAL(p, s) data type represents numbers with a maximum precision of p digits and a fixed scale of s digits, allowing for precise representation of fractional or whole numbers. DECIMAL is often used for financial calculations, measurements, or other scenarios requiring high precision and fixed-scale arithmetic to avoid rounding errors.

Syntax

DECIMAL(p, s)

  • p (Precision): Specifies the maximum total number of digits that the number can have, including both sides of the decimal point.

  • s (Scale): Specifies the number of digits to the right of the decimal point (fractional part). s must be less than or equal to p.

Literals

DECIMAL literals are expressed as numeric values with or without fractional parts. Examples of valid literals:

  • 123.45

  • -98765.234

  • 100.54321

Limits

  • The value of p can have a maximum of 38 digits

  • The value of s must be between 0 and 18. If s is 0, the number will be stored as an integer without a fractional part. The range of a DECIMAL(p, s) number is determined by p. For example, with DECIMAL(5, 2), the maximum value is 999.99 and the minimum value is -999.99.

  • A decimal without specified p and s will default to DECIMAL(37,15).

Examples

-- Example 1: Declaring DECIMAL data type in a table
CREATE TABLE products (
    product_id BIGINT,
    price DECIMAL(10, 2)  -- Maximum of 10 digits, with 2 digits for the fractional part
);

-- Example 2: Inserting valid values
INSERT INTO products (product_id, price) VALUES (1, 12345.67);  -- Valid
INSERT INTO products (product_id, price) VALUES (2, 1234567890.12);  -- Valid

-- Example 3: Inserting invalid values
INSERT INTO products (product_id, price) VALUES (3, 12345678901.12);  -- Exceeds precision, results in error

-- Example 4: Using DECIMAL in a SELECT query
> SELECT CAST(123.456 AS DECIMAL(5, 2)) AS rounded_value;
123.46  -- Rounded to two decimal places

-- Example 5: Using DECIMAL without specified precision and scale.
> SELECT CAST(1.25 AS DECIMAL)
1.250000000000000 -- No decimal places are retained because DECIMAL will default to DECIMAL(37,15).