Numeric data types

• 1: DOUBLE PRECISION (FLOAT)
• 2: INTEGER
• 3: NUMERIC
• 4: Numeric data type overflow
• 5: Numeric data type overflow with SUM, SUM_FLOAT, and AVG

CAST(numeric-expression AS data-type)
numeric-expression::data-type

=> SELECT(FLOAT '123.5')::INT;
 ?column?
----------
      124
(1 row)

1 - DOUBLE PRECISION (FLOAT)

OpenText™ Analytics Database supports the numeric data type DOUBLE PRECISION, which is the IEEE-754 8-byte floating point type, along with most of the usual floating point operations.

Syntax

[ DOUBLE PRECISION | FLOAT | FLOAT(n) | FLOAT8 | REAL ]

Parameters

Double precision is an inexact, variable-precision numeric type. In other words, some values cannot be represented exactly and are stored as approximations. Thus, input and output operations involving double precision might show slight discrepancies.

• All of the DOUBLE PRECISION data types are synonyms for 64-bit IEEE FLOAT.

• The n in FLOAT(n) must be between 1 and 53, inclusive, but a 53-bit fraction is always used. See the IEEE-754 standard for details.

• For exact numeric storage and calculations (money for example), use NUMERIC.

• Floating point calculations depend on the behavior of the underlying processor, operating system, and compiler.

• Comparing two floating-point values for equality might not work as expected.

• While the database treats decimal values as FLOAT internally, if a column is defined as FLOAT then you cannot read decimal values from ORC and Parquet files. In those formats, FLOAT and DECIMAL are different types.

Values

COPY accepts floating-point data in the following format:

• Optional leading white space

• An optional plus ("+") or minus sign ("-")

• A decimal number, a hexadecimal number, an infinity, a NAN, or a null value

Decimal Number

A decimal number consists of a non-empty sequence of decimal digits possibly containing a radix character (decimal point "."), optionally followed by a decimal exponent. A decimal exponent consists of an "E" or "e", followed by an optional plus or minus sign, followed by a non-empty sequence of decimal digits, and indicates multiplication by a power of 10.

Hexadecimal Number

A hexadecimal number consists of a "0x" or "0X" followed by a non-empty sequence of hexadecimal digits possibly containing a radix character, optionally followed by a binary exponent. A binary exponent consists of a "P" or "p", followed by an optional plus or minus sign, followed by a non-empty sequence of decimal digits, and indicates multiplication by a power of 2. At least one of radix character and binary exponent must be present.

Infinity

An infinity is either INF or INFINITY, disregarding case.

NaN (Not A Number)

A NaN is NAN (disregarding case) optionally followed by a sequence of characters enclosed in parentheses. The character string specifies the value of NAN in an implementation-dependent manner. (The database internal representation of NAN is 0xfff8000000000000LL on x86 machines.)

When writing infinity or NAN values as constants in a SQL statement, enclose them in single quotes. For example:

=> UPDATE table SET x = 'Infinity'

A NaN example follows.

=> SELECT CBRT('Nan'); -- cube root
 CBRT 
------
 NaN
(1 row)
=> SELECT 'Nan' > 1.0;
 ?column?
----------
 f
(1 row)

Null Value

The load file format of a null value is user defined, as described in the COPY command. The database internal representation of a null value is 0x7fffffffffffffffLL. The interactive format is controlled by the vsql printing option null. For example:

\pset null '(null)'

The default option is not to print anything.

Rules

• -0 == +0

• 1/0 = Infinity

• 0/0 == Nan

• NaN != anything (even NaN)

To search for NaN column values, use the following predicate:

... WHERE column != column

This is necessary because WHERE column = 'Nan' cannot be true by definition.

Sort order (ascending)

• NaN

• -Inf

• numbers

• +Inf

• NULL

Notes

• NULL appears last (largest) in ascending order.

• All overflows in floats generate +/-infinity or NaN, per the IEEE floating point standard.

2 - INTEGER

A signed 8-byte (64-bit) data type.

Syntax

[ INTEGER | INT | BIGINT | INT8 | SMALLINT | TINYINT ]

Parameters

INT, INTEGER, INT8, SMALLINT, TINYINT, and BIGINT are all synonyms for the same signed 64-bit integer data type. Automatic compression techniques are used to conserve disk space in cases where the full 64 bits are not required.

Notes

• The range of values is –2^63+1 to 2^63-1.

• 2^63 = 9,223,372,036,854,775,808 (19 digits).

• The value –2^63 is reserved to represent NULL.

• NULL appears first (smallest) in ascending order.

• OpenText™ Analytics Database does not have an explicit 4-byte (32-bit integer) or smaller types. The database's encoding and compression automatically eliminate the storage overhead of values that fit in less than 64 bits.

Restrictions

• The JDBC type INTEGER is 4 bytes and is not supported by the database. Use BIGINT instead.

