Supported Data Types

Character string

CHAR(M)

A fixed-length string of 1 to 255 characters, for example, CHAR(5).

The length limit is not mandatory. It is set to 1 by default.

'a' or 'aaaaa'

CHAR

CHAR

VARCHAR(M)

A variable-length string of 1 to 255 characters (more than 255 characters for MySQL of a later version), for example, VARCHAR(25).

When creating a field of the VARCHAR type, you must define the length.

'a' or 'aaaaa'

VARCHAR

VARCHAR

Value

DECIMAL(M,D)

Uncompressed floating-point numbers cannot be unsigned. In unpacking decimals, each decimal corresponds to a byte.

Defining the number of display lengths (M) and decimals (D) is required. NUMERIC is the synonym of DECIMAL.

52.36

DECIMAL

When D is 0, it corresponds to BIGINT.

When D is not 0, it corresponds to NUMBERIC.

NUMBERIC

Same as DECIMAL

-

DECIMAL

NUMBERIC

INTEGER

An integer of normal size that can be signed. If the value is signed, it ranges from -2147483648 to 2147483647.

If the value is unsigned, the value ranges from 0 to 4294967295. Up to 11-bit width can be specified.

5236

INT

INTEGER

INTEGER UNSIGNED

Unsigned form of INTEGER

-

BIGINT

INTEGER

INT

Same as INTEGER

5236

INT

INTEGER

INT UNSIGNED

Same as INTEGER UNSIGNED

-

BIGINT

INTEGER

BIGINT

A large integer that can be signed. If the value is signed, it ranges from -9223372036854775808 to 9223372036854775807. If the value is unsigned, the value ranges from 0 to 18446744073709551615. Up to 20-bit width can be specified.

5236

BIGINT

BIGINT

BIGINT UNSIGNED

Unsigned form of BIGINT

-

BIGINT

BIGINT

MEDIUMINT

A medium-sized integer that can be signed. If the value is signed, it ranges from -8388608 to 8388607.

If the value is unsigned, it ranges from 0 to 16777215, and you can specify a maximum of 9-bit width.

-128 to 127

INT

INTEGER

MEDIUMINT UNSIGNED

Unsigned form of MEDIUMINT

-

BIGINT

INTEGER

TINYINT

A very small integer that can be signed. If signed, the value ranges from -128 to 127.

If unsigned, the value ranges from 0 to 255, and you can specify a maximum of 4-bit width.

100

TINYINT

SMALLINT

TINYINT UNSIGNED

Unsigned form of TINYINT

-

TINYINT

SMALLINT

BOOL

The bool of MySQL is tinyint(1).

-128, 127

SMALLINT

BYTEA

SMALLINT

A small integer that can be signed. If the value is signed, it ranges from -32768 to 32767.

If unsigned, the value ranges from 0 to 65535, and you can specify a maximum of 5-bit width.

9999

SMALLINT

SMALLINT

SMALLINT UNSIGNED

Unsigned form of SMALLINT

-

INT

SMALLINT

REAL

Same as DOUBLE

-

DOUBLE

-

FLOAT(M,D)

Unsigned floating-point numbers cannot be used. The display length (M) and number of decimal places (D) can be specified. This is not mandatory, and the default value is 10,2. In the preceding information, 2 indicates the number of decimal places and 10 indicates the total number of digits (including decimal places). The decimal precision can reach 24 floating points.

52.36

FLOAT

FLOAT4

DOUBLE(M,D)

Unsigned double-precision floating-point numbers cannot be used. The display length (M) and number of decimal places (D) can be specified. This is not mandatory.

The default value is 16,4, where 4 is the number of decimal places. The decimal precision can reach 53-digit. REAL is a synonym of DOUBLE.

52.36

DOUBLE

FLOAT8

DOUBLE PRECISION

Similar to DOUBLE

52.3

DOUBLE

FLOAT8

Bit

BIT(M)

Stored bit type value. BIT(M) can store up to M bits of values, and M ranges from 1 to 64.

B'1111100' B'1100'

TINYINT

BYTEA

Time and date

DATE

The value is in the YYYY-MM-DD format and ranges from 1000-01-01 to 9999-12-31. For example, December 30, 1973 will be stored as 1973-12-30.

1999-10-01

DATE

TIMESTAMP

TIME

Stores information about the hour, minute, and second.

'09:10:21' or '9:10:21'

Not supported (string)

TIME

DATETIME

