Chapter 3Data Types and Subtypes

Data of various types are used to:

Table 3.1Overview of Data Types
NameSizePrecision & LimitsDescription

BIGINT

64 bits

From -263 to (263 - 1)

Signed 64-bit integer. This data type is available in Dialect 3 only

BINARY(n)

n bytes

from 1 to 32,767 bytes

A fixed-length binary data type; synonym for CHAR(n) CHARACTER SET OCTETS Values shorter than the declared length are padded with NUL (0x00) up to the declared length. If the number of characters is not specified, 1 is used by default.

BLOB

Varying

The size of a BLOB segment is limited to 64K. The maximum size of a BLOB field is 4 GB.

A data type of variable size for storing large amounts of data, such as images, text, digital sounds. The blob subtype defines its content. Depending on the page size, BLOBs can exceed 4 GB, but some built-in functions and features may not be able to access data beyond 4 GB.

BOOLEAN

1 byte

false, true, unknown

Boolean data type

CHAR(n), CHARACTER(n)

n characters. Size in bytes depends on the encoding, the number of bytes in a character

from 1 to 32,767 bytes

A fixed-length character data type. Values shorter than the declared length are padded with spaces (0x20) — or NUL (0x00) for character set OCTETS — up to the declared length. If the number of characters is not specified, 1 is used by default.

DATE

4 bytes

From 0001-01-01 AD to 9999-12-31 AD

Date only, no time element

DECFLOAT​(dec_prec)

64 or 128 bits

dec_prec = 16 or 34, defines the number of decimal digits

Decimal floating-point type, IEEE-754 decimal64 or decimal128. If the precision is not specified, 34 is used by default.

DECIMAL​(precision, scale)

Varying (16, 32, 64 or 128 bits)

precision = from 1 to 38, defines the minimum number of digits to store; scale = from 0 to 38, defines the number of digits after the decimal point

A number with a decimal point that has scale digits after the point. scale must be less than or equal to precision. Example: DECIMAL(10,3) contains a number in exactly the following format: ppppppp.sss

DOUBLE PRECISION

64 bits

2.225 * 10-308 to 1.797 * 10308

Double-precision, IEEE-754 binary64, ~15 digits, reliable size depends on the platform

FLOAT

32 bits

1.175 * 10-38 to 3.402 * 1038

Single-precision, IEEE-754 binary32, ~7 digits

FLOAT​(bin_prec)

32 or 64 bits

bin_prec = from 1 to 53, binary precision

Binary precision 1 - 24: synonym for FLOAT (32-bit single precision) 25 - 53: synonym for DOUBLE PRECISION (64-bit double precision)

INTEGER, INT

32 bits

-2,147,483,648 up to 2,147,483,647

Signed 32-bit integer

INT128

128 bits

From -2127 to (2127 - 1)

Signed 128-bit integer

NUMERIC​(precision, scale)

Varying (16, 32, 64 or 128 bits)

precision = from 1 to 38, defines the minimum number of digits to store; scale = from 0 to 38, defines the number of digits after the decimal point

A number with a decimal point that has scale digits after the point. scale must be less than or equal to precision. Example: NUMERIC(10,3) contains a number in exactly the following format: ppppppp.sss

REAL

32 bits

 

Synonym for FLOAT

SMALLINT

16 bits

-32,768 to 32,767

Signed short (word)

TIME [WITHOUT TIME ZONE]

4 bytes

0:00 to 23:59:59.9999

Time of day. It cannot be used to store an interval of time.

TIME WITH TIME ZONE

6 bytes

0:00 to 23:59:59.9999

Time of day with either a time zone offset or named zone. It cannot be used to store an interval of time.

TIMESTAMP [WITHOUT TIME ZONE]

8 bytes

From start of day 0001-01-01 AD to end of day 9999-12-31 AD

Date and time of day

TIMESTAMP WITH TIME ZONE

10 bytes

From start of day 0001-01-01 AD to end of day 9999-12-31 AD

Date and time of day with either a time zone offset or named zone.

VARBINARY(n), BINARY VARYING(n)

n bytes

from 1 to 32,765 bytes

Variable length string type; synonym for VARCHAR(n) CHARACTER SET OCTETS. The total size cannot be larger than (32KB-3). The two leading bytes store the declared length. There is no default size: the n argument is mandatory. Leading and trailing NUL characters are stored, and they are not trimmed, except for those trailing characters that are past the declared length.

VARCHAR(n), CHAR VARYING(n), CHARACTER VARYING(n)

n characters. Size in bytes depends on the encoding, the number of bytes in a character

from 1 to 32,765 bytes

Variable length string type. The total size of characters in bytes cannot be larger than (32KB-3), taking into account their encoding. The two leading bytes store the declared length. There is no default size: the n argument is mandatory. Leading and trailing spaces are stored, and they are not trimmed, except for those trailing characters that are past the declared length.

3.1Integer 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.

3.1.1SMALLINT

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

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

3.1.2INTEGER

The INTEGER — or INT — data type is a 32-bit integer. 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

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

3.1.3BIGINT

BIGINT is a 64-bit integer data type, available only in Dialect 3.

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.

3.1.4INT128

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.

3.1.5Hexadecimal Format for Integer Numbers

Constants of integer types can be specified in a hexadecimal format by means of 1 to 8 digits for INTEGER, 9 to 16 hexadecimal digits for BIGINT, and 10 to 32 hexadecimal digits for INT128. 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.

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.

Examples Using Integer Types

   |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 stored as different BIGINT values.