The Firebird 5.0 Language Reference | Data Types And Subtypes

SQL Language StructureCommon Language Elements

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, return values and parameters in PSQL modules and UDFs — user-defined functions
Provide arguments for the CAST() function when explicitly converting data from one type to another

Table 1. Overview of Data Types
Name	Size	Precision & Limits	Description
`BIGINT`	64 bits	From -2⁶³ to (2⁶³ - 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, `BLOB`s 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 * 10³⁰⁸	Double-precision, IEEE-754 binary64, ~15 digits, reliable size depends on the platform
`FLOAT`	32 bits	1.175 * 10^-38 to 3.402 * 10³⁸	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 -2¹²⁷ to (2¹²⁷ - 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.

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

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.

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

`INTEGER`

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

Data Types And Subtypes

Data Types and Subtypes

Integer Data Types

SMALLINT

INTEGER

`SMALLINT`

`INTEGER`