The date and time are in the YYYY-MM-DD HH:MM:SS format and range from 1000-01-01 00:00:00 to 9999-12-31 23:59:59. For example, 3:30 p.m. on December 30, 1973 will be stored as 1973-12-30 15:30:00.

'1973-12-30 15:30:00'

TIMESTAMP

TIMESTAMP

TIMESTAMP

Timestamp type. Timestamp between midnight on January 1, 1970 and a time point in 2037. Similar to the DATETIME format (YYYYMMDDHHMMSS), except that no hyphen is required. For example, 3:30 p.m. December 30, 1973 will be stored as 19731230153000.

19731230153000

TIMESTAMP

TIMESTAMP

YEAR(M)

The year is stored in 2-digit or 4-digit number format. If the length is specified as 2 (for example, YEAR(2)), the year ranges from 1970 to 2069 (70 to 69). If the length is specified as 4, the year ranges from 1901 to 2155. The default length is 4.

2000

Not supported (string)

Not supported

Multimedia (binary)

BINARY(M)

The number of bytes is M. The length of a variable-length binary string ranges from 0 to M. M is the value length plus 1.

0x2A3B4058 (binary data)

Not supported

BYTEA

VARBINARY(M)

The number of bytes is M. A fixed binary string with a length of 0 to M.

0x2A3B4059 (binary data)

Not supported

BYTEA

TEXT

The maximum length of the field is 65535 characters. TEXT is a "binary large object" and is used to store large binary data, such as images or other types of files.

0x5236 (binary data)

Not supported

Not supported

TINYTEXT

A binary string of 0 to 255 bytes in short text

-

-

Not supported

MEDIUMTEXT

A binary string of 0 to 167772154 bytes in medium-length text

-

-

Not supported

LONGTEXT

A binary string of 0 to 4294967295 bytes in large-length text

-

-

Not supported

BLOB

The maximum length of the field is 65535 characters. BLOB is a "binary large object" and is used to store large binary data, such as images or other types of files. BLOB is case-sensitive.

0x5236 (binary data)

Not supported

Not supported

TINYBLOB

A binary string of 0 to 255 bytes in short text

-

Not supported

Not supported

MEDIUMBLOB

A binary string of 0 to 167772154 bytes in medium-length text

-

Not supported

Not supported

LONGBLOB

A binary string of 0 to 4294967295 bytes in large-length text

0x5236 (binary data)

Not supported

Not supported

Special type

SET

SET is a string object that can have no or multiple values. The values come from the allowed column of values specified when the table is created. When specifying the SET column values that contain multiple SET members, separate the members with commas (,). The SET member value cannot contain commas (,).

-

-

Not supported

JSON

-

-

Not supported

Not supported (TEXT)

ENUM

When an ENUM is defined, a list of its values is created, which are the items that must be used for selection (or NULL). For example, if you want a field to contain "A", "B", or "C", you can define an ENUM ("A", "B", or "C"). Only these values (or NULL) can be used to fill in the field.

-

Not supported

Not supported

Character string

char

Fixed-length character string, which is padded with spaces to reach the maximum length.

CHAR

CHAR

nchar

Fixed-length character string contains data in Unicode format.

CHAR

CHAR

varchar2

Synonym of VARCHAR. It is a variable-length string, unlike the CHAR type, which does not pad the field or variable to reach its maximum length with spaces.

VARCHAR

VARCHAR

nvarchar2

Variable-length character string contains data in Unicode format.

VARCHAR

VARCHAR

Value

number

Stores numbers with a precision of up to 38 digits.

DECIMAL

NUMERIC

binary_float

2-bit single-precision floating point number

FLOAT

FLOAT8

binary_double

64-bit double-precision floating point number

DOUBLE

FLOAT8

long

A maximum of 2 GB character data can be stored.

Not supported

Not supported

Time and date

date

7-byte date/time data type, including seven attributes: century, year in the century, month, day in the month, hour, minute, and second.

DATE

TIMESTAMP

timestamp

7-byte or 11-byte fixed-width date/time data type that contains decimals (seconds)

TIMESTAMP

TIMESTAMP

timestamp with time zone

3-byte timestamp, which supports the time zone.

TIMESTAMP

TIME WITH TIME ZONE

timestamp with local time zone

7-byte or 11-byte fixed-width date/time data type. Time zone conversion occurs when data is inserted or read.

TIMESTAMP

Not supported (TEXT)

interval year to month

5-byte fixed-width data type, which is used to store a time segment.

Not supported

Not supported (TEXT)

interval day to second

11-byte fixed-width data type, which is used to store a time segment. The time segment is stored in days/hours/minutes/seconds. The value can also contain nine decimal places (seconds).

