The PostgreSQL FLOOR()
function is used to return the largest integer value that is less than or equal to a given number. This function is essential for rounding down numerical values to the nearest integer.
FLOOR(number)
The FLOOR()
function has the following component:
number
: The number to be rounded down.Let's look at some examples of PostgreSQL FLOOR()
function queries:
SELECT FLOOR(4.7) AS rounded_value;
This query returns the largest integer value less than or equal to 4.7, which is 4.
SELECT value, FLOOR(value) AS rounded_value
FROM numbers;
This query retrieves the value
and its rounded-down value from the numbers
table.
SELECT value, FLOOR(value) AS rounded_value
FROM numbers
WHERE value < 0;
This query retrieves the value
and its rounded-down value from the numbers
table where the value
is negative.
Let's go through a complete example that includes creating a table, inserting data, and using the FLOOR() function to retrieve the rounded-down values.
This step involves creating a new table named numbers
to store numerical data.
CREATE TABLE numbers (
id SERIAL PRIMARY KEY,
value NUMERIC
);
In this example, we create a table named numbers
with columns for id
and value
.
This step involves inserting some sample data into the numbers
table.
INSERT INTO numbers (value)
VALUES (4.7),
(2.3),
(-3.8),
(7.9);
Here, we insert data into the numbers
table.
This step involves using the FLOOR()
function to retrieve the rounded-down values from the numbers
table.
-- Basic FLOOR()
SELECT value, FLOOR(value) AS rounded_value
FROM numbers;
-- FLOOR() with Negative Values
SELECT value, FLOOR(value) AS rounded_value
FROM numbers
WHERE value < 0;
These queries demonstrate how to use the FLOOR()
function to retrieve the rounded-down values from the numbers
table, including basic usage and handling negative values.
The PostgreSQL FLOOR()
function is a fundamental tool for rounding down numerical values to the nearest integer. Understanding how to use the FLOOR()
function and its syntax is essential for effective data retrieval and manipulation in PostgreSQL databases.