SCALE

Return the declared scale (number of fractional digits) of a numeric value.

Category: numericReturns: INTEGERDialect: PostgreSql

Syntax

SCALE(n)

Description

## Overview Returns the declared scale (number of fractional digits) of the given DECIMAL value. Unlike MIN_SCALE, which inspects the value to compute the minimum scale needed, SCALE reports the scale according to the type declaration. A value stored as DECIMAL(10, 2) always reports scale 2, even when the value is 0. SCALE is the right primitive for schema-introspection work: you can verify that values conform to a type contract without trimming trailing zeros or inspecting the arithmetic value. ## Behavior - Accepts DECIMAL/NUMERIC input. DOUBLE and FLOAT should be cast first. - Returns an INTEGER representing the declared scale. - Returns NULL if the argument is NULL. - Includes trailing zeros: SCALE(CAST(1.50 AS DECIMAL(10, 2))) is 2. - For an integer literal, SCALE returns 0. ## Numeric precision - SCALE is a type-system inspection and does not perform any arithmetic. - For DOUBLE inputs, the implicit cast to DECIMAL must choose a scale; SCALE then reports that chosen scale, not the value's intrinsic precision. ## Compatibility - Matches the PostgreSQL SCALE scalar function. - Complements MIN_SCALE (value-dependent minimum) and TRIM_SCALE (value with trailing zeros removed).

Parameters

Name	Type	Description
`n`		Specifies the DECIMAL/NUMERIC value whose declared scale is returned. DOUBLE and FLOAT should be cast to DECIMAL first.

Examples

-- Integer has scale 0
SELECT SCALE(42);  -- 0

-- Scale as declared in the type
SELECT SCALE(CAST(3.14 AS DECIMAL(10,2)));  -- 2

-- Trailing zeros are included in the declared scale
SELECT SCALE(CAST(1.50 AS DECIMAL(10,2)));  -- 2

-- High-precision value
SELECT SCALE(CAST(1.123456 AS DECIMAL(20,6)));  -- 6

-- NULL propagation
SELECT SCALE(NULL);  -- NULL

-- Column use: enforce that prices are stored at no more than 4 fractional digits
SELECT order_id
FROM finance.trades.orders
WHERE SCALE(total_amount) > 4;

Pitfalls

SCALE reflects the declared type, not the value. SCALE(1.50) and SCALE(1.5) can differ if the two have different declared scales even though the arithmetic value is identical.
For DOUBLE or FLOAT inputs, the scale depends entirely on the cast target. SCALE(CAST(0.1 AS DECIMAL(38, 18))) returns 18, not some intrinsic property of 0.1.
SCALE does not count total digits (precision); it counts only fractional digits. Use ABS, LOG10, or direct string inspection to get a total digit count.
If a value is produced by arithmetic that widens the scale (for example, two DECIMAL(10, 2) columns multiplied produce DECIMAL(21, 4)), SCALE reports the wider intermediate scale, not the original column scale. Cast explicitly to preserve intent.
SCALE is often confused with precision. Scale is the number of digits after the decimal point; precision is the total number of significant digits. Do not mix them up.