Documentation > User Guide > SQL Manual > Data Types > Base Types

Base Types

The tables below list built-in SQL standard data types including supported aliases. The SQL type names are case insensitive, so instead of CHARACTER you may also type character or Character in your SQL statements.

CHARACTER

SQL Name	Aliases	Description
CHARACTER	CHARACTER(1), CHAR, CHAR(1)	UTF-8 character string of max 1 character
CHARACTER ( length )	CHAR( length )	UTF-8 character string with length upperbound limit between 1 and 2147483647. Warning: NO spaces are padded at the end
CHARACTER VARYING ( length )	VARCHAR ( length )	UTF-8 character string with length upperbound limit between 1 and 2147483647
CHARACTER VARYING	VARCHAR	UTF-8 character string with unbounded length
CHARACTER LARGE OBJECT	CLOB, TEXT, STRING	UTF-8 character string with unbounded length
CHARACTER LARGE OBJECT ( length )	CLOB ( length ), TEXT ( length ), STRING ( length )	UTF-8 character string with length upperbound limit between 1 and 2147483647

BOOLEAN

SQL Name	Aliases	Description
BOOLEAN	BOOL	logical boolean (true or false). For convenience we also accept values: 1 and 0 instead of true and false.

INTEGER

SQL Name	Aliases	Description
TINYINT		8 bit signed integer between -127 and 127
SMALLINT		16 bit signed integer between -32767 and 32767
INTEGER	INT, MEDIUMINT	32 bit signed integer between -2147483647 and 2147483647
BIGINT		64 bit signed integer between -9223372036854775807 and 9223372036854775807
HUGEINT		128 bit signed integer between -2^127 +1 and +2^127 -1 (±170141183460469231731687303715884105727)

The integer types align with the storage of 1, 2, 4, 8 and 16 bytes.

Note: HUGEINT is only available on platforms with a C-compiler that supports the __int128 or __int128_t data type (e.g. recent gcc, clang and icc on Linux or macOS). HUGEINT is not available on MS Windows.

DECIMAL

SQL Name	Aliases	Description
DECIMAL	DEC, NUMERIC	Exact signed decimal number with precision 18 and scale 3
DECIMAL ( Prec )	DEC ( Prec ), NUMERIC ( Prec )	Exact signed decimal number with the specified precision Prec and scale 0. Prec must be between 1 and 18 (or 38 when HUGEINT is also supported)
DECIMAL ( Prec , Scale )	DEC ( Prec , Scale ), NUMERIC ( Prec , Scale )	Exact signed decimal number with specified precision Prec and scale Scale. Prec must be between 1 and 18 (or 38 when HUGEINT is also supported). Scale must be between 0 and Prec

The decimal types are represented as fixed length integers, whose decimal point is produced during result rendering. Depending on the precision value (maximum number of digits), the storage is 1 or 2 or 4 or 8 or 16 bytes.

Decimal precision |  storage
----------------- | --------
        1 or 2    |  1 byte
        3 or 4    |  2 bytes
between 5 and 9   |  4 bytes
between 10 and 18 |  8 bytes
between 19 and 38 | 16 bytes

FLOAT

SQL Name	Aliases	Description
REAL	FLOAT(24)	32 bit signed floating point approximate number
DOUBLE PRECISION	DOUBLE, FLOAT, FLOAT(53)	64 bit signed floating point approximate number
FLOAT ( Prec )		32 or 64 bit signed floating point approximate number with binary (radix 2) precision Prec. Prec must be between 1 and 53. FLOAT(24) is same as REAL, FLOAT(53) is same as DOUBLE PRECISION

The types REAL, FLOAT and DOUBLE map to the underlying implementation system.

Note: No special attention is given to the value NaN. Arithmetic floating point expressions that overflow may lead to returning the NULL instead.

BINARY LARGE OBJECT

SQL Name	Aliases	Description
BINARY LARGE OBJECT	BLOB	bytes string with unbounded length
BINARY LARGE OBJECT ( length )	BLOB ( length )	bytes string with length upperbound limit between 1 and 2147483647

DATE TIME

For DATE, TIME, TIMESTAMP and INTERVAL see Temporal types.

Note: All scalar types include a reserved value for NULL, which is internally represented as a valid domain value. For numerical types, this is the smallest value in the type's domain (i.e., the one omitted in the ranges given above).