# SQL Numeric Types

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

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.

## 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`, `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`. 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)``````

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:

1. When the `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.

2. Storing a `NUMBER` data type, no matter how small and no matter the scale, will require extra storage space.

### Example

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``````

## 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`