Przejdź do głównej treści

PostgreSQL FLOOR

The `FLOOR` function in PostgreSQL is a mathematical function used to return the largest integer less than or equal to a specified numeric value. It is commonly used in scenarios where rounding down to the nearest whole number is required.

Usage

The `FLOOR` function is typically used when you need to disregard the fractional part of a number and obtain its integer part for calculations or data analysis. It is especially useful in financial computations or when managing indices. `FLOOR` is part of PostgreSQL's standard SQL mathematical functions, ensuring reliability and standardization.

FLOOR(numeric_expression)

In this syntax, `numeric_expression` is the number for which you want to find the largest integer less than or equal to it. Compatible data types for `numeric_expression` include `double precision`, `numeric`, and other numeric types.

Examples

1. Basic Usage

SELECT FLOOR(4.75);

In this example, the function returns `4`, as it's the largest integer less than or equal to `4.75`.

2. Using FLOOR with Negative Numbers

SELECT FLOOR(-3.14);

Here, the function returns `-4`, since `-4` is the largest integer less than `-3.14`.

3. Applying FLOOR in a Table Query

SELECT product_id, FLOOR(price) AS rounded_price
FROM products;

This query retrieves each product's ID and its price rounded down to the nearest integer, useful for creating simplified price lists.

Tips and Best Practices

  • Ensure numeric data type. Use `FLOOR` on numeric data types to avoid unexpected errors or type conversions.
  • Combine with other functions. Use `FLOOR` in combination with other mathematical functions like `CEIL` or `ROUND` for complex calculations.
  • Consider performance. When using `FLOOR` in large datasets, ensure that it doesn't become a performance bottleneck, especially in SELECT statements.
  • Be mindful of negatives. Remember that `FLOOR` will decrease the integer value for negative numbers, which might differ from expectations.
  • Impact on indexed columns. Be aware that using `FLOOR` on indexed columns might affect query optimization and performance.