PostgreSQL CEILING and FLOOR Functions

The CEILING() and FLOOR() functions in PostgreSQL round numbers up or down to the nearest integer respectively. CEILING (or CEIL) always rounds up, while FLOOR always rounds down, regardless of the decimal value.

Syntax

-- Round up to nearest integer
CEILING(numeric_value)
CEIL(numeric_value)  -- Alias for CEILING

-- Round down to nearest integer
FLOOR(numeric_value)

Official Documentation

Example usage

-- CEILING: Always round up
SELECT CEILING(3.14) AS ceil_result;
-- Result: 4

SELECT CEILING(3.99) AS ceil_result;
-- Result: 4

SELECT CEILING(-3.14) AS ceil_result;
-- Result: -3 (up from -3.14)

-- FLOOR: Always round down
SELECT FLOOR(3.14) AS floor_result;
-- Result: 3

SELECT FLOOR(3.99) AS floor_result;
-- Result: 3

SELECT FLOOR(-3.14) AS floor_result;
-- Result: -4 (down from -3.14)

-- CEIL alias (same as CEILING)
SELECT CEIL(5.1) AS ceil_result;
-- Result: 6

-- Calculate required pages
SELECT
    total_items,
    items_per_page,
    CEILING(total_items::NUMERIC / items_per_page) AS total_pages
FROM (VALUES (100, 15)) AS t(total_items, items_per_page);
-- Result: 7 pages needed

-- Round up prices (always favor business)
SELECT
    product_name,
    cost_price,
    CEILING(cost_price * 1.3) AS selling_price
FROM products;

-- Calculate storage units needed
SELECT
    data_size_mb,
    CEILING(data_size_mb / 1024.0) AS gb_needed
FROM storage_usage;

-- Round down for discounts (favor customer)
SELECT
    order_total,
    FLOOR(order_total * 0.1) AS discount_amount
FROM orders;

-- Age calculation (round down to complete years)
SELECT
    birth_date,
    FLOOR(EXTRACT(EPOCH FROM AGE(birth_date)) / 31557600) AS age_years
FROM users;

-- Pagination calculations
SELECT
    COUNT(*) AS total_records,
    CEILING(COUNT(*)::NUMERIC / 20) AS total_pages_20_per_page,
    CEILING(COUNT(*)::NUMERIC / 50) AS total_pages_50_per_page
FROM products;

-- Round to specific decimal places (combination approach)
SELECT
    value,
    CEILING(value * 100) / 100 AS round_up_2_decimals,
    FLOOR(value * 100) / 100 AS round_down_2_decimals
FROM measurements;

-- Resource allocation (round up to ensure coverage)
SELECT
    user_count,
    CEILING(user_count / 100.0) AS servers_needed
FROM user_stats;

Common issues

1. Integer division: CEILING(100 / 15) returns 7 because integer division happens first. Use CEILING(100::NUMERIC / 15).
2. Negative numbers: CEILING rounds toward positive infinity, FLOOR toward negative infinity.
3. Return type: Both return same type as input (NUMERIC, DOUBLE PRECISION, etc.).
4. Already integer: Applying to integers returns the same value.

Best practices

1. Cast to NUMERIC or FLOAT before division to avoid integer division: CEILING(a::NUMERIC / b).
2. Use CEILING for "units needed" calculations (pages, packages, storage blocks).
3. Use FLOOR for age calculations and when you want complete units only.
4. For rounding to specific decimal places, multiply-round-divide: CEILING(value * 100) / 100.

Frequently Asked Questions

Q: What's the difference between CEILING, FLOOR, and ROUND?
A: CEILING always rounds up: CEILING(3.1) = 4. FLOOR always rounds down: FLOOR(3.9) = 3. ROUND rounds to nearest: ROUND(3.5) = 4 (may vary with banker's rounding).

Q: How do CEILING and FLOOR handle negative numbers?
A: CEILING rounds toward positive infinity: CEILING(-3.1) = -3. FLOOR rounds toward negative infinity: FLOOR(-3.1) = -4. This is consistent with "up" and "down" on the number line.

Q: Is CEIL the same as CEILING?
A: Yes, CEIL is an alias for CEILING. They're functionally identical. Use whichever you prefer.

Q: How do I round up to 2 decimal places instead of integer?
A: Multiply, apply CEILING, then divide: CEILING(value * 100) / 100. For example, CEILING(3.141 * 100) / 100 = 3.15.

Q: Why does CEILING(100/15) return 7 instead of 7?
A: If both operands are integers, PostgreSQL performs integer division first (100/15 = 6), then CEILING(6) = 6. Cast to numeric: CEILING(100::NUMERIC / 15) = 7.