Base Types

The table below shows all the built-in general-purpose SQL data type names including supported aliases.

NameAliasesDescription
CHARACTERCHARACTER(1), CHAR, CHAR(1)UTF-8 character string of max 1 character
CHARACTER ( length )CHAR( length )UTF-8 character string with length upperbound limit. Warning: NO spaces are padded at the end
CHARACTER VARYING ( length )VARCHAR ( length )UTF-8 character string with length upperbound limit
CHARACTER LARGE OBJECTCLOB, TEXT, STRINGUTF-8 character string with unbounded length
CHARACTER LARGE OBJECT ( length )CLOB ( length ), TEXT ( length ), STRING ( length )UTF-8 character string with length upperbound limit
BINARY LARGE OBJECTBLOBbytes string with unbounded length
BINARY LARGE OBJECT ( length )BLOB ( length )bytes string with length upperbound limit
BOOLEANBOOLlogical boolean (true or false)
TINYINT8 bit signed integer between -127 and 127
SMALLINT16 bit signed integer between -32767 and 32767
INTEGERINT, MEDIUMINT32 bit signed integer between -2147483647 and 2147483647
BIGINT64 bit signed integer between -9223372036854775807 and 9223372036854775807
HUGEINT128 bit signed integer between -2^127 +1 and +2^127 -1 (±170141183460469231731687303715884105727)
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)
DECIMALDEC, NUMERICExact 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
REALFLOAT(24)32 bit signed floating point approximate number
DOUBLE PRECISIONDOUBLE, 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
DATEGregorian calendar date (YYYY-MM-DD) between between -4712-01-01 and 170049-12-31
TIMETIME(0)time of day (HH:MI:SS) between 00:00:00 and 23:59:59
TIME ( Prec )time of day including fractions of a seconds (HH:MI:SS.ffffff). Prec defines the number of fraction digits between 0 and 6
TIME WITH TIME ZONETIME(0) WITH TIME ZONEtime of day with timezone information
TIME ( Prec ) WITH TIME ZONEtime of day including fractions of a seconds with timezone information. Prec defines the number of fraction digits between 0 and 6
TIMESTAMPTIMESTAMP(6)combination of a date and time(6)
TIMESTAMP ( Prec )combination of date and time including fractions of a seconds. Prec defines the number of fraction digits between 0 and 6
TIMESTAMP WITH TIME ZONETIMESTAMP(6) WITH TIME ZONEtimestamp(6) with timezone information
TIMESTAMP ( Prec ) WITH TIME ZONEtimestamp including fractions of a seconds with timezone information. Prec defines the number of fraction digits between 0 and 6
INTERVAL interval_qualifiera months/days/seconds interval such as: INTERVAL DAY TO SECOND. See Temporal Types for all possible interval_qualifier values

All scalar types include a NULL value, 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). Arithmetic expressions that overflow may lead to returning the NULL instead.

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

The numeric and decimal types are represented as fixed length integers, whose decimal point is produced during result rendering.

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

No special attention is given to the value NaN.