SQL Numeric Types

SQL numeric data types include integer, fixed point types, and floating point types.

Integer Types: SMALLINT, INTEGER, BIGINT

Range of values for integer types are as follows:

Type Lowest Highest

SMALLINT

-32768

32767

INTEGER

-2147483648

2147483647

BIGINT

-9223372036854775808

9223372036854775807

Fixed Point Types: NUMERIC, DECIMAL

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. Conversions from values with a larger scale to values with a smaller scale are performed through rounding (i.e. a least significat digit of 5 or more is rounded up). Conversions from values with a larger integral part (i.e. digits before the decimal point) to values with smaller integral part result in an overflow error. For example:

SQL> select cast(12.345 as numeric(4,2)) from dual;
12.35

SQL> select cast(12.345 as numeric(3,2)) from dual;
Numeric Overflow: 12.35 does not fit in precision scale (3,2)

The maximum precision for a NUMERIC type is 38. An error is returned if a NUMERIC data type is defined with precision larger than 38. An arithmetic overflow is returned if any operation on a NUMERIC/DECIMAL type would produce a number with precision larger than 38.

Floating Point Types: 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.2250738585072014E-308

2.2250738585072014E-308

1.7976931348623157E+308

About 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
 -1000

SELECT * 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