PostgreSQL CEIL() Function

PostgreSQL CEIL() Function

The PostgreSQL CEIL() function is used to return the smallest integer value that is greater than or equal to a given number. This function is essential for rounding up numerical values to the nearest integer.

Syntax

CEIL(number)

The CEIL() function has the following component:

  • number: The number to be rounded up.

Example PostgreSQL CEIL() Queries

Let's look at some examples of PostgreSQL CEIL() function queries:

1. Basic CEIL() Example

SELECT CEIL(4.3) AS rounded_value;

This query returns the smallest integer value greater than or equal to 4.3, which is 5.

2. CEIL() with Column Values

SELECT value, CEIL(value) AS rounded_value
FROM numbers;

This query retrieves the value and its rounded-up value from the numbers table.

3. CEIL() with Negative Values

SELECT value, CEIL(value) AS rounded_value
FROM numbers
WHERE value < 0;

This query retrieves the value and its rounded-up value from the numbers table where the value is negative.

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the CEIL() function to retrieve the rounded-up values.

Step 1: Creating a Table

This step involves creating a new table named numbers to store numerical data.

CREATE TABLE numbers (
    id SERIAL PRIMARY KEY,
    value NUMERIC
);

In this example, we create a table named numbers with columns for id and value.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the numbers table.

INSERT INTO numbers (value)
VALUES (4.3),
       (2.7),
       (-3.8),
       (7.1);

Here, we insert data into the numbers table.

Step 3: Using the CEIL() Function

This step involves using the CEIL() function to retrieve the rounded-up values from the numbers table.

Basic CEIL()

SELECT value, CEIL(value) AS rounded_value
FROM numbers;

This query returns the smallest integer greater than or equal to each value in the 'value' column of the 'numbers' table.

CEIL() with Negative Values

SELECT value, CEIL(value) AS rounded_value
FROM numbers
WHERE value < 0;

This query returns the smallest integer greater than or equal to each negative value in the 'value' column of the 'numbers' table.

These queries demonstrate how to use the CEIL() function to retrieve the rounded-up values from the numbers table, including basic usage and handling negative values.

Conclusion

The PostgreSQL CEIL() function is a fundamental tool for rounding up numerical values to the nearest integer. Understanding how to use the CEIL() function and its syntax is essential for effective data retrieval and manipulation in PostgreSQL databases.