Not supported

Not supported (TEXT)

Multimedia (binary)

raw

A variable-length binary data type. Character set conversion is not performed for data stored in this data type.

Not supported

Not supported

long raw

Stores up to 2 GB binary information.

Not supported

Not supported

blob

A maximum of 4 GB data can be stored.

Not supported

Not supported

clob

In Oracle 10g and later versions, a maximum of (4 GB) x (database block size) bytes of data can be stored. CLOB contains the information for which character set conversion is to be performed. This data type is ideal for storing plain text information.

String

Not supported

nclob

This type can store a maximum of 4 GB data. When the character set is converted, this type is affected.

Not supported

Not supported

bfile

An Oracle directory object and a file name can be stored in the database column, and the file can be read through the Oracle directory object and file name.

Not supported

Not supported

Others

rowid

It is the address of a row in the database table. It is 10 bytes long.

Not supported

Not supported

urowid

It is a common row ID and does not have a fixed rowid table.

Not supported

Not supported

String data type

char

Fixed-length character string, which is padded with spaces to reach the maximum length.

CHAR

CHAR

CHAR

nchar

Fixed-length character string contains data in Unicode format.

CHAR

CHAR

CHAR

varchar

A variable-length string consists of 1 to 255 characters (more than 255 characters for MySQL of a later version). Example: VARCHAR(25). When creating a field of the VARCHAR type, you must define the length.

VARCHAR

VARCHAR

VARCHAR

nvarchar

Stores variable-length Unicode character data, similar to varchar.

VARCHAR

VARCHAR

VARCHAR

Numeric data type

int

int is stored in four bytes, where one binary bit represents a sign bit, and the other 31 binary bits represent a length and a size, and may represent all integers ranging from –2³¹ to 2³¹ – 1.

INT

INTEGER

INT

bigint

bigint is stored in eight bytes, where one binary bit represents a sign bit, and the other 63 binary bits represent a length and a size, and may represent all integers ranging from –2⁶³ to 2⁶³ – 1.

BIGINT

BIGINT

NUMBER

smallint

Data of the smallint type occupies two bytes of storage space. One binary bit indicates a positive or negative sign of an integer value, and the other 15 binary bits indicate a length and a size, and may represent all integers ranging from –2¹⁵ to 2¹⁵.

SMALLINT

SMALLINT

NUMBER

tinyint

Tinyint data occupies one byte of storage space and can represent all integers ranging from 0 to 255.

TINYINT

TINYINT

NUMBER

real

The value can be a positive or negative decimal number.

DOUBLE

FLOAT4

NUMBER

float

The number of digits (in scientific notation) of the mantissa of a float value, which determines the precision and storage size

FLOAT

FLOAT8

binary_float

decimal

Numeric data type with fixed precision and scale

DECIMAL

NUMERIC

NUMBER

numeric

Stores zero, positive, and negative fixed point numbers.

DECIMAL

NUMERIC

NUMBER

Date and time data type

date

Stores date data represented by strings.

DATE

TIMESTAMP

DATE

time

Time of a day, which is recorded in the form of a character string.

Not supported (string)

TIME

Not supported

datetime

Stores time and date data.

TIMESTAMP

TIMESTAMP

Not supported

datetime2

Extended type of datetime, which has a larger data range. By default, the minimum precision is the highest, and the user-defined precision is optional.

TIMESTAMP

TIMESTAMP

Not supported

smalldatetime

The smalldatetime type is similar to the datetime type. The difference is that the smalldatetime type stores data from January 1, 1900 to June 6, 2079. When the date and time precision is low, the smalldatetime type can be used. Data of this type occupies 4-byte storage space.

TIMESTAMP

TIMESTAMP

Not supported

datetimeoffset

A time that uses the 24-hour clock and combined with date and the time zone.

Not supported (string)

TIMESTAMP

Not supported

Multimedia data types

(binary)

text

Stores text data.

Not supported (string)

Not supported (string)

Not supported

netxt

The function of this type is the same as that of the text type. It is non-Unicode data with variable length.

Not supported (string)

Not supported (string)

Not supported

image

Variable-length binary data used to store pictures, catalog pictures, or paintings.

Not supported (string)

Not supported (string)

Not supported

binary

Binary data with a fixed length of n bytes, where n ranges from 1 to 8,000.

Not supported (string)

Not supported (string)

Not supported

varbinary

Variable-length binary data

Not supported (string)

Not supported (string)

Not supported

Currency data type

money

Stores currency values.

