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:
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 TYPE | DESCRIPTION | RANGE |
---|---|---|
double precision, float, float8 | decimal, floating-point numeric data, integer values | 15 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