• The database does not support the SQL/JDBC types NUMERIC, SMALLINT, or TINYINT.

• The database does not check for overflow (positive or negative) except in the aggregate function SUM(). If you encounter overflow when using SUM, use SUM_FLOAT(), which converts to floating point.

See also

Data Type Coercion Chart

3 - NUMERIC

Numeric data types store fixed-point numeric data. For example, a value of $123.45 can be stored in a NUMERIC(5,2) field. Note that the first number, the precision, specifies the total number of digits.

Syntax

numeric-type [ ( precision[, scale] ) ]

Parameters

numeric-type

One of the following:

• NUMERIC

• DECIMAL

• NUMBER

• MONEY

precision

An unsigned integer that specifies the total number of significant digits that the data type stores, where precision is ≤ 1024. If omitted, the default precision depends on numeric type that you specify. If you assign a value that exceeds precision, OpenText™ Analytics Database returns an error.

If a data type's precision is ≤ 18, its performance is equivalent to an INTEGER data type, regardless of scale. When possible, OpenText recommends that you use a precision ≤ 18.

scale

An unsigned integer that specifies the maximum number of digits to the right of the decimal point to store. scale must be ≤ precision. If omitted, the default scale depends on numeric type that you specify. If you assign a value with more decimal digits than scale, the scale is rounded to scale digits.

When using ALTER to modify the data type of a numeric column, scale cannot be changed.

Default precision and scale

NUMERIC, DECIMAL, NUMBER, and MONEY differ in their default precision and scale values:

Supported encoding

OpenText™ Analytics Database supports the following encoding for numeric data types:

• Precision ≤ 18: AUTO, BLOCK_DICT, BLOCKDICT_COMP, COMMONDELTA_COMP, DELTAVAL, GCDDELTA, and RLE
  

• Precision > 18: AUTO, BLOCK_DICT, BLOCKDICT_COMP, RLE

For details, see Encoding types.

Numeric versus integer and floating data types

Numeric data types are exact data types that store values of a specified precision and scale, expressed with a number of digits before and after a decimal point. This contrasts with the OpenText™ Analytics Database integer and floating data types:

• DOUBLE PRECISION (FLOAT) supports ~15 digits, variable exponent, and represents numeric values approximately. It can be less precise than NUMERIC data types.

• INTEGER supports ~18 digits, whole numbers only.

The NUMERIC data type is preferred for non-integer constants, because it is always exact. For example:

=> SELECT 1.1 + 2.2 = 3.3;
?column?
----------
 t
(1 row)

=>  SELECT 1.1::float + 2.2::float = 3.3::float;
 ?column?
----------
 f
(1 row)

Numeric operations

Supported numeric operations include the following:

• Basic math: +, -, *, /

• Aggregation: SUM, MIN, MAX, COUNT

• Comparison: <, <=, =, <=>, <>, >, >=

NUMERIC divide operates directly on numeric values, without converting to floating point. The result has at least 18 decimal places and is rounded.

NUMERIC mod (including %) operates directly on numeric values, without converting to floating point. The result has the same scale as the numerator and never needs rounding.

Some complex operations used with numeric data types result in an implicit cast to FLOAT. When using SQRT, STDDEV, transcendental functions such as LOG, and TO_CHAR/TO_NUMBER formatting, the result is always FLOAT.

Examples

The following series of commands creates a table that contains a numeric data type and then performs some mathematical operations on the data:

=> CREATE TABLE num1 (id INTEGER, amount NUMERIC(8,2));

Insert some values into the table:

=> INSERT INTO num1 VALUES (1, 123456.78);

Query the table:

=> SELECT * FROM num1;
  id  |  amount
------+-----------
    1 | 123456.78
(1 row)

The following example returns the NUMERIC column, amount, from table num1:

=> SELECT amount FROM num1;
  amount
-----------
 123456.78
(1 row)

The following syntax adds one (1) to the amount:

=> SELECT amount+1 AS 'amount' FROM num1;
  amount
-----------
 123457.78
(1 row)

The following syntax multiplies the amount column by 2:

=> SELECT amount*2 AS 'amount' FROM num1;
  amount
-----------
 246913.56
(1 row)

The following syntax returns a negative number for the amount column:

=> SELECT -amount FROM num1;
?column?
------------
-123456.78
(1 row)

The following syntax returns the absolute value of the amount argument:

=> SELECT ABS(amount) FROM num1;
ABS
-----------
123456.78
(1 row)

The following syntax casts the NUMERIC amount as a FLOAT data type:

=> SELECT amount::float FROM num1;
  amount
-----------
 123456.78
(1 row)

See also

4 - Numeric data type overflow

OpenText™ Analytics Database does not check for overflow (positive or negative) except in the aggregate function SUM(). If you encounter overflow when using SUM, use SUM_FLOAT() which converts to floating point.

