PostgreSQL: Format Number

If you’ve ever had to display financial data to customers or executives, you know that raw numbers like “1234.567” don’t look professional. They need commas: “1,234.57”. If it’s currency, you need a symbol. If it’s a code, you might need leading zeros. PostgreSQL’s TO_CHAR() function is your toolkit for turning raw numbers into display-ready formatted strings.

This isn’t just cosmetic—formatting numbers correctly is critical for business reports, invoices, dashboards, and anywhere users see financial data. Getting it right builds trust; getting it wrong makes your system look unprofessional or worse, can cause confusion about actual values.

Quick Answer: Use TO_CHAR() Function

To format numbers:

SELECT
    amount,
    TO_CHAR(amount, '999,999.00') as with_commas,
    TO_CHAR(amount, '$999,999.99') as currency,
    TO_CHAR(amount, '0.00') as two_decimals
FROM sales
WHERE amount IS NOT NULL
LIMIT 5;

Returns:

amount  | with_commas | currency  | two_decimals
--------|-------------|-----------|----------
1234.5  | 1,234.50    | $1,234.50 | 1234.50
56789   | 56,789.00   | $56,789.00| 56789.00

TO_CHAR(number, 'format') takes your raw number and applies a format pattern to create a readable string.

How It Works

The TO_CHAR() function uses format patterns to control how numbers display. Think of the pattern as a template: you specify how many digits, where commas go, where decimals go, and what symbols appear. PostgreSQL fills in the number according to your template.

The key characters in format patterns are:

  • 9 = optional digit (spaces if not needed)
  • 0 = mandatory digit (filled with zero if not present)
  • , = comma separator
  • . = decimal separator
  • $, €, ₹ = any currency symbol

When you write '999,999.99', you’re saying: “I want up to 6 digits with commas every 3 digits, plus 2 decimal places.” PostgreSQL right-aligns the number into this template and returns the result as text.

Different Approaches & When to Use Them

Approach 1: Commas and Decimals (Standard Business Format)

The most common case: displaying currency or large numbers with thousand separators:

SELECT
    product_id,
    price,
    TO_CHAR(price, '999,999.00') as formatted_price,
    TO_CHAR(cost, '999,999.00') as formatted_cost
FROM products
WHERE price > 1000
ORDER BY formatted_price DESC;

Use this format for any financial data—prices, amounts, balances. The '999,999.00' pattern gives you commas and always 2 decimal places, which is standard for currency.

Approach 2: Currency Symbol

When you’re displaying prices to customers or in invoices:

SELECT
    order_id,
    subtotal,
    TO_CHAR(subtotal, '$999,999.99') as subtotal_display,
    TO_CHAR(tax, '$999,999.99') as tax_display,
    TO_CHAR(total, '$999,999.99') as total_display
FROM orders
WHERE created_at > CURRENT_DATE - INTERVAL '30 days';

Adding a currency symbol makes the data context immediately clear. Users see “$1,234.99” and instantly understand it’s a dollar amount, not a quantity or percentage.

Approach 3: Percentages

For displaying percentages or ratios:

SELECT
    quarter,
    TO_CHAR(growth_rate * 100, '0.00%') as growth_percentage,
    TO_CHAR(margin_ratio * 100, '0.00%') as margin_percentage
FROM quarterly_metrics;

Note: the pattern includes the % symbol and you multiply the decimal by 100. TO_CHAR(0.75 * 100, '0.00%') displays as “75.00%”.

Approach 4: Leading Zeros (Codes and IDs)

When you need fixed-width codes or IDs with leading zeros:

SELECT
    customer_id,
    TO_CHAR(customer_id, '000000') as formatted_id,
    account_number,
    TO_CHAR(account_number, '000000000000') as formatted_account
FROM customers;

This is crucial for invoice numbers, customer IDs, and account numbers. '000000' means “always 6 digits, pad with zeros on the left.” So customer_id 123 becomes '000123'.

Approach 5: Scientific Notation (Large Numbers)

For very large numbers in scientific contexts:

SELECT
    measurement,
    TO_CHAR(measurement, '9.99EEEE') as scientific_notation
FROM sensor_data
WHERE measurement > 1000000;

Use this rarely—mostly for scientific or engineering applications where exponential notation makes sense.

Real-World Example: Complete Invoice Line Items

Let me walk you through a realistic scenario. You’re generating an invoice and need to format all monetary values consistently:

