How to Use the PostgreSQL Double Precision Type
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.
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:
sudo -u postgres psql
This command provides us with the access privileges of the
The PostgreSQL DOUBLE PRECISION type
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.
|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:
CREATE DATABASE some_db;
NOTE: Feel free to use the database name of your choice.
Use the command
\lto display a list of all databases that exist in your PostgreSQL database cluster.
\ccommand 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:
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:
CREATE TABLE demo(
id SERIAL PRIMARY KEY,
int_col INT NOT NULL,
float_col DOUBLE PRECISION
To view the table information, just use the command
\d demo;. The output of this command will look like this:
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 | | |
"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:
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:
SELECT id, float_col FROM demo;
The output will look like the following:
id | float_col
1 | 9673143120
2 | 9057046055
3 | 9129158355
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