How to Use the PostgreSQL Double Precision Type

Have a Database Problem? Speak with an Expert for Free
Get Started >>

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:

1
sudo -u postgres psql

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.

DATA TYPEDESCRIPTIONRANGE
double precision, float, float8decimal, floating-point numeric data, integer values15 decimal digits precision

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:

1
CREATE DATABASE some_db;

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:

1
2
3
CREATE TABLE TABLE_NAME(
        COL1 + DATA_TYPE + CONSTRAINT [OPTIONAL],
        COL2 + DATA_TYPE + CONSTRAINT [OPTIONAL]);

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

1
2
3
4
5
6
7
CREATE TABLE demo(
    id SERIAL PRIMARY KEY,
    str_col VARCHAR(100),
    int_col INT NOT NULL,
    boolean_col BOOL,
    float_col DOUBLE PRECISION
);

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

1
2
3
4
5
6
7
8
9
10
                                      Table "public.demo"
   Column    |          Type          | Collation | Nullable |             Default              
-------------+------------------------+-----------+----------+----------------------------------
 id          | integer                |           | not null | nextval('demo_id_seq'::regclass)
 str_col     | character varying(100) |           |          |
 int_col     | integer                |           | not null |
 boolean_col | boolean                |           |          |
 float_col   | double precision       |           |          |
Indexes:
    "demo_pkey" PRIMARY KEY, btree (id)

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:

1
2
3
4
INSERT INTO demo (str_col, int_col, boolean_col, float_col)
VALUES('ObjectRocket', 1234, TRUE, 09673143120),
    ('Examples', 6688, FALSE, 09057046055),
    ('Tutorials', 9678, TRUE, 09129158355);

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

1
SELECT id, float_col FROM demo;

The output will look like the following:

1
2
3
4
5
 id | float_col  
----+------------
  1 | 9673143120
  2 | 9057046055
  3 | 9129158355

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.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.