Not supported (string)

Not supported (string)

Not supported

smallmoney

Similar to the money type, a currency symbol is prefixed to the input data. For example, the currency symbol of USD is $.

Not supported (string)

Not supported (string)

Not supported

Data type

bit

Bit data type. The value is 0 or 1. The length is 1 byte. A bit value is often used as a logical value to determine whether it is true(1) or false(0). If a non-zero value is entered, the system replaces it with 1.

Not supported

Not supported

Not supported

Other data types

rowversion

Each piece of data has a counter. The value of the counter increases when an insert or update operation is performed on a table that contains the rowversion column in the database.

Not supported

Not supported

Not supported

uniqueidentifier

A 16-byte globally unique identifier (GUID) is a unique number generated by the SQL Server based on the network adapter address and host CPU clock. Each GUID is a hexadecimal number ranging from 0 to 9 or a to f.

Not supported

Not supported

Not supported

cursor

Cursor data type

Not supported

Not supported

Not supported

sql_variant

Stores any valid SQL Server data except the text, image, and timestamp data, which facilitates the development of the SQL Server.

Not supported

Not supported

Not supported

table

Stores the result set after a table or view is processed.

Not supported

Not supported

Not supported

xml

Data type of the XML data. XML instances can be stored in columns or variables of the XML type. The stored XML instance size cannot exceed 2 GB.

Not supported

Not supported

Not supported

Character

char

Fixed-length string, which is padded to a specified length with spaces on the right.

CHAR

CHAR

Not supported (string)

varchar

Variable-length string. Fields or variables are not padded to the maximum length with spaces.

CARCHAR

CARCHAR

Not supported (string)

Value

smallint

The extension name int2 is stored in two bytes and ranges from –32768 to 32767.

SMALLINT

SMALLINT

SMALLINT

int

The extension name int4 is stored in four bytes and ranges from –2147483648 to 2147483647.

INTEGER

INT

INT

bigint

The extension name int8 is stored in eight bytes and ranges from –9223372036854775808 to 9223372036854775807.

BIGINT

BIGINT

BIGINT

decimal(p,s)

The precision p represents the number of valid digits stored in the value, and the scale s represents the number of digits after the decimal point that can be stored. The maximum value of p is 1000.

DECIMAL(P,S)

DECIMAL(P,S)

DECIMAL(P,S)

float

4-byte or 8-byte storage. float(n): For the single precision, the value of n ranges from 1 to 24, the number of valid precision digits is 6, and the length is four bytes. For the double precision, the value of n ranges from 25 to 53, the number of valid precision digits is 15, and the length is 8 bytes.

FLOAT/DOUBLE

FLOAT/DOUBLE

FLOAT/DOUBLE

smallserial

Sequence data type, which is stored in smallint format

SMALLINT

SMALLINT

SMALLINT

serial

Sequence data type, which is stored in int format

INTEGER

INT

INT

bigserial

Sequence data type, which is stored in bigint format

BIGINT

BIGINT

BIGINT

Time and date

date

Stores the date.

DATE

DATE

DATE

timestamp

Stores date and time data without time zones.

TIMESTAMP

TIMESTAMP

Not supported (string)

timestamptz

Stores the date and time, including the time zone.

TIMESTAMP

TIMESTAMPZ

Not supported (string)

time

Time within one day, excluding the time zone

Not supported (string)

TIME

Not supported (string)

timez

Time within one day, including the time zone

Not supported (string)

TIMEZ

Not supported (string)

interval

Time interval

Not supported (string)

Not supported (string)

Not supported (string)

Bit string

bit

Fixed-length string, for example, b'000101'

Not supported (string)

Not supported (string)

Not supported (string)

varbit

Variable-length string, for example, b'101'

Not supported (string)

Not supported (string)

Not supported (string)

Currency type

money

The value is stored in eight bytes and ranges from –922337203685477.5808 to 922337203685477.5807.

DOUBLE

MONEY

DECIMAL(P,S)

Boolean

boolean

The value is stored in one byte and can be 1, 0, or NULL.

BOOLEAN

BOOLEAN

BOOLEAN

Text type

text

Variable-length text without a length limit

Not supported (string)

Not supported (string)

Not supported (string)

Character

char

Fixed-length string, which is padded to a specified length with spaces on the right.

varchar

Variable-length string. Fields or variables are not padded to the maximum length with spaces.

Value

double

Stores double-precision floating-point numbers.

decimal(p,s)

The precision p represents the number of valid digits stored in the value, and the scale s represents the number of digits after the decimal point that can be stored. The maximum value of p is 1000.

