How to Use the PostgreSQL Double Precision Type

Introduction

When you need to store numeric values with a large number of decimal digits, you need to make sure you utilize the correct data type for the task. PostgreSQL provides the DOUBLE PRECISION data type for this kind of numeric data– the data type offers 8 bytes of storage and can hold up to 15 decimal digit. In this article, we’ll discuss the PostgreSQL DOUBLE PRECISION data type and show you how to use this data type in queries and insert operations.

Prerequisites

In order to follow along with the examples in this tutorial, you’ll need to have PostgreSQL installed and running. You can use the psql -V command in your terminal to confirm that PostgreSQL is installed and display its version number. Make sure that your installation of Postgres is working before you proceed to the DOUBLE PRECISION query examples later in this tutorial.

Open the psql command-line console

Let’s begin by opening the interactive shell for PostgreSQL. We’ll use the command shown below:

This command provides us with the access privileges of the postgres superuser.

The PostgreSQL DOUBLE PRECISION type

The PostgreSQL DOUBLE PRECISION type is a numeric data type; it’s also known by the alternate name float8. Double precision values are treated as floating point values in PostgreSQL. This means that some rounding will occur if you try to store a value with “too many” decimal digits; for example, if you tried to store the result of 2/3, there would be some rounding when the 15th digit was reached. In most cases, there’s no real consequence of this approximation, but there may be circumstances where absolute precision is required and the NUMERIC data type is the better choice.

Create a database and table in PostgreSQL

In this section, we’ll create a table a with a DOUBLE PRECISION data type. Before we do that, we’ll need to create a database in PostgreSQL. We’ll use the command shown below:

NOTE: Feel free to use the database name of your choice.

• Use the command \l to display a list of all databases that exist in your PostgreSQL database cluster.

• Use the \c command to connect to a specific database.

Once you’ve created your database, you can create a table within it.

The syntax for creating a table is shown below:

Here’s the SQL statement we’ll use to create our table:

To view the table information, just use the command \d demo;. The output of this command will look like this:

Insert some values inside the DOUBLE PRECISION column

We can use an INSERT statement to insert data in our DOUBLE PRECISION column as well as the other columns of the table:

To see the values in the float_column, use the simple SELECT statement shown below:

The output will look like the following:

Conclusion

If you’re planning to store numeric values that require a certain precision or length, the DOUBLE PRECISION data type may be the right choice for your needs. In this article, we provided an introduction to the PostgreSQL DOUBLE PRECISION type and reviewed some examples of how to insert and retrieve data of this type. With these examples to serve as a guide, you’ll be prepared to use the DOUBLE PRECISION data type in your own PostgreSQL database.