PostgreSQL: Convert Integer to Decimal

You need to convert integer values to decimals for precise calculations, financial data, or reporting. Maybe you’re dividing integers and need exact results instead of truncation.

Quick Answer: Cast Using ::DECIMAL or ::NUMERIC

To convert integer to decimal:

SELECT
    emp_id,
    salary,
    salary::DECIMAL as salary_decimal,
    salary::DECIMAL(10,2) as salary_with_precision,
    (salary / 12.0)::DECIMAL(10,2) as monthly_salary
FROM employees
WHERE salary IS NOT NULL
LIMIT 5;

Returns:

emp_id | salary | salary_decimal | salary_with_precision | monthly_salary
-------+--------|----------------+-----------------------+----------------
     1 | 100000 | 100000         | 100000.00             | 8333.33
     2 | 120000 | 120000         | 120000.00             | 10000.00

Use ::DECIMAL(total_digits, decimal_places) to specify precision.

How It Works

::DECIMAL casts the integer to decimal type. You can specify precision with the format (total_digits, decimal_places). Without specification, it uses maximum precision. The key difference from integer division: decimals preserve fractional parts.

Different Approaches & When to Use Them

Approach 1: Simple Cast (Maximum Precision)

SELECT
    value,
    value::DECIMAL as unlimited_precision
FROM integers;

Best for: When you don’t need to limit precision.

Approach 2: Fixed Precision (Money Format)

SELECT
    salary,
    salary::DECIMAL(10,2) as formatted_salary,
    price::DECIMAL(12,4) as precise_price
FROM products;

(10,2) means 10 total digits, 2 after decimal.

Best for: Financial data, standardized format.

Approach 3: Convert Before Division

SELECT
    total_cost,
    item_count,
    (total_cost::DECIMAL / item_count) as cost_per_item
FROM orders;

Converting before math preserves decimals.

Best for: Calculations requiring precision.

Approach 4: CAST Function (More Explicit)

SELECT
    salary,
    CAST(salary AS DECIMAL(10,2)) as salary_decimal
FROM employees;

Equivalent to :: but more explicit.

Real-World Example: Calculate Precise Commission

SELECT
    emp_id,
    first_name,
    sales_amount,
    (sales_amount * 0.05)::DECIMAL(10,2) as commission_5_percent,
    (sales_amount * 0.075)::DECIMAL(10,2) as commission_7_5_percent,
    (sales_amount * 0.1)::DECIMAL(10,2) as commission_10_percent
FROM employees
WHERE sales_amount IS NOT NULL
ORDER BY sales_amount DESC;

Precise commission calculations matter for payroll.

Update Columns

Convert existing data:

UPDATE products
SET price = price::DECIMAL(10,2)
WHERE price IS NOT NULL;

-- Or create computed column
ALTER TABLE products ADD COLUMN price_decimal DECIMAL(10,2)
GENERATED ALWAYS AS (price::DECIMAL(10,2)) STORED;

Performance Comparison

Method Speed Use Case
Simple cast Very Fast No precision limit
Fixed precision Very Fast Standardized format
CAST function Very Fast Explicit intent

All are equally fast.

Common Patterns

-- Simple conversion
value::DECIMAL

-- Money format (cents)
value::DECIMAL(10,2)

-- Science/precise calculations
value::DECIMAL(20,10)

-- Before division
(numerator::DECIMAL / denominator)

-- With rounding
ROUND(value::DECIMAL(10,2), 2)

-- In WHERE clause
WHERE amount::DECIMAL > 1000.00

FAQ

Q: What’s the difference between DECIMAL and NUMERIC? A: They’re identical in PostgreSQL. Use whichever you prefer.

Q: What does (10,2) mean exactly? A: 10 total digits, 2 after decimal point. So max value is 99999999.99.

Q: What happens if I exceed the precision? A: PostgreSQL throws an error. Use a larger precision or round first.

Q: Should I convert at storage or query time? A: Depends on use. Store as integer if possible (faster), convert at query time.

Q: How do I round after converting? A: Use ROUND(value::DECIMAL(10,2), 2).

Q: Can I convert NULL values? A: NULL::DECIMAL returns NULL. Use COALESCE to provide default.

Wrapping Up

Convert integers to decimals with ::DECIMAL(precision, scale). Specify precision for financial data, use simple cast for unlimited precision. Always convert before division if you need decimal results.

The key: ::DECIMAL preserves fractional values; integer division truncates them.