This article will discuss what and how to use the numeric and decimal types. We will also explore the differences between the two types described in Standard SQL.
Numeric in Standard SQL
When defining a numeric value in Standard SQL, you provide two main arguments: precision and scale.
The syntax is as shown below:
The precision determines the maximum number of digits a value can have. (including the decimal points). On the other hand, the scale sets the maximum number of digits after the decimal points.
For example, if the value is 3.14159, the precision is 6 with a scale of 5
In Standard SQL, a numeric can store values in the range of -10^38+1 to 10^38
Hence, the precision value for a numeric type is 38. However, unless explicitly specified, SQL will default to a precision value of 18.
The default scale value is 0. The scale parameter can only be specified when the precision parameter is included.
Decimal in Standard SQL
There is not much difference in the definition of decimal and numeric type in Standard. The syntax for decimal definition is as shown below:
Like a numeric type, a decimal can store values from -10^38+1 to 10^38.
Difference Between Numeric vs. Decimal
Okay, if both numeric and decimal types share the same declaration and range, what is the difference?
The difference lies in how both types handle the precision value.
In numeric types, the precision value is fixed. This means that you cannot exceed the precision value.
In decimals, on the other hand, you can exceed the precision value. This provides for flexibility in the total number of values you can store.
For example, if you define a decimal as:
It means you can have more than ten values
Yes, that is the only difference.
Numeric and Decimal in other SQL Engines
As we stated earlier, various database engines provide variations from the ANSI Standard SQL. The following is how these changes apply to numeric and decimal types.
MySQL
In MySQL, the numeric type is implemented as a decimal. This removes the need to worry about the precision and scale difference.
PostgreSQL
On the other hand, PostgreSQL preserves the functionality and difference between numeric and decimal as provided in Standard SQL.
SQL Server
In SQL Server, both decimal and numeric are fundamentally similar. Furthermore, SQL Server treats a decimal as the synonym for numeric. Hence, you can use both types interchangeably.
Check the resource below for more:
BigQuery
In BigQuery, a numeric is treated as an alias to decimal type.
Oracle
In Oracle databases, decimal and numeric are different. For example, a decimal in oracle can hold up to 15 significant numbers. On the other hand, a numeric can hold up to 38 significant digits.
NOTE: Numeric in Oracle is renamed to NUMBER.
Conclusion
This article outlines the difference between a decimal and numeric data type as defined in Standard SQL and other database engines.
As always, thanks for reading!
