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.
SMALLINT
, INTEGER
, BIGINT
Range of values for integer types are as follows:
Type  Lowest  Highest 

SMALLINT  32768  32767 
INTEGER  2147483648  2147483647 
BIGINT  9223372036854775808  9223372036854775807 
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 1
.
NUMERIC
, DECIMAL
, NUMBER
The DECIMAL
data type is an alias for the NUMERIC
data type. For a NUMERIC
fixed point type, the precision and scale are usually specified using the following syntax:
NUMERIC [ (
precision
[,scale
]) ]
DECIMAL [ (
precision
[,scale
]) ]
where
precision
is the number of digits in the number.scale
is 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:
DECIMAL(6,4)
has values with 6 digits, 4 of which are after the decimal point, such as 12.3456
.
Limits for fixed point types are determined by the specified precision
and 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:
SQL> 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:
SQL> 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 BIGINT
.
You can think of the NuoDB NUMBER
data type as a DECIMAL
or 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:
NUMBER
data 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.NUMBER
data 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.12345678901234567890123456789012345678903DROP 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
NuoDB supports DOUBLE
as a floating type.
The following table shows the range of values for DOUBLE
:
Type  Lowest  Highest 

DOUBLE  1.7976931348623157E+308  2.2250738585072014E308 
0  
2.2250738585072014E308  1.7976931348623157E+308 
NaN
, Inf
, Inf
NuoSQL supports NaN
, Inf
, Inf
, and Null
as values for DOUBLE
. There is no concept of Nan
.
The order of values with regard to ORDER BY
, from lowest to highest, is Null
, Nan
, Inf
, Inf
.
When comparing (that is, if Nan
or Null
is greater than or less than any other value) the comparison always returns false.
For example:
SELECT * FROM t2;
S1  inf nan 20 0 999.99 inf 1000SELECT * FROM t2 WHERE s1>0;
S1  20 999.99 inf
NuoDB treats FLOAT
values the same as DOUBLE
. Specifying (float)Nan
is the same as specifying (double)Nan
.
Aggregate functions supported by NuoDB include COUNT
, AVG
and SUM
. These functions work with NaN
, Inf
, and Inf
as shown in the following table.
COUNT
works as expected.
AVG
and 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 

Nan

NaN

Inf

Inf

Inf

Inf

Any combination of NaN , Inf , or Inf 
NaN