SELECT
    line_item_id,
    product_name,
    quantity,
    unit_price,
    TO_CHAR(unit_price, '$999,999.99') as unit_price_formatted,
    (quantity * unit_price) as line_subtotal,
    TO_CHAR(quantity * unit_price, '$999,999.99') as line_subtotal_formatted,
    discount_percent,
    TO_CHAR(discount_percent * 100, '0.00%') as discount_display,
    (quantity * unit_price * (1 - discount_percent)) as final_amount,
    TO_CHAR(quantity * unit_price * (1 - discount_percent), '$999,999.99') as final_amount_formatted
FROM order_line_items
WHERE order_id = 12345
ORDER BY line_item_id;

Here you’re taking raw numbers and formatting them for display. The unit price, line subtotal, and final amount all get currency formatting. The discount shows as a percentage. This is exactly what you’d do before sending data to a report generator or API that displays to customers.

FAQ

Q: What’s the difference between the pattern characters 9 and 0? A: 9 is an optional digit—if there’s no digit to display, a space appears. 0 is a mandatory digit—it fills with zero if needed. For padding invoice numbers, use 0. For amounts, use 9 so you don’t get " 1,234.99".

Q: Can I use any currency symbol, not just $? A: Yes, absolutely. Use any symbol: '€999,999.99' for euros, '₹999,999.99' for Indian rupees, '£999,999.99' for pounds. PostgreSQL doesn’t validate—any character is literal.

Q: What’s the performance impact of number formatting? A: Very fast. TO_CHAR() is a native, optimized function. Formatting millions of rows has negligible overhead.

Q: How do I handle negative numbers? A: PostgreSQL displays them with a minus sign by default: -$1,234.99. If you want different styling (like parentheses for accounting), use PR in the pattern: '$999,999.99PR' displays -1234.99 as ($1,234.99).

Q: Can I use different decimal separators? A: By default PostgreSQL uses . for decimals and , for thousands, which is US standard. Other locales might use , for decimals. Check your database locale settings with SHOW LC_NUMERIC.

Q: What if the number is larger than my pattern? A: PostgreSQL displays all digits—the pattern is a minimum format, not a maximum. If you have '99.99' but a number is 12345.67, it displays as 12345.67, not truncated.

Q: Can I format in the application instead of the database? A: Absolutely. Many developers format numbers in their application code (Python, Node.js, etc.). Database formatting is useful for reports and data exports where you need the formatted value stored or displayed directly from SQL.

Q: How do I format very large numbers like millions? A: Use enough 9s: '999,999,999.99' or '999,999,999,999.99' for billions. Or divide and format: TO_CHAR(amount / 1000000, '0.00') || 'M' to display in millions.

Q: Can I apply different formatting conditionally with CASE? A: Yes. CASE WHEN amount > 0 THEN TO_CHAR(...) ELSE ... lets you format positive and negative numbers differently.

Q: What about rounding—does TO_CHAR round or truncate? A: It rounds to the specified decimal places. TO_CHAR(1234.567, '999.99') returns '1234.57' (rounded up).

Common Errors & Solutions

Error: “TO_CHAR result has too many/too few digits” The pattern specifies minimum format, not maximum. '99.99' means “at least 2 digits before decimal, 2 after,” but if you have larger numbers, they’ll display completely. Expand your pattern: '999,999.99' for 6 digits before decimal.

Error: “Negative numbers show with minus sign, I want accounting style” Use the PR modifier: '$999,999.99PR' wraps negatives in parentheses instead of a minus sign: ($1,234.99) instead of -$1,234.99.

Error: “Formatting returns wrong locale separators” Check your database locale with SHOW LC_NUMERIC. If it’s configured for European locale, commas and periods might be swapped. You can override with SET LC_NUMERIC or handle in application code.

Performance Tips

  • Very fast operation: TO_CHAR() is native and optimized. Use it freely on large result sets.
  • In SELECT vs. application: Formatting in the database is convenient for reports and exports. For APIs returning JSON, consider formatting in application code.
  • Combine with CAST if needed: Sometimes you need TO_CHAR(amount::NUMERIC, '999,999.99') if the column is text or needs type conversion first.
  • Pre-format for consistency: If you format the same way repeatedly, create a SQL function: CREATE FUNCTION format_currency(amount NUMERIC) AS ...

Wrapping Up

Number formatting is one of those operations that seems simple but makes a huge difference in how professional your output looks. The TO_CHAR() function is powerful and flexible—use '999,999.99' for currency, '0.00%' for percentages, '000000' for padded codes, and adjust the patterns based on your needs. The key is understanding that 9 and 0 behave differently and picking the right pattern for your use case. Format consistently, and your reports and interfaces will look polished and professional.