numeric

Stores zero, positive, and negative fixed point numbers.

real

Same as double

int

int is stored in four bytes, where one binary bit represents a sign bit, and the other 31 binary bits represent a length and a size, and may represent all integers ranging from –2³¹ to 2³¹ – 1.

bigint

bigint is stored in eight bytes, where one binary bit represents a sign bit, and the other 63 binary bits represent a length and a size, and may represent all integers ranging from –2⁶³ to 2⁶³ – 1.

smallint

Data of the smallint type occupies two bytes of storage space. One binary bit indicates a positive or negative sign of an integer value, and the other 15 binary bits indicate a length and a size, and may represent all integers ranging from –2¹⁵ to 2¹⁵.

tinyint

Tinyint data occupies one byte of storage space and can represent all integers ranging from 0 to 255.

Time and date

date

Stores the date.

timestamp

Stores date and time data without time zones.

time

Time within one day, excluding the time zone

Bit string

bit

Fixed-length string, for example, b'000101'

Boolean

boolean

The value is stored in one byte and can be 1, 0, or NULL.

Text type

text

Variable-length text without a length limit

Character

VARCHAR

Stores specified fixed-length character strings.

'a' or 'aaaaa'

VARCHAR(765)

STRING

BPCHAR

Stores specified variable-length character strings.

'a' or 'aaaaa'

VARCHAR(765)

STRING

Value

NUMERIC

Stores zero, positive, and negative fixed point numbers.

52.36

DECIMAL(10, 0)

DECIMAL(18, 0)

INT

Stores zero, positive, and negative fixed point numbers.

5236

INT

INT

BIGINT

Stores signed integers. Integer part: 19 digits; decimal part: 0 digits

5236

BIGINT

BIGINT

TINYINT

Stores signed integers. Integer part: 3 digits; decimal part: 0 digits

100

SMALLINT

INT

BINARY

Stores fixed-length binary data.

0x2A3B4058

Not supported

FLOAT

VARBINARY

Stores variable-length binary data.

0x2A3B4058

Not supported

BINARY

FLOAT

Stores floating-point numbers with binary precision.

52.36

FLOAT

FLOAT

DOUBLE

Stores double-precision floating-point numbers.

52.3

DOUBLE

DOUBLE

Time and date

DATE

Stores information about the year, month, and day.

'1999-10-01', '1999/10/01', or '1999.10.01'

DATE

DATE

TIME

Stores information about the hour, minute, and second.

'09:10:21' or '9:10:21'

STRING

STRING

TIMESTAMP

Stores information about the year, month, day, hour, minute, and second.

'2002-12-12 09:10:21', '2002-12-12 9:10:21', '2002/12/12 09:10:21', or '2002.12.12 09:10:21'

TIMESTAMP

TIMESTAMP

Multimedia

CLOB

Stores variable-length binary large objects with a maximum length of 2 GB minus 1 byte.

0x5236 (binary data)

STRING

STRING

BLOB

Stores variable-length binary large objects with a maximum length of 2 GB minus 1 byte.

0x5236 (binary data)

Not supported

BINARY

Boolean

BOOLEAN

The value is stored in one byte and can be 1, 0, or NULL.

1

BOOLEAN

BOOLEAN

Character

VARCHAR

Stores specified fixed-length character strings.

NVARCHAR

Variable-length character string contains data in Unicode format.

TEXT

Stores long character strings. The maximum length of a character string is 2 GB minus 1 byte. Long text strings are stored.

Value

BIGINT

Stores signed integers. Integer part: 19 digits; decimal part: 0 digits

TINYINT

Stores signed integers. Integer part: 3 digits; decimal part: 0 digits

SMALLINT

Data of the smallint type occupies two bytes of storage space. One binary bit indicates a positive or negative sign of an integer value, and the other 15 binary bits indicate a length and a size, and may represent all integers ranging from –2¹⁵ to 2¹⁵.

REAL

The value can be a positive or negative decimal number.

DECIMAL

Numeric data type with fixed precision and scale

FLOAT

Stores floating-point numbers with binary precision.

DOUBLE

Stores double-precision floating-point numbers.

Time and date

DATE

Stores information about the year, month, and day.

TIME

Stores information about the hour, minute, and second.

TIMESTAMP

Stores information about the year, month, day, hour, minute, and second.

Multimedia

CLOB

Stores variable-length binary large objects with a maximum length of 2 GB minus 1 byte.

NCLOB

This type can store a maximum of 4 GB data. When the character set is converted, this type is affected.

