The SQL DECIMAL data type is useful for storing numbers with decimal places. Learn all about it, including which databases support it, and how to work with it, in this guide.
What is SQL Decimal?
SQL Decimal is a data type for storing numbers with decimal places. It’s available in several databases, as it’s part of the ANSI standard, and is an exact number (as opposed to float which is an approximate number).
Which Databases Support Decimal?
The DECIMAL data type is available in several databases, as it is part of the SQL standard:
It’s probably available in many more databases, but I write about Oracle, SQL Server, MySQL, and Postgres on this site, so that’s what I’ve listed here.
In SQL Server, MySQL, and Postgres, the NUMERIC data type is equivalent to DECIMAL. In Oracle, the DECIMAL is translated to a NUMBER data type.
How to Create a Decimal Data Type
You can declare a DECIMAL data type like this:
DECIMAL (p, s)The syntax is the same in each vendor and includes:
- p stands for precision and is the total number of digits.
- s stands for scale and is the number of decimal places.
The range of values for precision and scale is different for each vendor:
| Criteria | Oracle | SQL Server | MySQL | Postgres |
| Range of precision | 1 to 38 | 1 to 38 | 1 to 65 | 1 to 131,072 |
| Range of scape | -84 to 127 | 1 to the value of p | 0 to 30, cannot be larger than p | 0 to 16,383, cannot be larger than p |
Examples
Let’s see some examples of creating an SQL DECIMAL data type, including how to insert data into a decimal and how to select data.
Example 1: Create Table with Decimal
This example shows how to create a new table with a decimal field.
CREATE TABLE bank_account (
account_id INT,
account_name VARCHAR(200),
account_balance DECIMAL(20,2)
);This example uses an account_balance column with a DECIMAL data type. It has a precision of 20 and a scale of 2. This means it can store 20 total digits: 18 to the left of the decimal and 2 to the right of the decimal.
Example 2: Insert with Decimal
This example shows how to insert a DECIMAL value. We’ll use the bank_account table from the previous example.
INSERT INTO bank_account (account_id, account_name, account_balance)
VALUES (1, 'John', 4502.25);The record is inserted.
If we select from the table, we can see the value.
SELECT account_id,
account_name,
account_balance
FROM bank_account;| account_id | account_name | account_balance |
| 1 | John | 4502.25 |
Example 3: Precision and Scale
Here are some examples of working with DECIMAL data types of different precisions and scales.
CREATE TABLE decimal_test (
decval DECIMAL (6, 2)
);Let’s insert some values.
INSERT INTO decimal_test (decval)
VALUES (1234.56);We can select from this table and see the value is returned.
SELECT decval
FROM decimal_test;| decval |
| 1234.56 |
Let’s insert a value that has 5 digits to the left of the decimal.
INSERT INTO decimal_test (decval)
VALUES (98765.43);We get an error message:
Error Code: 1264. Out of range value for column 'decval' at row 1
This error occurs in MySQL. The error messages in other databases will be different, but it has the same meaning: the value we inserted is too large for the column.
The decimal column can only accept a value with a maximum of 6 digits, and our value has 7.
What if we try to insert a value with 5 digits and 1 digit after the decimal?
INSERT INTO decimal_test (decval)
VALUES (45678.1);We get the same error:
Error Code: 1264. Out of range value for column 'decval' at row 1
So, even though the number is within the maximum of 6, it shows an error because the 6 digits actually includes the 2 decimal places, so there can only be 4 digits to the left of the decimal.
FAQ
Here are some common questions for working with decimals in SQL.
How do you convert to 2 decimal places in SQL?
You can use either the CONVERT function or the CAST function, depending on your database vendor.
CONVERT (your_decimal, NUMBER)Or using CAST:
CAST (your_decimal AS DECIMAL(x, 2))
SQL Decimal vs Float: What’s the difference?
The difference between decimal and float is that decimal stores an exact number and float stores an approximation.
This is due to the way that float values are represented in the database, and programming in general.
If you want to store a decimal value and be sure that the correct value is stored, use decimal. If you don’t care about having an exact level of precision, use float.
SQL Numeric vs Decimal: What’s the difference?
In each of the databases in this guide (Oracle, SQL Server, MySQL, and Postgres), there is no difference. Numeric is an alias of Decimal, which means if you use Numeric you’re actually creating a Decimal data type.
However, according to the SQL Standard, there is a difference. The database vendors haven’t implemented these differences, but here they are for your information.
The SQL standard (as shared in a post on the Postgres website) says:
In a quick look in the standard it appears that the only difference is this:
17) NUMERIC specifies the data type exact numeric, with the decimal precision and scale specified by the <precision> and <scale>.
18) DECIMAL specifies the data type exact numeric, with the decimal scale specified by the <scale> and the implementation-defined decimal precision equal to or greater than the value of the specified <precision>.
Conclusion
The SQL DECIMAL data type is part of the SQL standard and is implemented in several different vendors. It’s a handy data type for storing numbers with decimal places.