MIN_SCALE

Return the minimum number of fractional digits required to represent a numeric value exactly.

Category: numericReturns: INTEGERDialect: PostgreSql

Syntax

MIN_SCALE(n)

Description

## Overview Returns the minimum number of fractional digits required to represent the given DECIMAL value without loss of precision. Unlike SCALE, which reports the declared scale of the type (including trailing zeros), MIN_SCALE looks at the actual value and reports the smallest scale at which the value is exact. MIN_SCALE is the inspection primitive for data-quality work: you can detect whether a column declared as DECIMAL(18, 4) actually uses four fractional digits or whether most rows are integer-valued, which in turn informs storage layout decisions and schema refactoring. ## Behavior - Accepts DECIMAL/NUMERIC input. DOUBLE and FLOAT should be cast to DECIMAL first. - Returns an INTEGER representing the minimum scale. - Returns NULL if the argument is NULL. - Trailing zeros are ignored: MIN_SCALE(5.50000) returns 1 (the trailing four zeros are not counted). - Integer-valued inputs return 0. - Sign has no effect: MIN_SCALE(-3.14) equals MIN_SCALE(3.14). ## Numeric precision - MIN_SCALE is exact for DECIMAL input; no floating-point rounding is involved. - For values stored as DOUBLE, first cast to DECIMAL at an appropriate precision. Casting DOUBLE 0.1 to DECIMAL may reveal the full binary representation (for example, 55 fractional digits), which is rarely what you want. ## Compatibility - Matches the PostgreSQL MIN_SCALE scalar function. - Complements SCALE (declared scale) and TRIM_SCALE (value with trailing zeros removed).

Parameters

Name	Type	Description
`n`		Specifies the numeric (DECIMAL) value to inspect. Must be a DECIMAL/NUMERIC type; DOUBLE and FLOAT values should be cast first.

Examples

-- Integer value needs scale 0
SELECT MIN_SCALE(42.00);  -- 0

-- One significant decimal digit
SELECT MIN_SCALE(1.10);  -- 1

-- Two significant decimal digits
SELECT MIN_SCALE(3.14);  -- 2

-- Trailing zeros are ignored
SELECT MIN_SCALE(5.50000);  -- 1

-- NULL propagation
SELECT MIN_SCALE(NULL);  -- NULL

-- Negative value: scale counts the same way
SELECT MIN_SCALE(-123.456);  -- 3

Pitfalls

MIN_SCALE of a DOUBLE cast to DECIMAL can return a surprisingly large number because DOUBLE 0.1 is not exactly 0.1. Cast with a bounded target scale: CAST(x AS DECIMAL(18, 6)).
MIN_SCALE does not count trailing zeros. If you need the declared scale from the type system, use SCALE.
MIN_SCALE does not accept DOUBLE or FLOAT directly. An explicit CAST is required.
For very small DECIMAL values near zero with many leading zeros in the fractional part (0.000001), MIN_SCALE returns the full scale, not the significant-digit count. Use separate logic to count significant digits.
MIN_SCALE(0) is 0 in most engines because 0 needs no fractional digits. Verify behavior against your engine's docs if you depend on a specific convention.