TRIM_SCALE

Remove trailing zeros from a numeric value to reduce scale to the minimum needed.

Category: numericReturns: NUMERICDialect: PostgreSql

Syntax

TRIM_SCALE(n)

Description

## Overview Returns the given DECIMAL value with trailing zeros in the fractional part removed. The numerical value is unchanged; only the declared scale is reduced to the minimum needed to represent the value exactly. For presentation purposes TRIM_SCALE produces the most compact decimal form without surprising conversions to float. TRIM_SCALE is semantically equivalent to CAST(n AS DECIMAL(<precision>, MIN_SCALE(n))), but does not require computing the target scale manually. ## Behavior - Accepts DECIMAL/NUMERIC input. DOUBLE and FLOAT should be cast first. - Returns a DECIMAL with the same numerical value and with scale equal to MIN_SCALE(n). - Returns NULL if the argument is NULL. - Has no effect on integer-valued inputs. - Preserves sign and magnitude exactly. ## Numeric precision - TRIM_SCALE is exact. No rounding occurs. - The returned type is still DECIMAL, but with a reduced declared scale. - Arithmetic on the result follows normal DECIMAL scale-propagation rules, which may reintroduce trailing zeros. ## Compatibility - Matches the PostgreSQL TRIM_SCALE scalar function. - Complements MIN_SCALE (inspection) and SCALE (declared scale reporting).

Parameters

Name	Type	Description
`n`		Specifies the DECIMAL/NUMERIC value whose trailing fractional zeros are removed. The numerical value does not change; only the declared scale is reduced.

Examples

-- Remove trailing zeros
SELECT TRIM_SCALE(1.50000);  -- 1.5

-- No trailing zeros to remove
SELECT TRIM_SCALE(3.14);  -- 3.14

-- All fractional digits are zeros
SELECT TRIM_SCALE(42.000);  -- 42

-- Already an integer
SELECT TRIM_SCALE(100);  -- 100

-- NULL propagation
SELECT TRIM_SCALE(NULL);  -- NULL

-- Column use: compact representation for display
SELECT order_id, TRIM_SCALE(total_amount) AS display_amount
FROM finance.trades.orders;

Pitfalls

TRIM_SCALE does not change the numerical value. 1.500 and 1.5 compare as equal both before and after the call; only the type representation differs.
Subsequent arithmetic can reintroduce trailing zeros. TRIM_SCALE is most useful at the presentation boundary or just before serializing to a string.
TRIM_SCALE does not convert to an integer type even when all fractional digits are zero. TRIM_SCALE(42.000) is still a DECIMAL with scale 0, not an INTEGER. Cast to INT explicitly if you need an integer type.
For DOUBLE or FLOAT input, cast to DECIMAL first with a deliberate target scale. Casting DOUBLE 0.1 to high-precision DECIMAL can expose many unexpected trailing non-zero digits.
TRIM_SCALE is not a formatting function. For locale-aware or fixed-width presentation, use TO_CHAR or equivalent formatting utilities.