Chapter 3. Data Types and Subtypes

Data of various types are used to:

define columns in a database table in the CREATE TABLE statement or change columns using ALTER TABLE
declare or change a domain using the CREATE DOMAIN or ALTER DOMAIN statements
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

Name	Size	Precision & Limits	Description
`BIGINT`	64 bits	From -2⁶³ to (2⁶³ - 1)	The data type is available in Dialect 3 only
`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 basic structural unit is a segment. The blob subtype defines its content
`BOOLEAN`	8 bits	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. When its data is displayed, trailing spaces are added to the string up to the specified length. Trailing spaces are not stored in the database but are restored to match the defined length when the column is displayed on the client side. Network traffic is reduced by not sending spaces over the LAN. If the number of characters is not specified, 1 is used by default.
`DATE`	32 bits	From 0001-01-01 AD to 9999-12-31 AD	`ISC_DATE`. Date only, no time element
`DECIMAL (precision, scale)`	Varying (16, 32 or 64 bits)	precision = from 1 to 18, defines the least possible number of digits to store; scale = from 0 to 18, 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 * 10³⁰⁸	Double-precision IEEE, ~15 digits, reliable size depends on the platform
`FLOAT`	32 bits	1.175 * 10^-38 to 3.402 * 10³⁸	Single-precision IEEE, ~7 digits
`INTEGER`, `INT`	32 bits	-2,147,483,648 up to 2,147,483,647	Signed long
`NUMERIC (precision, scale)`	Varying (16, 32 or 64 bits)	precision = from 1 to 18, defines the exact number of digits to store; scale = from 0 to 18, 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`
`SMALLINT`	16 bits	-32,768 to 32,767	Signed short (word)
`TIME`	32 bits	0:00 to 23:59:59.9999	`ISC_TIME`. Time of day. It cannot be used to store an interval of time
`TIMESTAMP`	64 bits (2 X 32 bits)	From start of day 0001-01-01 AD to end of day 9999-12-31 AD	Date and time of day
`VARCHAR(n)`, `CHAR VARYING`, `CHARACTER VARYING`	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 trailing 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.

ⓘ

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 and BIGINT data types are used for integers of various precision in Dialect 3. Firebird does not support an unsigned integer data type.

3.1.1. `SMALLINT`

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 -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;

3.1.2. `INTEGER`

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 -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),
  |  ...
  |    PRIMARY KEY (CUST_NO) )

3.1.3. `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.

3.1.4. Hexadecimal Format for Integer Numbers

Starting from Firebird 2.5, constants of the three integer types can be specified in 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.

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

Prev	Up	Next
2.6. Comments	Home	3.2. Floating-Point Data Types