PostgreSQL: Truncate Decimal

I’ve run into scenarios where I need to be conservative with numbers—always rounding down, never up. Pricing floors, quota calculations, conservative estimates—you can’t risk 19.99 becoming 20 when that affects your bottom line. That’s where TRUNC() comes in. Unlike ROUND(), which follows mathematical rounding rules, TRUNC() just cuts off decimals without any rounding. It’s a hard floor.

The distinction matters. When you round 19.99, you get 20. When you truncate 19.99, you get 19. One decision could cost your company money; the other saves it. Knowing when to use each is essential.

Quick Answer: Use TRUNC() Function

To truncate decimals:

SELECT
    price,
    TRUNC(price) as whole_number,
    TRUNC(price, 2) as two_decimals,
    TRUNC(price, -1) as nearest_ten
FROM products
WHERE price IS NOT NULL
LIMIT 5;

Returns:

price   | whole_number | two_decimals | nearest_ten
--------|--------------|--------------|-------------
19.99   | 19           | 19.99        | 10
123.456 | 123          | 123.45       | 120

TRUNC() cuts off decimals without rounding. Unlike ROUND(), it always removes the excess digits without applying rounding logic.

How It Works

The TRUNC() function removes decimal places by cutting them off cleanly, discarding everything past your specified precision point. There’s no rounding logic—it’s just a hard truncation. If the second parameter is positive, it keeps that many decimal places. If negative, it truncates before the decimal point.

Here’s a concrete example: TRUNC(123.456, 1) returns 123.4 (keeps 1 decimal, discards the rest). TRUNC(123.456, -1) returns 120 (truncates to the nearest 10, removing the ones place). The pattern is: positive number = decimal places, negative number = places before decimal.

The difference from ROUND() is critical: ROUND(19.99, 0) returns 20 (rounded up), but TRUNC(19.99, 0) returns 19 (truncated). For conservative calculations, TRUNC() is your safety net.

Different Approaches & When to Use Them

Approach 1: Truncate to Whole Number (Most Common)

When you just need to discard decimals entirely:

SELECT
    price,
    TRUNC(price) as integer_price,
    quantity,
    TRUNC(price) * quantity as line_total
FROM order_items
WHERE price > 0;

This is the most common use case. You’re eliminating decimals for display or calculation. TRUNC(19.99) gives you 19, not 20.

Approach 2: Keep Specific Decimal Places

When you want to truncate but preserve some decimal precision:

SELECT
    measurement,
    TRUNC(measurement, 2) as two_decimals,
    TRUNC(measurement, 3) as three_decimals,
    TRUNC(measurement, 1) as one_decimal
FROM sensor_data;

This gives you controlled precision without rounding artifacts. TRUNC(123.456, 2) gives you 123.45, not 123.46 (which rounding would produce).

Approach 3: Truncate to Larger Units (Powers of 10)

When dealing with very large numbers that need conservative rounding:

SELECT
    annual_revenue,
    TRUNC(annual_revenue, -3) as nearest_thousand,
    TRUNC(annual_revenue, -6) as nearest_million,
    TRUNC(annual_revenue, -2) as nearest_hundred
FROM companies
WHERE annual_revenue > 1000000;

TRUNC(1,234,567, -3) gives you 1,234,000 (nearest thousand, truncated). This is useful for conservative financial reporting.

Approach 4: Floor Pricing (Business Use Case)

Using truncation for pricing calculations where you always want to round down:

SELECT
    product_id,
    product_name,
    cost,
    TRUNC(cost * 2, 2) as minimum_retail_price,
    ROUND(cost * 2.5, 2) as suggested_price,
    TRUNC(cost * 2, 0) as wholesale_to_retailers
FROM products
WHERE cost > 0;

This is real business logic. Your markup is 2x cost, but you truncate to ensure you never price below a certain level. The suggested price uses normal rounding, but the minimum uses truncation for safety.

Approach 5: Compare TRUNC vs. ROUND Side-by-Side

Understanding the practical difference:

SELECT
    value,
    ROUND(value, 2) as rounded,
    TRUNC(value, 2) as truncated,
    ROUND(value, 2) - TRUNC(value, 2) as difference
FROM (
    SELECT 19.567 as value
    UNION ALL
    SELECT 19.564 as value
    UNION ALL
    SELECT 19.501 as value
) data;

This shows you exactly where ROUND() and TRUNC() diverge and by how much.

Real-World Example: Conservative Commission Calculation

Let me walk you through a realistic scenario. You’re calculating employee commissions and want to be conservative—never inflating commission by rounding up:

