While solving percentage questions in mathematics and learning the accuracy in physics, you may have used the concept of round-off. The floor function of the PostgreSQL database is a little similar to round-off in working, but it adds up a new whole thing, which makes it unique. The “floor” function of PostgreSQL takes a decimal point number and returns the nearest smallest value of it after conversion. It works differently on negative and positive integers as the negative integers are quite the opposite of positive integers. Therefore, we have decided to deliver this article for our users to learn about using the PostgreSQL Floor function in Windows 10.
Let’s make a new start with the opening of PostgreSQL Shell. Use the Windows 10 desktop search bar to write “psql”. The PostgreSQL Shell application will be shown in the application area, and you have to click on it to quickly open it. The PostgreSQL shell black screen will be opened as presented below. It will not allow you to query your commands until you add the database name, user name, and password along with the port number. The default database and user name are “Postgres” and you can use it to quickly open the query area. We have been using our just made new database “aqsayasin” and a new user “aqsayasin” along with its password. Port number “5432” must be provided along with this information.
Example 01:
Within our first example, we will look at the working of floor function while applying some non-decimal integer values. The SELECT instruction will be useful for this purpose. The floor function will take the integer values in its argument within this query and convert them to the nearest smallest accurate value. As the value “23” is already a complete, accurate integer value, thus it doesn’t convert it more and returns the original value in the output, as shown below.
Let’s say how the Floor function of the PostgreSQL database works on the accurate negative integer values. Therefore, we have been taking “-23” in the parameters of the Floor function and executing the SELECT instruction. The instruction output shows the return of the same negative integer values as presented below. This means whether the value is negative or positive, the “floor” function will return it if it is accurate and has no decimal points.
Example 02:
Now, let’s check how the floor function works on floating-point values in the PostgreSQL database shell. Let’s start with the positive float value to take its floor in the shell. Thus, the same SELECT instruction uses the FLOOR function taking value “92.8” in its argument. This instruction shows the exact accurate integer “92” as output, which is the nearest smallest value of “92.8”. You can see that the floor is quite different from the round-off method. Within the round-off, the value before the point is incremented by 1 when the value after the point is greater than or equal to 5. While in the “floor” function, the value is not round-off but converted to an exact accurate nearest lowest value which is 92 for 92.8
Let’s take another value to see how floor function works. Upon using 99.9, we haven’t got 100 as the nearest round-off value but 99 as the nearest smallest accurate value upon applying the “floor” function in SELECT instruction. You can see the output demonstrated below.
This was about the use of floor function on the positive decimal point number. Let’s see how it works on the negative decimal point integer value. This time, we have been using the “-9.9” in the parameters of the “floor” function within the SELECT query. The output for this command is showing the negative value “-10”. As the nearest small value for “-9.9” is “-10”, and “-9” is the nearest largest accurate value; thus, the floor function is showing the “-10” as output.
Example 03:
Let’s take a look at the opposite function of the “floor” method in the PostgreSQL database, i.e., the CEILING method. It works the same as the floor function but in the opposite direction. This implies that the CEILING function will get the exact accurate nearest largest value of the specified decimal point value. While the FLOOR function will get the exact accurate nearest lowest value of the specified decimal point value. We have been using both the functions in the same SELECT query. Both the functions utilize the same decimal value, “23.56”. On execution, we have got two column records for this instruction. The floor function is returning “23” and the ceiling function is returning “24” for the decimal value “23.56”.
Let’s use the negative decimal point value “-23.56” for both the functions in the SELECT instruction, i.e., Floor and Ceiling. The output for this instruction shows the “-24” value for the floor and “-23” for the ceiling function as the “-24” is the nearest small value, and “-23” is the nearest large value for the negative decimal point value “-23.56”.
Example 04:
Let’s have another example to see how we can use the Floor function on the columns of tables. We must have some tables with records in our PostgreSQL database. Therefore, we have been creating a new table, “Ftest” having 3 columns “ID”, “Country”, and “Number” with the use of CREATE TABLE instruction in the PostgreSQL shell. The command shown in the image has been executed and created a table. The SELECT instruction with the “*” will be used to query the table records, and the empty table is displayed.
Let’s insert some records in the just made table “Ftest”. Make use of INSERT INTO instruction to do so. We have added a total of 7 records in the column ID, Country, Number of the table. The table column “Number” has some positive and some negative decimal point values.
Using the SELECT instruction for the table “Ftest”, we have got all the records of this table utilizing the “*” sign. All 7 records have been displayed on the PostgreSQL shell.
Let’s fetch all the records from the table “Ftest” using the SELECT instruction with the “*” sign. Also, the floor of all the values in the column “Number” using the FLOOR function on the particular column “Number”. The output table displays all the column values for the ID, Country, and Number column along with the floor of the “Number” column. All the number column values have been converted to the nearest small value beneath the “floor” column.
The same statement query has been utilized to find out the floor and ceiling for a column “Number” as shown in the attached output photo.
Conclusion:
This article contains the explanation of getting the nearest small value for a particular integer or decimal point number using the PostgreSQL “Floor” function. This article has been implemented on the PostgreSQL shell, while you can also implement it PostgreSQL pgAdmin Graphical User Interface. The examples we have discussed will let you know the method to find out the floor for positive and negative floating-point numbers and also for a simple integer. With the comparison of the Ceiling function, we have made the concept of the “floor” function clearer.
