Decimal vs Double in SQL
Introduction
Postgresql supports a wide variety of native data types. Among them are text data types, numeric data types, date and times, Boolean data type etc. Creation of data types in Postgresql is easily done using the CREATE TYPE command. In this article we will focus on two types of data; the decimal and the double data types. The two data types are categorized as part of the numeric data types. The difference between the two types can be considered in terms of the storage size and the precision – the number of digits the number can hold including the digits on the right and left of the decimal point. Without further ado, let’s jump into decimal vs double in SQL and find out more.
Decimal Type
The DECIMAL data type takes two arguments, decimal(p, s) where p is the maximum number of digits to hold between 1 and 131072, the s is the number of the digits to the right of the decimal point to store.
Practical examples using decimals
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | $ CREATE TABLE decimal_values ( id SERIAL, item VARCHAR(50) NOT NULL, VALUE DECIMAL(13, 2) NOT NULL, PRIMARY KEY(id) ); CREATE TABLE $ INSERT INTO decimal_values (item, VALUE) VALUES ('Test Item', 12.35); INSERT 0 1 $ INSERT INTO decimal_values (item, VALUE) VALUES ('Test Item', 1012.305); INSERT 0 1 $ INSERT INTO decimal_values (item, VALUE) VALUES ('Test Item', 1212.3894); INSERT 0 1 $ SELECT * FROM decimal_values; id | item | VALUE ----+-----------+------- 1 | Test Item | 12.35 2 | Test Item | 1012.31 3 | Test Item | 1212.39 |
Double Type
Double types are used when we are not certain of the behavior of our data. The input data is interpreted as floating point integer values. Some data may take more digits to the right of the decimal point. While the storage size of the decimal type is variable, the double type takes 8 bytes storage size. Also double precision ranges up to fifteen decimal digits.
Practical examples using double
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | $ CREATE TABLE double_values ( id SERIAL, item VARCHAR(50) NOT NULL, VALUE DOUBLE PRECISION, PRIMARY KEY(id) ); CREATE TABLE $ INSERT INTO double_values (item, VALUE) VALUES ('Test Item', 1532.3985); INSERT 0 1 $ INSERT INTO double_values (item, VALUE) VALUES ('Test Item', 12312.3559023); INSERT 0 1 $ INSERT INTO double_values (item, VALUE) VALUES ('Test Item', 11935.372014); SELECT * FROM double_values; |
Conclusion
We hope this article cleared any confusion you had on the difference between decimal and double data types in SQL. We explained when to use each type as well as showed examples of how to specify each type along with the parameters they require. Take some time thinking about your data ahead of time and choose your data types wisely because it will pay dividends in the long run.
If you need someone you trust to manage a Postgres, CockroachDB, or Redis please don’t hesitate to reach out to us at Object Rocket. We’re always eager to help you with your database needs. We don’t just handle SQL databases but we manage NoSQL databases like MongoDB as well.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started