Boolean

BOOLEAN

The value is stored in one byte and can be 1, 0, or NULL.

Character

CHAR

Stores specified fixed-length character strings.

VARCHAR

Same as CHAR

STRING

Stores long character strings. The maximum length of a character string is 2 GB minus 1 byte. Long text strings are stored.

Value

BIGINT

Stores signed integers. Integer part: 19 digits; decimal part: 0 digits

TINYINT

Stores signed integers. Integer part: 3 digits; decimal part: 0 digits

SMALLINT

Data of the smallint type occupies two bytes of storage space. One binary bit indicates a positive or negative sign of an integer value, and the other 15 binary bits indicate a length and a size, and may represent all integers ranging from –2¹⁵ to 2¹⁵.

INT

Stores signed integers. Integer part: 10 digits; decimal part: 0 digits

DECIMAL

Numeric data type with fixed precision and scale

FLOAT

Stores floating-point numbers with binary precision.

DOUBLE

Stores double-precision floating-point numbers.

Time and date

DATE

Stores information about the year, month, and day.

TIMESTAMP

Stores information about the year, month, day, hour, minute, and second.

Boolean

BOOLEAN

The value is stored in one byte and can be 1, 0, or NULL.

Character

keyword

Stores strings.

"keyword"

String

text

Stores long character strings. The maximum length of a character string is 2 GB minus 1 byte. Long text strings are stored.

"long string"

TEXT

string

Stores long character strings. The maximum length of a character string is 2 GB minus 1 byte. Long text strings are stored.

"a string"

String

Integer

short

Stores 16-bit signed integers ranging from –32768 to 32767.

32765

smallInt

integer

Stores 32-bit signed integers ranging from –2³¹ to 2³¹ – 1.

3276566

int

long

Stores 64-bit signed integers ranging from –2⁶³ to 2⁶³ – 1.

3276566666

BIGINT

Value

double

64-bit IEEE 754 double-precision floating-point format

21.333

double

float

32-bit IEEE 754 single-precision floating-point format

21.333

double

Boolean

boolean

The value is stored in one byte and can be 1, 0, or NULL.

1

Boolean

Object

object

A string of flat storage objects

{"users.name":["John","Smith"],

users.age":[26,28],

"users.gender":[1, 2]}

TEXT

Nested

nested

A string of nested storage objects

{"users.name" : "John" ,

"users.age" : 26,

"users.gender" : 1}

{ "users.name" : "Smith",

"users.age" : 28,

"users.gender" : 2}

TEXT

Date

date

A string in the date format

"2018-01-13" or "2018-01-13 12:10:30"

DATE or timeStamp

Special type

ip

A string in the IP address format

"192.168.127.100"

String

Array

string_array

An array of strings

["str","str"]

TEXT

short_array

An array of 16-bit integers

[1,1,1]

TEXT

integer_array

An array of 32-bit integers

[1,1,1]

TEXT

long_array

An array of 64-bit integers

[1,1,1]

TEXT

float_array

An array of 32-bit floating-point numbers

[1.0,1.0,1.0]

TEXT

double_array

An array of 64-bit floating-point numbers

[1.0,1.0,1.0]

TEXT

Value range

completion

A string that is automatically completed

"string"

TEXT

String

CHAR(M)

Range: char[(length)]. A fixed-length string of 1 to 255 characters (1 by default).

VARCHAR(M)

Range: char(length). A variable-length string of 1 to 65,535 characters.

Value

DECIMAL(M,D)

Uncompressed floating-point numbers cannot be unsigned. In unpacking decimals, each decimal corresponds to a byte.

Defining the number of display lengths (M) and decimals (D) is required. NUMERIC is the synonym of DECIMAL.

Value type

TINYINT

Length: 1-byte signed integer

Range: [-128, 127]

SMALLINT

Length: 2-byte signed integer

Range: [-32768, 32767]

INT

Length: 4-byte signed integer

Range: [-2147483648, 2147483647]

BIGINT

Length: 8-byte signed integer

Range: [-9223372036854775808, 9223372036854775807]

LARGEINT

Length: 16-byte signed integer

Range: [-2^127, 2^127-1]

FLOAT

Length: 4-byte floating point number

Range: -3.40E+38 to +3.40E+38

DOUBLE

Length: 8-byte floating point number

Range: -1.79E+308 to +1.79E+308

DECIMAL[M,D]

Decimal type that ensures precision. M indicates the total number of valid digits, and D indicates the maximum number of digits after the decimal point. The range of M is [1,27], and that of D is [1,9]. In addition, M must be greater than or equal to D. The default value is decimal[10,0].

