# Using the CEILING Function in PostgreSQL

Get Started >>

## Introduction

If you’re using PostgreSQL to store and manage your data, you’ll quickly find out that there are many functions available to evaluate and manipulate numerical data. In this article, we’ll focus on the CEILING function in PostgreSQL, which is used to get the nearest value that is greater than or equal to a specified argument. We’ll look at a few examples of this function to get a better idea of how it’s used.

## Prerequisites

If you’re planning to follow along with the examples we’ll show in this tutorial, you’ll need to make sure you have PostgreSQL server installed and configured on your machine. The service should be running in the background. Windows and Linux users can download PostgreSQL here.

## What is the CEILING Function in PostgreSQL?

As we mentioned earlier, the CEILING function is a PostgreSQL numeric function that’s used to return the nearest integer greater than or equal to the given argument. Put simply, the CEILING function “rounds up” and returns the resulting integer value

Shown below is the syntax for the PostgreSQL CEILING function:

 1 CEIL(double_precision OR your_numeric_expression)

The alternative syntax looks like the following:

 1 CEILING(double_precision OR your_numeric_expression)

Whether you use the CEIL or CEILING syntax, the function requires one argument that represents either double precision or a numeric value.

Both versions of the function will return a value that is the same type as their input argument.

Let’s look at an example and see how the function CEIL() is used to round up numbers to the nearest integer. We’ll use the following statement:

 1 SELECT CEIL( 199.75);

We could also use the CEILING function:

 1 SELECT CEILING ( 199.75);

The results will look something like the following:

 1234567891011 testdatabase=# SELECT CEIL( 199.75); CEIL ------ 200 (1 ROW) testdatabase=# SELECT CEILING ( 199.75); CEILING --------- 200 (1 ROW)

We can see that the results are the same in both cases; we can also see that the returned value data type is the same as the input argument. In this case, the value is 200.

## Using the CEILING Function in PostgreSQL

Now that we understand how the CEILING and CEIL functions work, let’s use these functions to query a table.

Before we can proceed with this task, we’ll need to create tables to work with. In our example, we’ll want to determine the ceiling values for purchases made by clients, so we’ll create two tables named `purchases` and `client`.

First, we’ll create the `client` table:.

 123456 CREATE TABLE client ( p_client_id SERIAL PRIMARY KEY, c_first_name TEXT, c_last_name TEXT, c_email TEXT );

Then we’ll need to insert some records into the `client` table:

 1234567891011 INSERT INTO client (c_first_name, c_last_name, c_email) VALUES ('james','daniels','jd@tutorial.com'), ('jaime','abella','ja@tutorial.com'), ('yeshua','galisanao','yg@tutorial.com'), ('emma','huston','eh@tutorial.com'), ('shawn','bean','sb@tutorial.com'), ('kevin','hill','kh@tutorial.com'), ('abishai','galisanao','ag@tutorial.com'), ('dereck','rudolf','dr@tutorial.com'), ('greg','diaz','gd@tutorial.com'), ('raizel','galisanao','rg@tutorial.com');

We should get results that look something like this:

 12345678910111213 id | c_first_name | c_last_name | c_email ----+--------------+-------------+----------------- 1 | james | daniels | jd@tutorial.com 2 | jaime | abella | ja@tutorial.com 3 | yeshua | galisanao | yg@tutorial.com 4 | emma | huston | eh@tutorial.com 5 | shawn | bean | sb@tutorial.com 6 | kevin | hill | kh@tutorial.com 7 | abishai | galisanao | ag@tutorial.com 8 | dereck | rudolf | dr@tutorial.com 9 | greg | diaz | gd@tutorial.com 10 | raizel | galisanao | rg@tutorial.com (10 ROWS)

Next, we’ll create the `purchase` table:

 12345 CREATE TABLE purchases ( p_id SERIAL PRIMARY KEY, p_client_id INTEGER, p_amount DECIMAL );

We’ll insert records into this table:

 123456789101112131415 INSERT INTO purchases (p_client_id, p_amount) VALUES (1, 251.75), (1, 732.10), (2, 425.60), (2, 324.72), (3, 725.80), (3, 950.14), (4, 223.78), (5, 200.76), (5, 410.78), (6, 750.90), (7, 260.40), (8, 450.89), (8, 250.90), (9, 750.90);

The output will look like the following:

 1234567891011121314151617 p_id | p_client_id | p_amount ------+-------------+---------- 1 | 1 | 251.75 2 | 1 | 732.10 3 | 2 | 425.60 4 | 2 | 324.72 5 | 3 | 725.80 6 | 3 | 950.14 7 | 4 | 223.78 8 | 5 | 200.76 9 | 5 | 410.78 10 | 6 | 750.90 11 | 7 | 260.40 12 | 8 | 450.89 13 | 8 | 250.90 14 | 9 | 750.90 (14 ROWS)

Now that we’ve set up our tables and data, we’ll try our SELECT statement. The example shown below will calculate the ceiling value of the purchases made by the clients:

 123456789101112 SELECT c_first_name, c_last_name, CEIL(SUM( p_amount )) amt FROM client INNER JOIN purchases USING(p_client_id) GROUP BY p_client_id ORDER BY amt DESC;

The output of this query will look like this:

 123456789101112 c_first_name | c_last_name | amt --------------+-------------+------ yeshua | galisanao | 1676 james | daniels | 984 kevin | hill | 751 jaime | abella | 751 greg | diaz | 751 dereck | rudolf | 702 shawn | bean | 612 abishai | galisanao | 261 emma | huston | 224 (9 ROWS)

We can see that all the `amount` values were rounded up to the nearest whole number; therefore, we can confirm that the CEIL function worked properly.

## Conclusion

When you’re working with numeric values in PostgreSQL, it’s sometimes necessary to round numbers up or down, depending on the situation. In this tutorial, we explained how to use the CEILING function in PostgreSQL to round a number up to the nearest integer that’s greater than or equal to the number itself. With the examples we provided to serve as a guide, you’ll be able to implement this function in your own PostgreSQL database queries.

## Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

## Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.