Integer Data Types

SMALLINT
INTEGER
BIGINT

The SMALLINT, INTEGER and BIGINT data types are used for integers of various precision in Dialect 3. Firebird does not support an unsigned integer data type.

SMALLINT

The SMALLINT data type is for compact data storage of integer data for which only a narrow range of possible values is required for storing them. Numbers of the SMALLINT type are within the range from -2¹⁶ to 2¹⁶ - 1, that is, from -32,768 to 32,767.

SMALLINT Examples:

      CREATE DOMAIN DFLAG AS SMALLINT DEFAULT 0 NOT NULL
        CHECK (VALUE=-1 OR VALUE=0 OR VALUE=1);

      CREATE DOMAIN RGB_VALUE AS SMALLINT;

INTEGER

The INTEGER data type is a 4-byte integer. The shorthand name of the data type is INT. Numbers of the INTEGER type are within the range from -2³² to 2³² - 1, that is, from -2,147,483,648 to 2,147,483,647.

INTEGER Example:

CREATE TABLE CUSTOMER (
  CUST_NO INTEGER NOT NULL, 
  CUSTOMER VARCHAR(25) NOT NULL, 
  CONTACT_FIRST VARCHAR(15), 
  CONTACT_LAST VARCHAR(20),
  CONSTRAINT INTEG_60,
  ...
    PRIMARY KEY (CUST_NO) )

BIGINT

BIGINT is an SQL:99-compliant 64-bit integer data type, available only in Dialect 3. If a client uses Dialect 1, the generator value sent by the server is reduced to a 32-bit integer (INTEGER). When Dialect 3 is used for connection, the generator value is of type BIGINT.

Numbers of the BIGINT type are within the range from -2⁶³ to 2⁶³ - 1, or from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

Hexadecimal Format for BIGINT Numbers: Starting from Firebird 2.5, numbers of the BIGINT type can be specified in hexadecimal format by means of 9 to 16 hexadecimal digits. Shorter hexadecimal numbers are interpreted as the INTEGER data type.

BIGINT Examples:

       CREATE TABLE WHOLELOTTARECORDS (
         ID BIGINT NOT NULL PRIMARY KEY,
         DESCRIPTION VARCHAR(32)
        );

       INSERT INTO MYBIGINTS VALUES (
         -236453287458723,
         328832607832,
         22,
         -56786237632476,
         0X6F55A09D42, -- 478177959234
         0X7FFFFFFFFFFFFFFF, -- 9223372036854775807
         0XFFFFFFFFFFFFFFFF, -- -1
         0X80000000, -- -2147483648, an INTEGER
         0X080000000, -- 2147483648, a BIGINT
         0XFFFFFFFF, -- -1, an INTEGER
         0X0FFFFFFFF -- 4294967295, a BIGINT
        );

The hexadecimal INTEGERs in the above example are automatically cast to BIGINT before being inserted into the table. However, this happens after the numerical value is determined, so 0x80000000 (8 digits) and 0x080000000 (9 digits) will be saved as different BIGINT values.