Open topic with navigation
SQL numeric data types include integer, fixed point types, and floating point types.
NUMBER is a NuoDB extension that differs from the SQL standard definition for this fixed point type.
The number of digits in a numeric type can be no more than 25000. This applies to all numbers: positive, negative, stored numbers and numbers that are part of calculations.
Numeric data does not have a concept of storage length. Partly because of this, the data storage algorithm does not impose a penalty for specifying the size of a field as larger than actually required.
Range of values for integer types are as follows:
If you assign a
DOUBLE to an integer type, or if you cast it to an integer type, it is rounded to an integer type. The rounding is to the nearest value, where .5 and higher are rounded up. For example,
CAST(0.5 AS INTEGER) returns
DECIMAL types are functionally equivalent in NuoDB. In a
DECIMAL fixed point type, the precision and scale are usually specified using the following syntax:
NUMERIC [ (
DECIMAL [ (
precisionis the number of digits in the number.
scaleis the number of digits to the right of the decimal point in the number.
If precision is not specified the default is 18. If scale is not specified the default is 0. If you want to specify scale then you must also specify precision. For example, a column declared:
has values with 6 digits, 4 of which are after the decimal point, such as
Limits for fixed point types are determined by the specified
scale. If a value has a scale that is greater than allowed, then it is rounded to fit. A value of 5 or more is rounded up. If a value has a greater precision than is allowed then it is rejected as an integrity violation. However, if rounding is needed then NuoDB SQL rounds the value before it evaluates the number of digits in the value. For example:
select cast(12.345 as numeric(4,2)) from dual;12.35
In the previous example, although the original number has 5 digits, the rounding reduces the number to 4 digits and this puts the number within the
(4,2) specification. Now consider this example:
select cast(12.345 as numeric(3,2)) from dual;Numeric Overflow: 12.35 does not fit in precision scale (3,2)
NuoDB SQL rounds first, as you can see by the
12.35 in the error message. The rounded value has 4 digits but the precision specification allows only 3 digits so the value is rejected.
Values with fixed point type, and precision greater than 18, have larger ranges than
INTEGER types. After 19 digits these values are stored using an internal data type designed for large integers, allowing larger ranges than
INTEGER types, and not restricted to ranges specified for
You can think of the NuoDB
NUMBER data type as a
NUMERIC type without a specified maximum precision and scale. A
NUMBER type is limited to no more than 25000 digits just like the other NuoDB SQL numeric types.
Although NuoDB does support the
NUMBER data type, it is recommended that
NUMBER only be used when the precision and scale value of the data is not known. The reasons for this are:
NUMBERdata type is used, and the scale of the data (the number of digits after the decimal point) is greater than 38, the results of sorting can be unpredictable, as NuoDB will only use the first 38 characters to determine ordering.
NUMBERdata type, no matter how small and no matter the scale, will require extra storage space.
Shows the NuoDB handling of
NUMBER data type versus
DECIMAL data type with precision and scale values provided.
USE test; CREATE TABLE test_tab (column1 NUMBER); INSERT INTO test_tab VALUES (1.12345678901234567890123456789012345678901); INSERT INTO test_tab VALUES (1.12345678901234567890123456789012345678908); INSERT INTO test_tab VALUES (1.12345678901234567890123456789012345678905); INSERT INTO test_tab VALUES (1.12345678901234567890123456789012345678903);/* sorting only uses the first 38 characters of the scale value */
SELECT * FROM test_tab ORDER BY column1;COLUMN1 ------------------------------------------- 1.12345678901234567890123456789012345678901 1.12345678901234567890123456789012345678908 1.12345678901234567890123456789012345678905 1.12345678901234567890123456789012345678903
DROP TABLE IF EXISTS test_tab; CREATE TABLE test_tab (column1 DECIMAL(42,41)); INSERT INTO test_tab VALUES (1.12345678901234567890123456789012345678901); INSERT INTO test_tab VALUES (1.12345678901234567890123456789012345678908); INSERT INTO test_tab VALUES (1.12345678901234567890123456789012345678905); INSERT INTO test_tab VALUES (1.12345678901234567890123456789012345678903); SELECT * FROM test_tab ORDER BY column1;COLUMN1 ------------------------------------------- 1.12345678901234567890123456789012345678901 1.12345678901234567890123456789012345678903 1.12345678901234567890123456789012345678905 1.12345678901234567890123456789012345678908
DOUBLE as a floating type.
The following table shows the range of values for
Null as values for
DOUBLE. There is no concept of
The order of values with regard to
ORDER BY, from lowest to highest, is
When comparing (that is, if
Null is greater than or less than any other value) the comparison always returns false.
SELECT * FROM t2;S1 ------ -inf nan 20 0 999.99 inf -1000
SELECT * FROM t2 WHERE s1>0;S1 ------ 20 999.99 inf
FLOAT values the same as
(float)Nan is the same as specifying
Aggregate functions supported by NuoDB include
SUM. These functions work with
-Inf as shown in the following table.
COUNT works as expected.
SUM report values as described in the following table, based on the kind of values specified as column values.
|Kind of Values in Column||Kind of Value Reported|
|Any combination of