SELECT
    emp_id,
    emp_name,
    base_salary,
    sale_amount,
    -- Calculate commission: 2% of sales
    sale_amount * 0.02 as commission_calculated,
    -- Truncate to 2 decimals (never round up)
    TRUNC(sale_amount * 0.02, 2) as commission_truncated,
    -- Compare: rounded version (which might be higher)
    ROUND(sale_amount * 0.02, 2) as commission_rounded,
    -- Show the difference
    ROUND(sale_amount * 0.02, 2) - TRUNC(sale_amount * 0.02, 2) as potential_overpayment,
    -- Final commission: truncated + base percentage bonus
    TRUNC(sale_amount * 0.02, 2) + TRUNC(base_salary * 0.01, 2) as final_commission
FROM employees
WHERE status = 'active'
AND sale_amount > 0
ORDER BY final_commission DESC;

Here you’re using truncation to ensure commissions are conservative. You’re not losing money by rounding employee commissions up. The difference between rounded and truncated could add up to significant amounts across all employees.

FAQ

Q: What’s the practical difference between TRUNC and ROUND? A: TRUNC() cuts off decimals with no rounding logic. ROUND() applies mathematical rounding (5 and above rounds up). TRUNC(19.99, 0) = 19, but ROUND(19.99, 0) = 20.

Q: When should I use TRUNC instead of ROUND? A: Use TRUNC() for conservative calculations (floors, quotas, conservative estimates). Use ROUND() for normal rounding. In financial systems, TRUNC() is often safer because it never inflates numbers unexpectedly.

Q: Does TRUNC work with negative numbers? A: Yes. TRUNC(-19.99, 0) = -19 (truncates toward zero). The behavior is consistent.

Q: Can I use TRUNC on strings or non-numeric types? A: No, TRUNC() requires numeric types. Cast first: TRUNC(amount::NUMERIC, 2) if needed.

Q: What’s the performance impact of TRUNC? A: Negligible. It’s a native, optimized operation. Use freely on millions of rows.

Q: Can I use TRUNC in WHERE clauses? A: Yes. WHERE TRUNC(price, 2) > 10.00 works, but won’t use indexes efficiently. For frequent filtering, consider storing truncated values in a computed column.

Q: Does TRUNC always go down (floor behavior)? A: Yes, it truncates toward zero. TRUNC(19.99) goes to 19, TRUNC(-19.99) goes to -19. It’s always removing the fractional part, never adding.

Q: How is TRUNC different from FLOOR()? A: Similar but distinct. FLOOR() always rounds down (toward negative infinity). TRUNC() truncates toward zero. For positive numbers, they’re identical. For negative numbers, FLOOR(-2.5) = -3, but TRUNC(-2.5) = -2.

Q: Can I combine TRUNC with other functions? A: Yes. ROUND(TRUNC(value, 3), 2) first truncates to 3 decimals, then rounds to 2. Or TRUNC(ABS(value), 2) for absolute value, then truncate.

Q: What happens with very large numbers? A: Works fine. TRUNC(1234567890.123, -6) = 1234000000 (truncates to the nearest million). PostgreSQL handles this accurately.

Common Errors & Solutions

Error: “Unexpected results—got 20 instead of 19” You probably used ROUND() instead of TRUNC(). Double-check: ROUND(19.99, 0) = 20 (wrong), TRUNC(19.99, 0) = 19 (correct).

Error: “TRUNC on string column doesn’t work” The column isn’t numeric. Cast it first: TRUNC(amount::NUMERIC, 2). Check column types with \d table_name in psql.

Error: “Negative numbers are truncating the wrong direction” Remember: TRUNC() truncates toward zero, not toward negative infinity. TRUNC(-19.99, 0) = -19 (correct). If you need floor behavior for negatives, use FLOOR() instead.

Performance Tips

  • Very fast: TRUNC() is native and optimized. Zero performance concerns.
  • In WHERE clauses: While it works, filtering with WHERE TRUNC(column) = value won’t use indexes. Calculate and store truncated values if you filter frequently.
  • For large calculations: Combining TRUNC() with other operations is fine. PostgreSQL optimizes the whole expression.
  • Indexes on computed columns: If you filter frequently by truncated values, create a computed column or index: CREATE INDEX idx_truncated ON table_name (TRUNC(price, 2)).

Wrapping Up

Use TRUNC() when you need conservative calculations—floor values that never round up. For most business logic involving money, TRUNC() is the safer choice. Remember: ROUND() applies mathematical rounding rules, while TRUNC() just cuts off decimals. Choose wisely based on whether you can afford rounding up or if you need a hard floor.