Chapter 3. Data Types and Subtypes

Data 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, UDRs — user-defined routines, and UDFs — user-defined functions
Provide the type for Section 8.5.1, “CAST()” or the RETURNING clause of Section 10.1.2, “UNLIST()” 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)	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`. If length n is not specified, 1 is used by default. Values are padded to the declared length with NUL (0x00).
`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. `BLOB`s can exceed 4 GB, but some built-in functions and features cannot access data beyond 4 GB.
`BOOLEAN`	1 byte	false, true, unknown	Boolean data type
`CHAR[(n)], CHARACTER[(n)]`	n characters	from 1 to 32,767 bytes	A fixed-length character data type. If length n is not specified, 1 is used by default. Maximum of length n depends on the character set (e.g. 32,767 for `WIN1252`, 8,191 for `UTF8`). Values are padded to the declared length with spaces (0x20) — or NUL (0x00) for character set OCTETS.
`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 (number of decimal digits)	Decimal floating-point type, IEEE-754 decimal64 or decimal128. If precision dec_prec is not specified, 34 is used by default.
`DECIMAL[(p[,s])], DEC[(p[,s])]`	Varying (32, 64 or 128 bits)	p = from 1 to 38 s = from 0 to p	A number with p digits, with s digits after the decimal point. If precision p is not specified, 9 is used by default. If scale s is not specified, 0 is used by default. s must be less than or equal to p. Example: `DECIMAL(5,3)` contains a number in the following format: `pp.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[(p[,s])]`	Varying (16, 32, 64 or 128 bits)	p = from 1 to 38 s = from 0 to p	A number with p digits, with s digits after the decimal point. If precision p is not specified, 9 is used by default. If scale s is not specified, 0 is used by default. s must be less than or equal to p. Example: `NUMERIC(5,3)` contains a number in the following format: `pp.sss`
`REAL`	32 bits		Synonym for `FLOAT`
`SMALLINT`	16 bits	-32,768 to 32,767	Signed 16-bit integer
`TIME [WITHOUT TIME ZONE]`	4 bytes	0:00 to 23:59:59.9999	Time of day.
`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.
`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`. If length n is not specified, 255 is used by default. Trailing NUL (0x00) are not trimmed, except past the declared length.
`VARCHAR[(n)], CHAR VARYING[(n)], CHARACTER VARYING[(n)]`	n characters	from 1 to 32,765 bytes	Variable length string type. If length n is not specified, 255 is used by default. Maximum of length n depends on the character set (e.g. 32,765 for `WIN1252`, 8,191 for `UTF8`). Trailing spaces — or NUL (0x00) for `OCTETS` — are not trimmed, except past the declared length.

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.

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 — 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) )

3.1.3. `BIGINT`

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

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

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 -2¹²⁷ to 2¹²⁷ - 1.

3.1.5. Hexadecimal 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 MYBIGINTS (
  BIGINT_1 bigint,
  -- BIGINT_2 .. BIGINT_10 left out
  BIGINT_11 bigint
);
 
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.

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