Supported Data Types | Database Design | Teradata Vantage - 17.10 - Numeric Data Types - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Design

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
User Guide
Publication ID
B035-1094-171K
Language
English (United States)

Differences Between Exact and Approximate Predefined Numeric Data Types

The ANSI/ISO SQL:2011 standard defines two families of predefined numeric data types.
  • Exact
  • Approximate

An exact predefined numeric data type is one that can represent a value exactly. Exact numeric types are divided between the true integer types, which specify a precision but not a scale, and the fractional types, which can specify both a precision and a scale.

Teradata also supports various forms of the NUMBER data type, which can be used to represent both exact numeric values and floating point numeric values, depending on the syntax used to define the value. By the definitions the ANSI/ISO SQL:2011 standard uses for exact and approximate predefined numeric data types, the NUMBER data type as implemented by Teradata is neither an exact nor an approximate numeric type.

Exact Numeric Data Types

The definition of an exact numeric data type used by the ANSI/ISO SQL:2011 standard states that to be an exact numeric, the type must have a precision and a scale expressed either in base 2 or base 10.

According to the ANSI/ISO SQL:2011 standard, the following types are members of the exact predefined numeric set.

The following types are members of the set of true integers.
  • BIGINT
  • INTEGER
  • SMALLINT
The following types are members of the set of fractional exact numeric types.
  • DECIMAL
  • NUMERIC

Teradata also supports the following exact predefined numeric data type extensions to the ANSI/ISO SQL:2011 standard.

                                Data Type                                 Type Family
BYTEINT true integer
NUMBER(p) fractional exact numeric
You can use the NUMBER type to represent both fixed point and floating point values, depending on the syntax you use to specify the value.

See Teradata Vantage™ - Data Types and Literals, B035-1143 for more information about the various exact numeric data types supported by Teradata.

Approximate Numeric Data Types

The following types are members of the ANSI/ISO SQL:2011 approximate predefined numeric set.
  • DOUBLE PRECISION
  • FLOAT
  • REAL

Although FLOAT, REAL, and DOUBLE PRECISION are distinct types as defined by the ANSI/ISO SQL:2011 standard, Teradata treats them as if they are equivalent to one another. The approximate numeric data types represent floating point numbers.

See Teradata Vantage™ - Data Types and Literals, B035-1143 for more information about the various approximate numeric data types supported by Teradata.

Floating Point NUMBER Types

The requirements of an exact NUMBER include that for any addition, subtraction, multiplication, and division operations, the result must be an exact numeric value.

The NUMBER(p,s) syntax is not exact because the results of the previously mentioned dyadic operations do not produce an exact numeric result value.

Teradata supports the following predefined floating point numeric data type extensions to the ANSI/ISO SQL:2011 standard.

Data Type Reason the Type is Not Exact Type Family
NUMBER(p,s) The result of the dyadic operations +, -, *, and / on NUMBER(p,s) values do not produce an exact numeric value when they operate on the NUMBER(p,s) type. fractional floating point numeric

The exact floating point NUMBER types can be used anywhere an approximate numeric type is used, but NUMBER values are stored as exact values in the same way as the exact NUMBER types and with the same accuracy.

Floating point NUMBER types are not approximate types.

NUMBER NUMBER and NUMBER(*) do not specify a scale.
NUMBER(*)
NUMBER(*,s) NUMBER(*,s) has a precision that cannot be expressed as an integer for either base 2 or base 10 arithmetic.
You can use the NUMBER type to represent both fixed point and floating point values, depending on the syntax you use to specify the value.

See Teradata Vantage™ - Data Types and Literals, B035-1143 for more information about the NUMBER data type.