PostgreSQL – FLOOR Function
In PostgreSQL, the FLOOR() function is used to round a numeric value down to the nearest integer that is less than or equal to the original value.
The basic syntax of the FLOOR() function is as follows:
FLOOR(numeric_expression)where numeric_expression is the numeric value or expression that you want to round down to the nearest integer.
Below are some examples of FLOOR() function:
- Rounding down a positive decimal number:
SELECT FLOOR(5.7) AS rounded_value;This query will return 5 since the nearest integer less than or equal to 5.7 is 5.
- Rounding down a negative decimal number:
SELECT FLOOR(-3.2) AS rounded_value;This query will return -4 since the nearest integer less than or equal to -3.2 is -4.
- Rounding down an expression:
SELECT FLOOR(10 / 3) AS rounded_value;This query will return 3 since the nearest integer less than or equal to 10 / 3 (which is approximately 3.3333) is 3.
The FLOOR() function is useful when you need to ensure that a value is rounded down to the nearest integer, regardless of whether it is positive or negative.
FLOOR() function returns the result as a numeric data type. If you need the result in a different data type, you can explicitly cast it to the desired type using the CAST() or :: operator.
For example:
SELECT FLOOR(5.7)::integer AS rounded_integer;This query will return 5 as an integer data type.