eXtremeSQL Integer Data

eXtremeSQL provides the full range of SQL integer data types: TINYINT, SMALLINT, INT, BIGINT, LONGINT and UNSIGNED.

For an overview see page SQL Data Types

The table in page SQL Data Types shows the correspondence between these SQL types and their underlying eXtremeDB storage types as well as the ranges of values possible for each. Note that the integer type declarations can be expressed with explicit size or implicitly using the standard names: SMALLINT, BIGINT etc. For example the following statements demonstrate equivalent declarations:

 
    create table tiny( i1 int(1), ti tinyint);
     
    select FieldName, FieldTypeName, FieldSize from Metatable 
    where TableName = 'tiny';
    FieldName       FieldTypeName   FieldSize
    ------------------------------------------------------------------------------
    i1      Int1    1
    ti      Int1    1
     
    create table small( i2 int(2), si smallint);
     
    select FieldName, FieldTypeName, FieldSize  from Metatable 
    where TableName = 'small';
    FieldName       FieldTypeName   FieldSize   
    -------------------------------------------------------------------------
    i2       Int2 2   
    si      Int2 2
     
    create table ints( i4 int(4), i integer);
     
    select FieldName, FieldTypeName, FieldSize  from Metatable 
    where TableName = 'ints';
    FieldName       FieldTypeName   FieldSize  
    ------------------------------------------------------------------------    
    i4      Int4 4
    i       Int4 4   
     
    create table big( i8 int(8), bi bigint);
     
    select FieldName, FieldTypeName, FieldSize  from Metatable 
    where TableName = 'big';
    FieldName       FieldTypeName   FieldSize   
    ------------------------------------------------------------------------
    i8      Int8 8
    bi      Int8 8
     
    create table long( i8 int(8), li longint);
     
    select FieldName, FieldTypeName, FieldSize  from Metatable 
    where TableName = 'long';
    FieldName       FieldTypeName   FieldSize   
    ------------------------------------------------------------------------
    i8      Int8 8
    li      Int8 8
     

Unsigned integer values can also be declared of 1, 2, 4 and 8 byte sizes. If no size is specified, the default size is 4 bytes . For example:

     
    create table u( u1 unsigned(1), u2 unsigned(2), u4 unsigned(4), 
            u8 unsigned(8), u unsigned);
     
    select FieldName, FieldTypeName, FieldSize  from Metatable 
    where TableName = 'u';
    FieldName       FieldTypeName   FieldSize     
    ------------------------------------------------------------------------------
    u1      UInt1 1
    u2      UInt2 2
    u4      UInt4 4
    u8      UInt8 8
    u UInt4 4
     

Integer Type Conversion

As explained in page Data Type Conversion, when integer fields are used in expressions, the value of an integer field will be converted to a float or numeric to successfully perform statements.

For example, to evaluate the expression i1+f in the following query, the integer(1) field i1 is promoted to a float value then added to the float field f:

     
    create table n( i1 int(1), f float);
    insert into n(i1,f) values(10,6.78);
     
    select format('%5.2f', i1+f) from n;
    #1
    --------------------------------------
    16.78
            
     

(Note that the format() function is used to format the output which would otherwise show digits to the right of .78 that are beyond the precision of the specified values.)

Also, when performing insert or update operations, eXtremeSQL may coerce values to the appropriate type which can lead to loss of precision. For example:

     
    insert into n(i1) values(16.78);
    insert into n(i1) values(3.141);
     
    select i1 from n;
    i1
    --------------------------------------
    17
            
    3
            
     

Note that the float values 16.78 and 3.141 are first rounded to the nearest integer value before inserting into the integer(1) field i1 with the consequent loss of precision.