How Use Float Data Type in PostgreSQL?

PostgreSQL is a powerful and popular open-source relational database management system. It supports various data types, including the float data type, which allows you to store decimal numbers with a floating-point precision. In this tutorial, we will explore how to use the float data type in PostgreSQL and perform basic operations with it.

Defining a Float Column

To use the float data type in PostgreSQL, you need to define a column with the float type in your table. Let’s consider an example where we have a table named products with a column named price of type float:

CREATE TABLE products (
    id serial PRIMARY KEY,
    name varchar(255) NOT NULL,
    price float
);

In this example, the price column is defined as float. This means that it can store decimal numbers with floating-point precision.

Inserting Float Values

To insert values into a table with a float column, you need to provide valid float values. For example, let’s insert some sample values into the products table:

INSERT INTO products (name, price)
VALUES ('Product 1', 10.99),
       ('Product 2', 19.99),
       ('Product 3', 7.50);

In this example, we are inserting three rows into the products table. Each row consists of a name and a price value. The price values are decimal numbers represented as floats.

Selecting Float Values

To select and retrieve float values from a table in PostgreSQL, you can use simple SELECT statements. For example:

SELECT name, price
FROM products;

This query will retrieve the name and price columns from the products table. The price column will be returned as float values.

Performing Arithmetic Operations with Float Values

In PostgreSQL, you can perform various arithmetic operations on float values. Let’s consider a simple example where we want to calculate the total price of all products:

SELECT SUM(price) AS total_price
FROM products;

In this example, we are using the SUM() function to calculate the sum of all prices in the products table. The result is returned as a float value with the alias total_price.

Rounding Float Values

Sometimes, you may need to round float values to a specific decimal precision. PostgreSQL provides several functions for rounding float values, such as ROUND(), CEIL(), and FLOOR(). Let’s see an example using the ROUND() function:

SELECT name, price, ROUND(price, 2) AS rounded_price
FROM products;

In this example, we are selecting the name, price, and a rounded version of the price column using the ROUND() function with a precision of 2 decimal places. The result is returned as a new column called rounded_price.

Note:

• The ROUND() function rounds a float value to the nearest integer or decimal precision specified.
• The CEIL() function rounds up to the nearest integer or decimal precision specified.
• The FLOOR() function rounds down to the nearest integer or decimal precision specified.

Conclusion

In this tutorial, we explored how to use the float data type in PostgreSQL. We learned how to define a float column, insert and select float values, perform arithmetic operations with floats, and round float values to a specific decimal precision. Understanding and effectively using the float data type in PostgreSQL will allow you to store and manipulate decimal numbers with floating-point precision in your database.