Precision: 1–27

Scale: 0–9

Date

DATE

Range: ['1000-01-01', '9999-12-31']. The default printing format is 'YYYY-MM-DD'.

DATETIME

Range: ['1000-01-01 00:00:00', '9999-12-31 00:00:00']. The default printing format is 'YYYY-MM-DD HH:MM:SS'.

Special type

HLL

HyperLogLog (HLL) is a binary type. It can be used only for aggregation tables, and the aggregation type must be HLL_UNION.

This type is mainly used to pre-aggregate data in non-accurate and fast deduplication scenarios.

HLL columns can be queried or used only using hll_union_agg, hll_cardinality, or hll_hash.

BITMAP

BITMAP is a binary type. It can be used only for aggregation tables, and the aggregation type must be BITMAP_UNION.

This type is mainly used to pre-aggregate data in accurate deduplication scenarios. It can also be used to store user IDs in user profile scenarios.

BITMAP columns can be queried or used only using BITMAP functions.

Character

CHAR

Stores specified fixed-length character strings.

'a' or 'aaaaa'

CHAR

CHAR

CHARACTER

Same as CHAR

'a' or 'aaaaa'

CHAR

CHAR

VARCHAR

Stores specified variable-length character strings.

'a' or 'aaaaa'

VARCHAR

VARCHAR

VARCHAR2

Same as VARCHAR

'a' or 'aaaaa'

VARCHAR

VARCHAR

Value

NUMERIC

Stores zero, positive, and negative fixed point numbers.

52.36

DECIMAL

NUMERIC

DECIMAL

Similar to NUMERIC

52.36

DECIMAL

NUMERIC

DEC

Same as DECIMAL

52.36

DECIMAL

NUMERIC

INTEGER

Stores signed integers. Integer part: 10 digits; decimal part: 0 digits

5236

INT

INTEGER

INT

Same as INTEGER

5236

INT

INTEGER

BIGINT

Stores signed integers. Integer part: 19 digits; decimal part: 0 digits

5236

BIGINT

BIGINT

TINYINT

Stores signed integers. Integer part: 3 digits; decimal part: 0 digits

100

TINYINT

SMALLINT

SMALLINT

Stores signed integers. Integer part: 5 digits; decimal part: 0 digits

9999

SMALLINT

SMALLINT

BYTE

Similar to TINYINT. Integer part: 3 digits; decimal part: 0 digits

100

TINYINT

SMALLINT

BINARY

Stores fixed-length binary data.

0x2A3B4058

BINARY (NULL)

BYTEA (NULL)

VARBINARY

Stores variable-length binary data.

0x2A3B4058

BINARY (NULL)

BYTEA (NULL)

FLOAT

Stores floating-point numbers with binary precision.

52.36

FLOAT

FLOAT8

DOUBLE

Similar to FLOAT

52.36

DOUBLE

FLOAT8

REAL

Stores binary floating-point numbers.

52.3

FLOAT

FLOAT4

DOUBLE PRECISION

Stores double-precision floating-point numbers.

52.3

DOUBLE

FLOAT8

Bit string

BIT

Stores 1, 0, or NULL.

1, 0, or NULL

TINYINT(1 0 NULL)

BOOLEAN (true false NULL)

Time and date

DATE

Stores information about the year, month, and day.

'1999-10-01', '1999/10/01', or '1999.10.01'

DATE

TIMESTAMP

TIME

Stores information about the hour, minute, and second.

'09:10:21' or '9:10:21'

Not supported (string)

TIME

TIMESTAMP

Stores information about the year, month, day, hour, minute, and second.

'2002-12-12 09:10:21', '2002-12-12 9:10:21', '2002/12/12 09:10:21', or '2002.12.12 09:10:21'

TIMESTAMP

TIMESTAMP

TIME WITH TIME ZONE

Stores a TIME value with a time zone. Add the time zone information to the end of the TIME type.

'09:10:21 +8:00', '09:10:21+8:00', or '9:10:21+8:00'

Not supported (string)

TIME WITH TIME ZONE

TIMESTAMP WITH TIME ZONE

Stores a TIMESTAMP value with a time zone. Add the time zone information to the end of the TIMESTAMP type.

'2002-12-12 09:10:21 +8:00', '2002-12-12 9:10:21 +8:00', '2002/12/12 09:10:21 +8:00', or '2002.12.12 09:10:21 +8:00'

TIMESTAMP

TIMESTAMP WITH TIME ZONE

