Skip to main content
Documents
Basic SyntaxMath FunctionsDate FunctionsJSON FunctionsDatabasesTables & Schema ManagementString FunctionsTriggersIndexes

PostgreSQL CEIL

The PostgreSQL `CEIL` function is a mathematical function that returns the smallest integer greater than or equal to a specified numeric value. It is used when rounding up numbers to the nearest integer is desired.

Usage

The `CEIL` function is employed when you need to round up a decimal number to the nearest whole number. It is particularly useful in financial calculations, where rounding up is often required. In PostgreSQL, `CEIL` is an alias for the `CEILING` function, which may be familiar to users of other SQL dialects.

sql
CEIL(numeric_expression)

In this syntax, `numeric_expression` is the number you wish to round up to the nearest integer. Compatible data types include `INTEGER`, `REAL`, `DOUBLE PRECISION`, and `NUMERIC`.

Examples

1. Basic Usage

sql
SELECT CEIL(4.2);

This example returns `5`, as `4.2` is rounded up to the next integer.

2. Rounding Negative Numbers

sql
SELECT CEIL(-3.7);

Here, the result is `-3`, since `-3.7` rounds up to the smallest integer that is greater than or equal to the number, which is closer to zero for negative numbers.

3. Using CEIL with Column Data

sql
SELECT product_id, CEIL(price) AS rounded_price
FROM products;

In this query, the `CEIL` function is applied to the `price` column of the `products` table to round up each product price to the nearest whole number, and the result is aliased as `rounded_price`.

4. CEIL with Different Data Types

sql
SELECT CEIL(123.456::NUMERIC(6, 3));

This returns `124`, demonstrating `CEIL` applied to a `NUMERIC` type with scale and precision.

5. CEIL on Whole Numbers

sql
SELECT CEIL(10);

This returns `10`, illustrating that applying `CEIL` to a whole number results in the number itself.

Tips and Best Practices

  • Combine with other functions. Use `CEIL` in conjunction with other mathematical or aggregate functions for complex calculations.
  • Understand rounding behavior. Keep in mind that `CEIL` always rounds towards the next highest integer, even for negative values.
  • Performance consideration. Apply `CEIL` to columns selectively to avoid unnecessary computations, especially in large datasets. Consider using indices or pre-computation techniques to optimize performance in large query operations.
  • Use in financial calculations. Ideal for scenarios where rounding up is a requirement, such as calculating financial ceilings or limits.