How do I Round in PostgreSQL?

Whenever we talk about rounding off a number, we have a mechanism to truncate a specific part of that number and keep the rest. In PostgreSQL, we have the “ROUND” function that serves this purpose. The syntax of this function is as follows:

# SELECT ROUND(NumberToBeRoundedOff, DecimalCount);

Here, NumberToBeRoundedOff represents the number you want to round off, whereas DecimalCount will tell the number of decimal places to be kept. We will explore in this article how to use this function effectively in PostgreSQL in Windows 10.

How do I Round in PostgreSQL in Windows 10?

The usage of the “ROUND” function in PostgreSQL in Windows 10 can be understood very well with the help of the examples that follow:

Note: Before executing all the examples shared below, you must access the PostgreSQL environment through Windows 10 command prompt.

Example # 1: Rounding to the Same Integer:

You can round a decimal number to the very same integer if its decimal part is less than “5”. For obtaining such a result, you can take a look at the following query:

# SELECT ROUND(3.3);

The “SELECT” statement in the above query will display the results on the console, whereas the “ROUND” function will do the actual magic of rounding the specified number to the same integer since the value of the decimal part was less than “5”.

You can visualize the results of this query from the image shown below. You can easily verify that “3.3” was rounded to “3”.

Example # 2: Rounding to One Greater Integer:

Now, if you want to see a decimal number is rounded off to one greater integer, then you can run the following query in your PostgreSQL console:

# SELECT ROUND(3.7);

Again, the “SELECT” statement will present the results on the console, whereas the “ROUND” function will round off the specified decimal number to one greater integer since the value of the decimal part was greater than “5”.

The exact result of this query can be seen in the image appended below. This time, the provided number, i.e., “3.7” was rounded off to one greater integer, i.e., “4”.

Example # 3: Rounding to Two Decimal Places:

We can also choose the number of decimal places to be displayed after rounding off the specified number. In the two examples shown above, we have simply truncated the decimal parts and only kept the integer parts, but now we are interested in exploring how to get a decimal number correct up to the specified number of decimal places after rounding it off. We also need to use the second argument of the “ROUND” function, i.e., the decimal count. To demonstrate this, we have designed the cited query for you:

# SELECT ROUND(3.745,2);

We wanted to round off the number “3.745” correct up to “2” decimal places in this query.

The result of this query can be seen from the image affixed below. The number “3.745” was rounded off as “3.75” i.e., correct up to “2” decimal places.

Example # 4: Negatively Rounding Using -1 Decimal Count:

If you only want to keep the integer part of a decimal number and want the number/numbers to the left of the decimal to be converted to zero, you should use a negative decimal count. The following query depicts this:

# SELECT ROUND(374.5, -1);

Since we have used the decimal count of “-1” in this query, the first digit left to the decimal will be converted to zero.

You can verify these results from the image cited below. The number “374.5” has been converted to “370” because of using the “-1” decimal count. Moreover, since “4” was less than “5”, it did not impact the value next to it, i.e., it remained the same.

Example # 5: Negatively Rounding Using -2 Decimal Count:

This is just another example depicting the usage of the negative decimal count in PostgreSQL in Windows 10. The exact query for this particular example is as follows:

# SELECT ROUND(374.5, -2);

The decimal number provided in this query is the same as our example # 4. Only the decimal count has changed to “-2”. Now, this decimal count will convert the first two digits left to the decimal to zero.

This result can be verified from the image shown below. The number “374.5” in this case has been rounded off to “400” because of using the “-2” decimal count. Moreover, since “7” was greater than “5”, therefore, the number “3” has also changed to “4”, i.e., it has been incremented.

Example # 6: Rounding the Values of a PostgreSQL Table:

Up till now, we have sufficiently talked about using the “ROUND” function in one-liner PostgreSQL statements where this function was rounding off a single number provided. However, this function also has other capabilities, such as rounding off an entire column or columns of a table to the specified number of decimal places. You can learn the method of doing so by going through the following steps:

Step # 1: PostgreSQL Table Creation in Windows 10:

First, we will create a PostgreSQL table with the help of the query stated below:

# CREATE TABLE product_Prices(Number INT NOT NULL, Price DECIMAL NOT NULL);

This query will create a table named “product_Prices” with two attributes, i.e., Number and Price.

The execution of this query will display the following result:

Step # 2: PostgreSQL Table Value Assignment in Windows 10:

After creating the table, we will populate it by assigning values with the help of the query shown below:

# INSERT INTO product_Prices VALUES(1,100.245);

The insertion of a record in the table will display the following result:

By using the very same query, you can insert multiple records to this table, as shown in the image below:

Step # 3: Displaying the Current Values of the PostgreSQL Table in Windows 10:

Once our table is populated, we can take a look at the values that it holds currently by executing the following query:

# SELECT * FROM product_Prices;

All the records of the specified table are shown in the image below

Step # 4: Rounding off a Specified Column of the Created PostgreSQL Table in Windows 10:

Now, we will round off the decimal column of this table correct up to two decimal places with the help of the following query:

# SELECT ROUND(Price,2) FROM product_Prices;

Here, “Price” represents the name of the decimal column to be rounded off, whereas “2” specifies the number of decimal places to be kept.

You can see the results of this query from the image shown below and can verify that all the entries of the “Price” column of the “product_Prices” table have been rounded off up to two decimal places.

Also, if you will execute the above-stated query without specifying a decimal count, then only the integer parts of all the values of the “Price” column will be kept after rounding off, as shown in the following image:

Conclusion:

By reading this article, you will be able to learn the correct usage of the “ROUND” function of PostgreSQL in Windows 10. After doing so, you will be able to use this function effectively while working with PostgreSQL.

How do I Round in PostgreSQL?