For a detailed discussion of how the database handles overflow when you use the functions SUM, SUM_FLOAT, and AVG with numeric data types, see Numeric data type overflow with SUM, SUM_FLOAT, and AVG. The discussion includes directives for turning off silent numeric overflow and setting precision for numeric data types. Dividing by zero returns an error:

=> SELECT 0/0;
ERROR 3117:  Division by zero

=> SELECT 0.0/0;
ERROR 3117:  Division by zero

=> SELECT 0 // 0;
ERROR 3117:  Division by zero

=> SELECT 200.0/0;
ERROR 3117:  Division by zero

=> SELECT 116.43 // 0;
ERROR 3117:  Division by zero

Dividing zero as a FLOAT by zero returns NaN:

=> SELECT 0.0::float/0;
 ?column?
----------
      NaN
=> SELECT 0.0::float//0;
 ?column?
----------
      NaN

Dividing a non-zero FLOAT by zero returns Infinity:

=> SELECT 2.0::float/0;
 ?column?
----------
Infinity
=> SELECT 200.0::float//0;
?column?
----------
Infinity

Add, subtract, and multiply operations ignore overflow. Sum and average operations use 128-bit arithmetic internally. SUM() reports an error if the final result overflows, suggesting the use of SUM_FLOAT(INT), which converts the 128-bit sum to a FLOAT. For example:

=> CREATE TEMP TABLE t (i INT);
=> INSERT INTO t VALUES (1<<62);
=> INSERT INTO t VALUES (1<<62);
=> INSERT INTO t VALUES (1<<62);
=> INSERT INTO t VALUES (1<<62);
=> INSERT INTO t VALUES (1<<62);
=> SELECT SUM(i) FROM t;
   ERROR: sum() overflowed
   HINT: try sum_float() instead
=> SELECT SUM_FLOAT(i) FROM t;
     SUM_FLOAT
---------------------
 2.30584300921369e+19

5 - Numeric data type overflow with SUM, SUM_FLOAT, and AVG

When you use the SUM, SUM_FLOAT, and AVG functions (aggregate and analytic) to query a numeric column, overflow can occur. The following two configuration parameters determine how OpenText™ Analytics Database responds to the overflow:

• AllowNumericOverflow (Boolean, default 1) allows numeric overflow. The database does not implicitly extend precision of numeric data types.
• NumericSumExtraPrecisionDigits (integer, default 6) determines whether to return an overflow error if a result exceeds the specified precision. This parameter is ignored if AllowNumericOverflow is set to 1 (true).

The database also allows numeric overflow when you use SUM or SUM_FLOAT to query pre-aggregated data. See Impact on Pre-Aggregated Data Projections below.

Default overflow handling

With numeric columns, the database internally works with multiples of 18 digits. If specified precision is less than 18—for example, x(12,0)—The database allows overflow up to and including the first multiple of 18. In some situations, if you sum a column, you can exceed the number of digits the database internally reserves for the result. In this case, the database allows silent overflow.

Turning off silent numeric overflow

You can turn off silent numeric overflow by setting AllowNumericOverflow to 0. In this case, the database checks the value of configuration parameter NumericSumExtraPrecisionDigits. By default, this parameter is set to 6, which means that the database internally adds extra digit places beyond a column's DDL-specified precision. Adding extra precision digits enables the database to consistently return results that overflow the column's precision. However, crossing into the second multiple of 18 internally can adversely impact performance.

For example, if AllowNumericOverflow is set to 0 :

• Column x is defined as x(12,0)and NumericSumExtraPrecisionDigits is set to 6: The database internally stays within the first multiple of 18 digits and no additional performance impact occurs (a).
• Column x is defined as x(2,0)and NumericSumExtraPrecisionDigits is set to 20: The database internally crosses a threshold into the second multiple of 18. In this case, performance is significantly affected (2a). Performance beyond the second multiple of 18 continues to be 2a.
  

Impact on pre-aggregated data projections

The database supports silent numeric overflow for queries that use SUM or SUM_FLOAT on projections with pre-aggregated data such as live aggregate or Top-K projections. To turn off silent numeric overflow for these queries:

1. Set AllowNumericOverflow to 0.

2. Set NumericSumExtraPrecisionDigits to the desired number of implicit digits. Alternatively, use the default setting of 6.

3. Drop and re-create the affected projections.

If you turn off silent numeric overflow, be aware that overflow can sometimes cause rollback or errors:

• Overflow occurs during load operations, such as COPY, MERGE, or INSERT:

  The database aggregates data before loading the projection with data. If overflow occurs while data is aggregated, the database rolls back the load operation.

• Overflow occurs after load operations, while the database sums existing data.

  The database computes the sum of existing data separately from the computation that it does during data load. If the projection selects a column with SUM or SUM_FLOAT and overflow occurs, the database produces an error message.

• Overflow occurs during mergeout.

  The database logs a message during mergeout if overflow occurs while the database computes a final sum during the mergeout operation. If an error occurs, the database marks the projection as out of date and disqualifies it from further mergeout operations.