• define columns in a database table in the CREATE TABLE statement or change columns using ALTER TABLE

• declare local variables in stored procedures, PSQL blocks and triggers and specify parameters in stored procedures

• indirectly specify arguments and return values when declaring external functions (UDFs — user-defined functions)

• provide arguments for the CAST() function when explicitly converting data from one type to another

Table 3.1 Overview of Data Types

Note About Dates

Bear in mind that a time series consisting of dates in past centuries is processed without taking into account the actual historical facts, as though the Gregorian calendar were applicable throughout the entire series.

3.1 Integer Data Types

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

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

SMALLINT Examples

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

INTEGER Example

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 -263 to 263 - 1, or from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

INT128 is a 128-bit integer data type. This type is not defined in the SQL standard.

Numbers of the INT128 type are within the range from -2127 to 2127 - 1.

Starting from Firebird 2.5, constants of integer types can be specified in a hexadecimal format by means of 9 to 16 hexadecimal digits for BIGINT or 1 to 8 digits for INTEGER. Hex representation for writing to SMALLINT is not explicitly supported, but Firebird will transparently convert a hex number to SMALLINT if necessary, provided it falls within the ranges of negative and positive SMALLINT.

Note

The hexadecimal integer literals currently do not support INT128 values.

See

The usage and numerical value ranges of hexadecimal notation are described in more detail in the discussion of number constants in the chapter entitled Common Language Elements.

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 values.