TIMESTAMP WITH LOCAL TIME ZONE

Stores the TIMESTAMP value of a local time zone. The standard time zone type (TIMESTAMP WITH TIME ZONE) can be converted to the local time zone type.

'2002-12-12 09:10:21 +8:00', '2002-12-12 9:10:21 +8:00', '2002/12/12 09:10:21 +8:00', or '2002.12.12 09:10:21 +8:00'

Not supported (string)

Not supported (TEXT)

DATETIME WITH TIME ZONE

Same as TIMESTAMP WITH TIME ZONE

'2002-12-12 09:10:21 +8:00', '2002-12-12 9:10:21 +8:00', '2002/12/12 09:10:21 +8:00', or '2002.12.12 09:10:21 +8:00'

TIMESTAMP

TIMESTAMP WITH TIME ZONE

INTERVAL YEAR

Interval of years. The leading precision specifies the range of years.

INTERVAL '0015' YEAR

Not supported (string)

Not supported (VARCHAR)

INTERVAL YEAR TO MONTH

Interval of months and years. The leading precision specifies the range of years.

INTERVAL '0015-08' YEAR TO MONTH

Not supported (string)

Not supported (VARCHAR)

INTERVAL MONTH

Interval of months. The leading precision specifies the range of months.

INTERVAL '0015' MONTH

Not supported (string)

Not supported (VARCHAR)

INTERVAL DAY

Interval of days. The leading precision specifies the range of days.

INTERVAL '150' DAY

Not supported (string)

Not supported (VARCHAR)

INTERVAL DAY TO HOUR

Interval of hours and days. The leading precision specifies the range of days.

INTERVAL '9 23' DAY TO HOUR

Not supported (string)

Not supported (VARCHAR)

INTERVAL DAY TO MINUTE

Interval of minutes, hours, and days. The leading precision specifies the range of days.

INTERVAL '09 23:12' DAY TO MINUTE

Not supported (string)

Not supported (VARCHAR)

INTERVAL DAY TO SECOND

Interval of seconds, minutes, hours, and days. The leading precision specifies the range of days.

INTERVAL '09 23:12:01.1' DAY TO SECOND

Not supported (string)

Not supported (VARCHAR)

INTERVAL HOUR

Interval of hours. The leading precision specifies the range of hours.

INTERVAL '150' HOUR

Not supported (string)

Not supported (VARCHAR)

INTERVAL HOUR TO MINUTE

Interval of minutes and hours. The leading precision specifies the range of hours.

INTERVAL '23:12' HOUR TO MINUTE

Not supported (string)

Not supported (VARCHAR)

INTERVAL HOUR TO SECOND

Interval of seconds, minutes, and hours. The leading precision specifies the range of hours.

INTERVAL '23:12:01.1' HOUR TO SECOND

Not supported (string)

Not supported (VARCHAR)

INTERVAL MINUTE

Interval of minutes. The leading precision specifies the range of minutes.

INTERVAL '150' MINUTE

Not supported (string)

Not supported (VARCHAR)

INTERVAL MINUTE TO SECOND

Interval of minutes and seconds. The leading precision specifies the range of minutes.

INTERVAL '12:01.1' MINUTE TO SECOND

Not supported (string)

Not supported (VARCHAR)

INTERVAL SECOND

Interval of seconds. The leading precision specifies the value range of the integer part of the second

INTERVAL '51.1' SECOND

Not supported (string)

Not supported (VARCHAR)

Multimedia

IMAGE

IMAGE specifies the image type in the multimedia information.

An image consists of a pixel lattice with a maximum length of 2 GB minus 1 byte. In addition to storing image data, other binary data can also be stored.

0x2A3B4058 (binary data)

Not supported

Not supported

LONGVARBINARY

Same as IMAGE

0x2A3B4059 (binary data)

Not supported

Not supported

TEXT

Stores the long string type.

The maximum length of a string is 2 GB minus 1 byte.

0x5236 (binary data)

Not supported

Not supported

LONGVARCHAR

Similar to TEXT

0x5236 (binary data)

Not supported

Not supported

BLOB

Stores variable-length binary large objects with a maximum length of 2 GB minus 1 byte.

0x5236 (binary data)

Not supported

Not supported

CLOB

Stores variable-length binary large objects with a maximum length of 2 GB minus 1 byte.

0x5236 (binary data)

Not supported

Not supported

BFILE

Specified the binary files stored in the operating systems.

Files are stored in the operating systems instead of the databases. They can be read only.

-

Not supported

Not supported