Numeric Types

Numeric types consist of integers of 1, 2, 4, or 8 bytes, floating-point numbers of 4 or 8 bytes, and optionally precise decimals.

For details about numeric operators and functions, see Numeric Operation Functions and Operators.

DataArts Fabric SQL supports integers, arbitrary precisions, floating point types, and serial integers.

Integer Types

The SMALLINT, INTEGER, and BIGINT types store the entire value (without the decimal part), that is, an integer. Saving a number with a decimal in any of the data types will result in errors.

The type INTEGER is the common choice. Generally, use the SMALLINT type only if you are sure that the value range is within the SMALLINT value range. The storage speed of INTEGER is much faster. BIGINT is used only when the range of INTEGER is not large enough.

**Table 1** Integer types
Column	Description	Storage Space	Range
SMALLINT	Small integer, also called INT2	2 bytes	-32,768 ~ +32,767
INTEGER	Typical choice for integer, also called INT4	4 bytes	-2,147,483,648 ~ +2,147,483,647
BIGINT	Big integer, also called INT8	8 bytes	-9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807

Examples:

Create a table containing data of the SMALLINT, INTEGER, and BIGINT types.

     
          CREATE TABLE int_type_t1 
(
    a SMALLINT, 
    b SMALLINT,
    c INTEGER,
    d BIGINT
) STORE AS orc;

Insert data.

    
         INSERT INTO int_type_t1 VALUES(100, 10, 1000, 10000);

View data.

    
         SELECT * FROM int_type_t1;
  a  | b  |  c   |   d   
-----+----+------+-------
 100 | 10 | 1000 | 10000
(1 row)

Arbitrary Precision Types

To configure a numeric or decimal column, you are advised to specify both the maximum precision (p) and the maximum scale (s) of the column.

If the precision or scale of a value is greater than the declared scale of the column, the system will round the value to the specified number of fractional digits. Then, if the number of digits to the left of the decimal point exceeds the declared precision minus the declared scale, an error will be reported.

**Table 2** Any-precision types
Column	Description	Storage Space	Range
NUMERIC[(p[,s])], DECIMAL[(p[,s])]	The value range of p (precision) is [1,1000], and the value range of s (standard) is [0,p].	The precision is specified by users. Every four decimal digits occupy two bytes, and an extra eight-byte overhead is added to the entire data.	Up to 131,072 digits before the decimal point; and up to 16,383 digits after the decimal point when no precision is specified

Examples:

Create a table with DECIMAL values.

    
         CREATE TABLE decimal_type_t1 (DT_COL1 DECIMAL(10,4)) STORE AS orc;

Insert data.

    
         INSERT INTO decimal_type_t1 VALUES(123456.122331);
INSERT INTO decimal_type_t1 VALUES(123456.452399);

View data.

    
         SELECT * FROM decimal_type_t1;
   dt_col1
-------------
 123456.1223
 123456.4524
(2 rows)

Floating-Point Types

The floating-point type is an inexact, variable-precision numeric type. This type is an implementation of IEEE Standard 754 for Binary Floating-Point Arithmetic (single and double precision, respectively), to the extent that the underlying processor, OS, and compiler support it.

**Table 3** Floating point types
Column	Description	Storage Space	Range
REAL, FLOAT4	Single precision floating points, inexact	4 bytes	Six bytes of decimal digits
DOUBLE PRECISION, FLOAT8	Double precision floating points, inexact	8 bytes	1E-307~1E+308, 15 bytes of decimal digits
FLOAT[(p)]	Floating points, inexact. The value range of precision (p) is [1,53]. NOTE: p is the precision, indicating the total decimal digits.	4 or 8 bytes	REAL or DOUBLE PRECISION is selected as an internal identifier based on different precision (p). If no precision is specified, DOUBLE PRECISION is used as the internal identifier.
DEC[(p[,s])]	The value range of p (precision) is [1,1000], and the value range of s (standard) is [0,p]. NOTE: p indicates the total digits, and s indicates the decimal digit.	The precision is specified by users. Every four decimal digits occupy two bytes, and an extra eight-byte overhead is added to the entire data.	Up to 131,072 digits before the decimal point; and up to 16,383 digits after the decimal point when no precision is specified
INTEGER[(p[,s])]	The value range of p (precision) is [1, 1000], and the value range of s (standard) is [0, p].	The precision is specified by users. Every four decimal digits occupy two bytes, and an extra eight-byte overhead is added to the entire data.	Up to 131,072 digits before the decimal point; and up to 16,383 digits after the decimal point when no precision is specified

Examples:

Create a table with floating-point values.

    
         CREATE TABLE float_type_t1
(
FT_COL1 REAL,
FT_COL2 FLOAT4,
FT_COL3 DOUBLE PRECISION,
FT_COL4 FLOAT8,
FT_COL5 FLOAT,
FT_COL6 FLOAT(3),
FT_COL7 DEC(10,4),
FT_COL8 INTEGER(6,3)
) STORE AS orc;

Insert data.

    
         INSERT INTO float_type_t1 VALUES (10.01, 10.3655, 123456.1234, 10.3214, 123.1237, 123.1237, 123.124, 125.123456);

View data.

    
         SELECT * FROM float_type_t1;
 
 ft_col1 | ft_col2 |   ft_col3   | ft_col4 | ft_col5  | ft_col6 | ft_col7  | ft_col8 
---------+---------+-------------+---------+----------+---------+----------+---------
   10.01 | 10.3655 | 123456.1234 | 10.3214 | 123.1237 | 123.124 | 123.1240 | 125.123
(1 row)