Using the CEILING Function in PostgreSQL
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:
1 2 3 4 5 6 7 8 9 10 11 | 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:.
1 2 3 4 5 6 | 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:
1 2 3 4 5 6 7 8 9 10 11 | 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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:
1 2 3 4 5 | CREATE TABLE purchases ( p_id SERIAL PRIMARY KEY, p_client_id INTEGER, p_amount DECIMAL ); |
We’ll insert records into this table:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | 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:
1 2 3 4 5 6 7 8 9 10 11 12 | 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:
1 2 3 4 5 6 7 8 9 10 11 12 | 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.
Get Started