Schema initialization: Data type mappings - Data Transmission Service

Heterogeneous databases support different data types. When Data Transmission Service (DTS) synchronizes data between heterogeneous databases, it converts data types from the source database to compatible ones in the destination database during schema initialization. This topic provides detailed data type mappings to help you evaluate the impact of data synchronization on your business.

Overview

The following topics describe data type mappings for data synchronization between heterogeneous databases.

Data synchronization from a MySQL source
Data synchronization from an Oracle source
Data synchronization from a PostgreSQL source
Data synchronization from an SQL Server source
Data synchronization from a Db2 for LUW source
Data synchronization from a Db2 for i source
Data synchronization from a TiDB source

Synchronize data from MySQL

The following are the data type mappings between a source MySQL database, such as ApsaraDB RDS for MySQL, a self-managed MySQL database, or PolarDB for MySQL, and a destination heterogeneous database, such as AnalyticDB for MySQL and AnalyticDB for PostgreSQL.

Note

If the value range of the data to be synchronized from the source instance exceeds the range supported by DTS, the data written to the destination instance will have reduced accuracy.

Destination instances: AnalyticDB for MySQL and AnalyticDB for PostgreSQL

Note

When synchronizing data to AnalyticDB for MySQL, note the following:

If a source field has the AUTO_INCREMENT attribute and is of the INT type, DTS converts it to the BIGINT type during synchronization.
If tables in a JOIN operation contain both INT and BIGINT fields, we recommend that you consistently use INT or BIGINT at the destination to avoid performance impacts.

Type	Source data type	Value range	AnalyticDB for MySQL type	AnalyticDB for PostgreSQL type
Integer types	BIT[(M)]	1 to 64	VARCHAR	BIT[(M)]
	TINYINT[(M)]	-128 to 127	TINYINT	SMALLINT
	TINYINT[(M)] [UNSIGNED]	0 to 255	SMALLINT	SMALLINT
	SMALLINT[(M)]	-32768 to 32767	SMALLINT	SMALLINT
	SMALLINT[(M)] [UNSIGNED]	0 to 65535	INT	INTEGER
	MEDIUMINT[(M)]	-8388608 to 8388607	INT	INTEGER
	MEDIUMINT[(M)] [UNSIGNED]	0 to 16777215	INT	INTEGER
	INT[(M)]	-2147483648 to 2147483647	INT	INTEGER
	INT[(M)] [UNSIGNED]	0 to 4294967295	BIGINT	BIGINT
	BIGINT[(M)]	-9223372036854775808 to 9223372036854775807	BIGINT	BIGINT
	BIGINT[(M)] [UNSIGNED]	0 to 18446744073709551615	DECIMAL(20,0)	NUMERIC(20)
Decimal types	DECIMAL[(M[,D])]	M: 0 to 65; D: 0 to 30	DECIMAL[(M[,D])]	DECIMAL[(M[,D])]
	FLOAT(p)	1.175494351E-38 to 3.402823466E+38	FLOAT	REAL
	DOUBLE[(M,D)]	2.2250738585072014E-308 to 1.7976931348623157E+308	DOUBLE	DOUBLE PRECISION
Date and time types	DATE	1000-01-01 to 9999-12-31 Note Format: YYYY-MM-DD.	DATE	DATE
	DATETIME[(fsp)]	1000-01-01 00:00:00.000000 to 9999-12-31 23:59:59.999999 Note Format: YYYY-MM-DD hh:mm:ss[.fraction] (UTC).	DATETIME	TIMESTAMP
	TIMESTAMP[(fsp)]	1970-01-01 00:00:01.000000 to 2038-01-19 03:14:07.999999 Note Format: YYYY-MM-DD hh:mm:ss[.fraction] (UTC).	TIMESTAMP	TIMESTAMP WITH TIME ZONE
	TIME[(fsp)]	-838:59:59.000000 to 838:59:59.000000 Note Format: hh:mm:ss[.fraction] (UTC).	TIME	TIMESTAMP WITH TIME ZONE
	YEAR[(4)]	1901 to 2155, or 0000	INT	INTEGER
String types	CHAR[(M)]	0 to 255 characters	VARCHAR	CHAR
	VARCHAR(M)	0 to 65,535 characters	VARCHAR	VARCHAR
	BINARY[(M)]	0 to 255 bytes	VARBINARY	BYTEA
	VARBINARY(M)	0 to 65,535 bytes	VARBINARY	BYTEA
	TINYBLOB	255 (2^8 - 1) bytes	VARBINARY	BYTEA
	TINYTEXT	255 (2^8 - 1) characters	VARCHAR	TEXT
	BLOB	65,535 (2^16 - 1) bytes	VARBINARY	BYTEA
	TEXT	65,535 (2^16 - 1) characters	VARCHAR	TEXT
	MEDIUMBLOB	16,777,215 (2^24 - 1) bytes	VARBINARY	BYTEA
	MEDIUMTEXT	16,777,215 (2^24 - 1) characters	VARCHAR	TEXT
	LONGBLOB	4,294,967,295 or 4 GB (2^32 - 1) bytes	VARBINARY	BYTEA
	LONGTEXT	4,294,967,295 or 4 GB (2^32 - 1) characters	VARCHAR	TEXT
	ENUM('value1','value2',...)	Up to 65,535 enumeration values	VARCHAR	VARCHAR(128)
	SET('value1','value2',...)	Up to 64 members	VARCHAR	VARCHAR(128)
Spatial types	GEOMETRY	A value of any geometry type	VARBINARY	POLYGON
	POINT	N/A	VARBINARY	POINT
	LINESTRING	N/A	VARBINARY	PATH
	POLYGON	N/A	VARBINARY	POLYGON
	MULTIPOINT	N/A	VARBINARY	POLYGON
	MULTILINESTRING	N/A	VARBINARY	PATH
	MULTIPOLYGON	N/A	VARBINARY	POLYGON
	GEOMETRYCOLLECTION	A collection of values of any geometry type	VARBINARY	POLYGON
JSON type	JSON	N/A	JSON	JSON

Destination instances: DataHub and Kafka

Type	Source type	Value range	DataHub type	Kafka type
Integer	`BIT[(M)]`	1 to 64	`BOOLEAN` \| `STRING`	Same as the data types in MySQL and PolarDB for MySQL.
	`TINYINT[(M)]`	-128 to 127	`BIGINT`
	`TINYINT[(M)] [UNSIGNED]`	0 to 255	`BIGINT`
	`SMALLINT[(M)]`	-32768 to 32767	`BIGINT`
	`SMALLINT[(M)] [UNSIGNED]`	0 to 65535	`BIGINT`
	`MEDIUMINT[(M)]`	-8388608 to 8388607	`BIGINT`
	`MEDIUMINT[(M)] [UNSIGNED]`	0 to 16777215	`BIGINT`
	`INT[(M)]`	-2147483648 to 2147483647	`BIGINT`
	`INT[(M)] [UNSIGNED]`	0 to 4294967295	`BIGINT`
	`BIGINT[(M)]`	-9223372036854775808 to 9223372036854775807	`BIGINT`
	`BIGINT[(M)] [UNSIGNED]`	0 to 18446744073709551615	`BIGINT`
Decimal	`DECIMAL[(M[,D])]`	M: 0 to 65; D: 0 to 30	`DECIMAL`
	`FLOAT(p)`	1.175494351E-38 to 3.402823466E+38	`DOUBLE`
	`DOUBLE[(M,D)]`	2.2250738585072014E-308 to 1.7976931348623157E+308	`DOUBLE`
Date and time	`DATE`	1000-01-01 to 9999-12-31 Note Format: `YYYY-MM-DD`.	`TIMESTAMP`
	`DATETIME[(fsp)]`	1000-01-01 00:00:00.000000 to 9999-12-31 23:59:59.999999 Note Format: `YYYY-MM-DD hh:mm:ss[.fraction]` (UTC).	`TIMESTAMP`
	`TIMESTAMP[(fsp)]`	1970-01-01 00:00:01.000000 to 2038-01-19 03:14:07.999999 Note Format: `YYYY-MM-DD hh:mm:ss[.fraction]` (UTC).	`TIMESTAMP`
	`TIME[(fsp)]`	-838:59:59.000000 to 838:59:59.000000 Note Format: `hh:mm:ss[.fraction]` (UTC).	`STRING`
	`YEAR[(4)]`	1901 to 2155, or 0000	`STRING`
String	`CHAR[(M)]`	0 to 255 characters	`STRING`
	`VARCHAR(M)`	0 to 65,535 characters	`STRING`
	`BINARY[(M)]`	0 to 255 bytes	`STRING`
	`VARBINARY(M)`	0 to 65,535 bytes	`STRING`
	`TINYBLOB`	255 (2^8 - 1) bytes	`STRING`
	`TINYTEXT`	255 (2^8 - 1) characters	`STRING`
	`BLOB`	65,535 (2^16 - 1) bytes	`STRING`
	`TEXT`	65,535 (2^16 - 1) characters	`STRING`
	`MEDIUMBLOB`	16,777,215 (2^24 - 1) bytes	`STRING`
	`MEDIUMTEXT`	16,777,215 (2^24 - 1) characters	`STRING`
	`LONGBLOB`	4,294,967,295 (4 GB) (2^32 - 1) bytes	`STRING`
	`LONGTEXT`	4,294,967,295 (4 GB) (2^32 - 1) characters	`STRING`
	`ENUM('value1','value2',...)`	An `ENUM` column can have up to 65,535 distinct members.	`STRING`
	`SET('value1','value2',...)`	A `SET` column can have up to 64 distinct members.	`STRING`
Spatial	`GEOMETRY`	Any geometry type.	`STRING`
	`POINT`	N/A	`STRING`
	`LINESTRING`	N/A	`STRING`
	`POLYGON`	N/A	`STRING`
	`MULTIPOINT`	N/A	`STRING`
	`MULTILINESTRING`	N/A	`STRING`
	`MULTIPOLYGON`	N/A	`STRING`
	`GEOMETRYCOLLECTION`	A collection of any geometry types.	`STRING`
JSON	`JSON`	N/A	`STRING`

Destination databases: MaxCompute, Elasticsearch, and ClickHouse

Type	Source type	Value range	MaxCompute	Elasticsearch	ClickHouse
Integer	BIT[(M)]	1 to 64	BOOLEAN \| STRING	BOOLEAN \| LONG Note If the value is 1 byte, we recommend using the BOOLEAN data type in Elasticsearch.	UInt8
	TINYINT[(M)]	-128 to 127	BIGINT	SHORT	Int8
	TINYINT[(M)] [UNSIGNED]	0 to 255	BIGINT	INTEGER	UInt8
	SMALLINT[(M)]	-32768 to 32767	BIGINT	SHORT	Int16
	SMALLINT[(M)] [UNSIGNED]	0 to 65535	BIGINT	INTEGER	UInt16
	MEDIUMINT[(M)]	-8388608 to 8388607	BIGINT	INTEGER	Int32
	MEDIUMINT[(M)] [UNSIGNED]	0 to 16777215	BIGINT	INTEGER	Int32
	INT[(M)]	-2147483648 to 2147483647	BIGINT	INTEGER	Int32
	INT[(M)] [UNSIGNED]	0 to 4294967295	BIGINT	LONG	UInt32
	BIGINT[(M)]	-9223372036854775808 to 9223372036854775807	BIGINT	LONG	Int64
	BIGINT[(M)] [UNSIGNED]	0 to 18446744073709551615	BIGINT	LONG	UInt64
Decimal	DECIMAL[(M[,D])]	M: 0 to 65; D: 0 to 30	DOUBLE	DOUBLE Note If a DECIMAL value contains a decimal point, we recommend using the TEXT data type in Elasticsearch to ensure data consistency.	DECIMAL
	FLOAT(p)	1.175494351E-38 to 3.402823466E+38	DOUBLE	FLOAT	Float32
	DOUBLE[(M,D)]	2.2250738585072014E-308 to 1.7976931348623157E+308	DOUBLE	DOUBLE	Float64
Date and time	DATE	1000-01-01 to 9999-12-31 Note The format is YYYY-MM-DD.	DATETIME	DATE Note The format is YYYY-MM-DD. For more information, see date format.	DATE32 Note The value range of the DATE data type in ClickHouse is smaller than that in MySQL. If you use the DATE data type in ClickHouse, write operations may fail.
	DATETIME[(fsp)]	1000-01-01 00:00:00.000000 to 9999-12-31 23:59:59.999999 Note The format is YYYY-MM-DD hh:mm:ss[.fraction] (UTC).	DATETIME	DATE Note The DATE format is yyyy-MM-dd'T'HH:mm:ss (UTC). For microsecond precision, the format is yyyy-MM-dd'T'HH:mm:ss.S. For more information, see date format.	DATETIME64 Note The value range of the DATETIME data type in ClickHouse is smaller than that in MySQL. If you use the DATETIME data type in ClickHouse, write operations may fail.
	TIMESTAMP[(fsp)]	1970-01-01 00:00:01.000000 to 2038-01-19 03:14:07.999999 Note The format is YYYY-MM-DD hh:mm:ss[.fraction] (UTC).	DATETIME	DATE Note The DATE format is yyyy-MM-dd'T'HH:mm:ss (UTC). For microsecond precision, the format is yyyy-MM-dd'T'HH:mm:ss.S. For more information, see date format.	DATETIME Note Time zone information is not included.
	TIME[(fsp)]	-838:59:59.000000 to 838:59:59.000000 Note The format is hh:mm:ss[.fraction] (UTC).	STRING	DATE Note For more information, see date format.	STRING
	YEAR[(4)]	1901 to 2155, or 0000	STRING	DATE Note The DATE format is yyyy. For more information, see date format.	Int16
String	CHAR[(M)]	0 to 255 characters	STRING	KEYWORD	STRING
	VARCHAR(M)	0 to 65,535 characters	STRING	If the string is 255 characters or shorter: KEYWORD If the string is longer than 255 characters: TEXT	STRING
	BINARY[(M)]	0 to 255 bytes	STRING	BINARY	STRING
	VARBINARY(M)	0 to 65,535 bytes	STRING	BINARY	STRING
	TINYBLOB	255 (2^8 - 1) bytes	STRING	BINARY	STRING
	TINYTEXT	255 (2^8 - 1) characters	STRING	TEXT	STRING
	BLOB	65,535 (2^16 - 1) bytes	STRING	BINARY	STRING
	TEXT	65,535 (2^16 - 1) characters	STRING	TEXT	STRING
	MEDIUMBLOB	16,777,215 (2^24 - 1) bytes	STRING	BINARY	STRING
	MEDIUMTEXT	16,777,215 (2^24 - 1) characters	STRING	TEXT	STRING
	LONGBLOB	4,294,967,295 or 4 GB (2^32 - 1) bytes	STRING	BINARY	STRING
	LONGTEXT	4,294,967,295 or 4 GB (2^32 - 1) characters	STRING	TEXT	STRING
	ENUM('value1','value2',...)	Up to 65,535 enumerated values	STRING	KEYWORD	ENUM
	SET('value1','value2',...)	Up to 64 members	STRING	KEYWORD	STRING
Spatial type	GEOMETRY	A value of any geometry type	STRING	GEO_SHAPE	STRING
	POINT	N/A	STRING	GEO_POINT	STRING
	LINESTRING	N/A	STRING	GEO_SHAPE	STRING
	POLYGON	N/A	STRING	GEO_SHAPE	STRING
	MULTIPOINT	N/A	STRING	GEO_SHAPE Note	STRING
	MULTILINESTRING	N/A	STRING	GEO_SHAPE	STRING
	MULTIPOLYGON	N/A	STRING	GEO_SHAPE	STRING
	GEOMETRYCOLLECTION	A collection of values of any geometry type	STRING	GEO_SHAPE	STRING
JSON type	JSON	N/A	STRING	OBJECT Note	STRING

Destination instance: Tablestore

Source type	Tablestore type
INTEGER	INTEGER
INT	INTEGER
SMALLINT	INTEGER
TINYINT	INTEGER
MEDIUMINT	INTEGER
BIGINT	INTEGER
DECIMAL	DOUBLE
NUMERIC	DOUBLE
FLOAT	DOUBLE
DOUBLE	DOUBLE
BIT	BOOLEAN
DATE	STRING or INTEGER Note Default: STRING.
TIMESTAMP
DATETIME
TIME
YEAR
CHAR	STRING
VARCHAR	STRING
BINARY	BINARY
VARBINARY	BINARY
TINYBLOB/BLOB/MEDIUMBLOB/LONGBLOB	BINARY
TINYTEXT/TEXT/MEDIUMTEXT/LONGTEXT	STRING
ENUM	STRING
SET	STRING
GEOMETRY	STRING
POINT	STRING
LINESTRING	STRING
POLYGON	STRING
MULTIPOINT	STRING
MULTILINESTRING	STRING
MULTIPOLYGON	STRING
GEOMETRYCOLLECTION	STRING
JSON	STRING

Target instance: Lindorm

Source type	Lindorm type
BOOLEAN	BOOLEAN
BIT	BOOLEAN
TINYINT	TINYINT
SMALLINT	SMALLINT
INTEGER	INTEGER
BIGINT	BIGINT
BIGINT UNSIGNED Important Lindorm supports only values within the signed BIGINT range (-9223372036854775808 to 9223372036854775807).	BIGINT
FLOAT	FLOAT
DOUBLE	DOUBLE
DECIMAL	DECIMAL Important The precision must match that of the source field.
CHAR/VARCHAR/TEXT/TINYTEXT/MEDIUMTEXT/LONGTEXT	CHAR/VARCHAR
BINARY	BINARY
BLOB	VARBINARY
VARBINARY	VARBINARY
TIMESTAMP	TIMESTAMP
YEAR	INTEGER
DATE	The mapped data type depends on the target Lindorm instance version. Version 2.8.0.2 or later: DATE Versions earlier than 2.8.0.2: VARCHAR
DATETIME	VARCHAR Important We recommend mapping this to the VARCHAR data type in the target instance. Mapping to the TIMESTAMP data type can cause inconsistencies due to time zone differences. To prevent this, use the ETL feature when you configure the data synchronization task.
TIME	The mapped data type depends on the target Lindorm instance version. Version 2.8.0.2 or later: TIME Note The format is `hh:mm:ss`. This mapping truncates fractional seconds. For example, a source value of `08:11:15.354` becomes `08:11:15` in the target instance. Versions earlier than 2.8.0.2: VARCHAR
JSON	JSON

Target instance: Oracle

Type	Source data type	Value range	Oracle data type
Integer type	BIT[(M)]	1 to 64	NUMBER(2,0)
	TINYINT[(M)]	-128 to 127	NUMBER(3,0)
	TINYINT[(M)] [UNSIGNED]	0 to 255	NUMBER(3,0)
	SMALLINT[(M)]	-32768 to 32767	NUMBER(5,0)
	SMALLINT[(M)] [UNSIGNED]	0 to 65535	NUMBER(5,0)
	MEDIUMINT[(M)]	-8388608 to 8388607	NUMBER(7,0)
	MEDIUMINT[(M)] [UNSIGNED]	0 to 16777215	NUMBER(7,0)
	INT[(M)]	-2147483648 to 2147483647	INT
	INT[(M)] [UNSIGNED]	0 to 4294967295	NUMBER(10,0)
	BIGINT[(M)]	-9223372036854775808 to 9223372036854775807	NUMBER(20,0)
	BIGINT[(M)] [UNSIGNED]	0 to 18446744073709551615	NUMBER(20,0)
Decimal type	DECIMAL[(M[,D])]	M: 0 to 65; D: 0 to 30	NUMBER(M,D) Note If the precision and scale are omitted, the data type defaults to `NUMBER`.
	FLOAT(p)	1.175494351E-38 to 3.402823466E+38	FLOAT
	DOUBLE[(M,D)]	2.2250738585072014E-308 to 1.7976931348623157E+308	DOUBLE
Date and time type	DATE	1000-01-01 to 9999-12-31 Note Format: YYYY-MM-DD.	DATE
	DATETIME[(fsp)]	1000-01-01 00:00:00.000000 to 9999-12-31 23:59:59.999999 Note Format: YYYY-MM-DD hh:mm:ss[.fraction] (UTC).	TIMESTAMP[(fsp)] Note If the precision is omitted, the data type defaults to `TIMESTAMP(0)`.
	TIMESTAMP[(fsp)]	1970-01-01 00:00:01.000000 to 2038-01-19 03:14:07.999999 Note Format: YYYY-MM-DD hh:mm:ss[.fraction] (UTC).	TIMESTAMP[(fsp)] WITH LOCAL TIME ZONE Note If the precision is omitted, the data type defaults to `TIMESTAMP(0) WITH LOCAL TIME ZONE`.
	TIME[(fsp)]	-838:59:59.000000 to 838:59:59.000000 Note Format: hh:mm:ss[.fraction] (UTC).	Not supported
	YEAR[(4)]	1901 to 2155, or 0000	INT
String type	CHAR[(M)]	0 to 255 characters	CHAR[(M)] Note If the length is omitted, the data type defaults to `CHAR(1)`.
	VARCHAR(M)	0 to 65,535 characters	VARCHAR(M)
	VARCHAR(M)	0 to 65,535 characters	VARCHAR(M)
	BINARY[(M)]	0 to 255 bytes	RAW(M) Note If the length is omitted, the data type defaults to `RAW(1)`.
	VARBINARY(M)	0 to 65,535 bytes	RAW(M)
	TINYBLOB	255 (2^8 - 1) bytes	BLOB
	TINYTEXT	255 (2^8 - 1) characters	CLOB
	BLOB	65,535 (2^16 - 1) bytes	BLOB
	TEXT	65,535 (2^16 - 1) characters	CLOB
	MEDIUMBLOB	16,777,215 (2^24 - 1) bytes	BLOB
	MEDIUMTEXT	16,777,215 (2^24 - 1) characters	CLOB
	LONGBLOB	4,294,967,295 (2^32 - 1) bytes	BLOB
	LONGTEXT	4,294,967,295 (2^32 - 1) characters	CLOB
	ENUM('value1','value2',...)	Up to 65,535 enumeration values	Not supported
	SET('value1','value2',...)	Up to 64 members	Not supported
Spatial type	GEOMETRY	A value of any geometry type	Not supported
	POINT	N/A	Not supported
	LINESTRING	N/A	Not supported
	POLYGON	N/A	Not supported
	MULTIPOINT	N/A	Not supported
	MULTILINESTRING	N/A	Not supported
	MULTIPOLYGON	N/A	Not supported
	GEOMETRYCOLLECTION	A collection of values of any geometry type	Not supported
JSON type	JSON	N/A	CLOB

Data synchronization from Oracle

When you synchronize data from a self-managed Oracle database to a heterogeneous destination instance, such as AnalyticDB for PostgreSQL, the data types are mapped as follows.

Note

If the value range of the source data exceeds what DTS supports, the data written to the destination instance may lose precision.

Type	Oracle data type	Value range	MySQL, PolarDB for MySQL, and PolarDB-X	AnalyticDB for PostgreSQL
Numeric type	NUMBER(p,s)	Storage: 1 to 22 bytes. `p` is the precision, with a range of 1 to 38. `s` is the scale, with a range of -84 to 127.	DECIMAL[(p[,s])] Note If precision and scale are not specified, DTS maps the data type to DECIMAL(65,30).	DECIMAL \| TINYINT \| SMALLINT \| INTEGER \| BIGINT
	FLOAT(p)	Storage: 1 to 22 bytes. `p` is the binary precision, with a range of 1 to 126 bits.	DOUBLE	DOUBLE PRECISION
	BINARY_FLOAT	A 4-byte, 32-bit single-precision floating-point number.	FLOAT	DOUBLE PRECISION
	BINARY_DOUBLE	An 8-byte, 64-bit double-precision floating-point number.	DOUBLE	DOUBLE PRECISION
Date and time type	DATE	N/A	DATETIME	TIMESTAMP(0)
	TIMESTAMP [(fractional_seconds_precision)]	N/A	DATETIME[(fractional_seconds_precision)]	TIMESTAMP
	TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE	N/A	DATETIME[(fractional_seconds_precision)]	TIMESTAMP WITH TIME ZONE
	TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE	N/A	DATETIME[(fractional_seconds_precision)]	TIMESTAMP WITH TIME ZONE
	INTERVAL YEAR [(year_precision)] TO MONTH	N/A	Not supported	VARCHAR(32)
	INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]	N/A	Not supported	VARCHAR(32)
String type	CHAR [(size [BYTE \| CHAR])]	Maximum size: 2,000 bytes.	CHAR[(n)] Note If the size is not specified, DTS maps the data type to CHAR(1).	CHAR
	NCHAR[(size)]	Maximum size: 2,000 bytes.	NATIONAL CHAR[(n)] Note If the size is not specified, DTS maps the data type to NATIONAL CHAR(1).	VARCHAR
	VARCHAR(size [BYTE \| CHAR])	If `MAX_STRING_SIZE` is set to `EXTENDED`, the maximum size is 32,767 bytes. If `MAX_STRING_SIZE` is set to `STANDARD`, the maximum size is 4,000 bytes.	VARCHAR(n)	VARCHAR(n)
	VARCHAR2(size [BYTE \| CHAR])	If `MAX_STRING_SIZE` is set to `EXTENDED`, the maximum size is 32,767 bytes. If `MAX_STRING_SIZE` is set to `STANDARD`, the maximum size is 4,000 bytes.	VARCHAR(n)	VARCHAR
	NVARCHAR2(size)	If `MAX_STRING_SIZE` is set to `EXTENDED`, the maximum size is 32,767 bytes. If `MAX_STRING_SIZE` is set to `STANDARD`, the maximum size is 4,000 bytes.	NATIONALVARCHAR[(n)]	VARCHAR
	LONG	Maximum size: 2 GB (2^31 - 1 bytes).	LONGTEXT	TEXT
	RAW(size)	Maximum size: 32,767 bytes or 2,000 bytes.	BINARY(2*size)	BYTEA
	LONG RAW	Maximum size: 2 GB.	LONGBLOB	BYTEA
	CLOB	Maximum size: (4 GB - 1) × DB_BLOCK_SIZE.	LONGTEXT	TEXT
	NCLOB	Maximum size: (4 GB - 1) × DB_BLOCK_SIZE.	LONGTEXT	TEXT
	BLOB	Maximum size: (4 GB - 1) × DB_BLOCK_SIZE.	LONGBLOB	BYTEA
	BFILE	Maximum size: 4 GB.	Not supported	Not supported
JSON type	JSON	Maximum size: 32 MB.	Not supported	JSON
ROWID type	ROWID	64 characters.	Not supported	OID
ROWID type	UROWID	64 characters.	Not supported	Not supported
Spatial type	Customization required	Not supported

Note

If an Oracle data type is not supported by AnalyticDB for PostgreSQL, DTS converts it to BYTEA. If the conversion fails, DTS sets the value of the corresponding field to NULL.

Data synchronization from a PostgreSQL source

This section lists the data type mappings for synchronizing data from a source PostgreSQL instance (including self-managed PostgreSQL and ApsaraDB RDS for PostgreSQL) to a heterogeneous destination instance, such as AnalyticDB for PostgreSQL.

Note

If the value range of data from the source instance exceeds the range supported by DTS, data precision is reduced in the destination instance.

Type	PostgreSQL data type	Value range	Destination data type
integer type	SMALLINT	-32768 to +32767	SMALLINT
	INTEGER	-2147483648 to +2147483647	INTEGER
	BIGINT	-9223372036854775808 to +9223372036854775807	BIGINT
decimal type	DECIMAL	Up to 131,072 digits before the decimal point and 16,383 digits after the decimal point.	DECIMAL
	NUMERIC	Up to 131,072 digits before the decimal point and 16,383 digits after the decimal point.	NUMERIC
	REAL	6 decimal digits of precision	REAL
	DOUBLE PRECISION	15 decimal digits of precision	DOUBLE PRECISION
monetary type	MONEY	-92233720368547758.08 to +92233720368547758.07	MONEY
string type	CHARACTER VARYING(n)	N/A	CHARACTER VARYING(n)
	CHARACTER(n)	N/A	CHARACTER(n)
	TEXT	N/A	TEXT
	CHAR	Default length: 1 byte	CHAR
	NAME	Maximum length: 64 bytes	NAME
text search type	TSQUERY	A text query.	TEXT
text search type	TSVECTOR	A document optimized for text search.	TEXT
binary data type	BYTEA	1 or 4 bytes plus the actual binary string.	BYTEA
date and time type	TIMESTAMP [ (p) ] [ WITHOUT TIME ZONE ]	Date and time without a time zone. Storage size: 8 bytes.	TIMESTAMP [ (p) ] [ WITHOUT TIME ZONE ]
	TIMESTAMP [ (p) ] WITH TIME ZONE	Date and time with a time zone. Storage size: 8 bytes.	TIMESTAMP [ (p) ] WITH TIME ZONE
	DATE	A date. Storage size: 4 bytes.	DATE
	TIME [ (p) ] [ WITHOUT TIME ZONE ]	A time without a time zone. Storage size: 8 bytes.	TIME [ (p) ] [ WITHOUT TIME ZONE ]
	TIME [ (p) ] WITH TIME ZONE	A time with a time zone. Storage size: 12 bytes.	TIME [ (p) ] WITH TIME ZONE
	interval [ fields ] [ (p) ]	A time interval. Storage size: 16 bytes.	interval [ fields ] [ (p) ]
boolean type	BOOLEAN	1 byte	BOOLEAN Important If the destination instance is ClickHouse, DTS maps this data type to STRING.
enumerated type	Custom enumeration	N/A	VARCHAR(128)
spatial type	POINT	A point on a plane. Storage size: 16 bytes.	POINT
	LINE	An infinite line. Storage size: 32 bytes.	LINE
	LSEG	A finite line segment. Storage size: 32 bytes.	LSEG
	BOX	A rectangular box. Storage size: 32 bytes.	BOX
	PATH	A path. Storage size: 16 + 16n bytes.	PATH
	POLYGON	A polygon, similar to a closed path. Storage size: 40 + 16n bytes.	POLYGON
	CIRCLE	A circle. Storage size: 24 bytes.	CIRCLE
network address type	CIDR	IPv4 and IPv6 networks. Storage size: 7 or 19 bytes.	CIDR
	INET	IPv4 and IPv6 hosts and networks. Storage size: 7 or 19 bytes.	INET
	MACADDR	MAC address. Storage size: 6 bytes.	MACADDR
	MACADDR8	MAC address (EUI-64 format). Storage size: 8 bytes.	MACADDR8
bit string type	Bit (n)	N/A	Bit (n)
bit string type	BIT VARYING (n)	N/A	BIT VARYING (n)
UUID type	UUID	N/A	VARCHAR(64)
XML type	XML	N/A	XML
JSON type	JSON	N/A	JSON
JSON type	JSONB	N/A	JSONB

Data synchronization from SQL Server

The following data type mappings apply when the source instance is SQL Server (including a self-managed SQL Server or an ApsaraDB RDS for SQL Server instance) and the destination is a heterogeneous database, such as AnalyticDB for MySQL, AnalyticDB for PostgreSQL, PostgreSQL, MySQL, or a PolarDB for MySQL cluster.

Note

If the value range of data from the source instance exceeds the range supported by DTS, its precision decreases when written to the destination instance.

Type	SQL Server type	Range	AnalyticDB for MySQL type	PostgreSQL and AnalyticDB for PostgreSQL type	MySQL and PolarDB for MySQL type
Integer	BIT	An integer that can be 1, 0, or NULL.	BOOLEAN	BIT(1)	BIT(1)
	TINYINT	0 to 255	TINYINT	SMALLINT	TINYINT UNSIGNED
	SMALLINT	-2^15 (-32,768) to 2^15-1 (32,767)	SMALLINT	SMALLINT	SMALLINT
	INT	-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)	INTEGER	INTEGER	INT
	BIGINT	-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)	BIGINT	BIGINT	BIGINT
Decimal	NUMERIC[ (`p`[ ,`s`] )]	-10^38 + 1 to 10^38 - 1; 1 <= `p` <= 38	DECIMAL	DECIMAL	DECIMAL[ (`p`[ ,`s`] )]
	DECIMAL[ (`p`[ ,`s`] )]	-10^38 + 1 to 10^38 - 1; 1 <= `p` <= 38	DECIMAL	DECIMAL	DECIMAL[ (`p`[ ,`s`] )]
	FLOAT	-1.79E+308 to -2.23E-308, 0, and 2.23E-308 to 1.79E+308	DOUBLE	DOUBLE PRECISION	DOUBLE
	REAL	-3.40E+38 to -1.18E-38, 0, and 1.18E-38 to 3.40E+38	FLOAT	REAL	DOUBLE
Monetary	MONEY	-922,337,203,685,477.5808 to 922,337,203,685,477.5807	DECIMAL(19, 4)	DECIMAL(19, 4)	DECIMAL(19, 4)
Monetary	SMALLMONEY	-214,748.3648 to 214,748.3647	DECIMAL(10, 4)	DECIMAL(10, 4)	DECIMAL(10, 4)
Date and time	DATE	0001-01-01 to 9999-12-31	DATE	DATE	DATE
	DATETIME	Date range: January 1, 1753 to December 31, 9999 Time range: 00:00:00 to 23:59:59.997	DATETIME	TIMESTAMP(3) WITHOUT TIME ZONE	DATETIME(3)
	DATETIME2[ (fractional seconds precision) ]	Date range: January 1, 1 CE to December 31, 9999 CE Time range: 00:00:00 to 23:59:59.9999999	DATETIME	TIMESTAMP(7) WITHOUT TIME ZONE	DATETIME(`p`) Note The default precision is 6.
	DATETIMEOFFSET [ (fractional seconds precision) ]	Date range: January 1, 1 CE to December 31, 9999 CE Time range: 00:00:00 to 23:59:59.9999999 Time zone offset range: -14:00 to +14:00	TIMESTAMP	TIMESTAMP(7) WITH TIME ZONE	DATETIME(`p`) Note The default precision is 6.
	SMALLDATETIME	Seconds are always zero (:00) and have no fractional part.	DATETIME	TIMESTAMP WITHOUT TIME ZONE	DATETIME
	TIME [ (fractional second scale) ]	00:00:00.0000000 to 23:59:59.9999999	TIME	TIME(7) WITH TIME ZONE	TIME(`p`)
	TIMESTAMP[(`fsp`)]	1970-01-01 00:00:01.000000 to 2038-01-19 03:14:07.999999 Note Format: YYYY-MM-DD hh:mm:ss[.fraction] (UTC).	VARBINARY(8)	BYTEA	VARBINARY(8)
String	BINARY [ (`n`) ]	`n` ranges from 1 to 8,000.	VARBINARY	BYTEA	If `length` > 255: BLOB Otherwise: BINARY(`n`)
	VARBINARY [ (`n` \| max) ]	`n` is from 1 to 8,000. `max` indicates a maximum storage size of 2^31-1 bytes.	VARBINARY	BYTEA	If `length` = `max`: LONGBLOB Otherwise: VARBINARY(`n`)
	CHAR [ (`n`) ]	`n` ranges from 1 to 8,000. The storage size is `n` bytes.	VARCHAR	CHARACTER	If `length` > 255: VARCHAR Otherwise: CHAR
	VARCHAR [ (`n` \| max ) ]	`n` is from 1 to 8,000. `max` indicates a maximum storage size of 2^31 - 1 bytes (2 GB).	VARCHAR	CHARACTER	If `length` = `max`: LONGTEXT Otherwise: VARCHAR(`n`)
	NCHAR [ (`n`) ]	`n` is the number of byte-pairs (from 1 to 4,000). The storage size is twice the value of `n` in bytes.	VARCHAR	CHARACTER VARYING	VARCHAR(200)
	NVARCHAR [ (`n` \| max ) ]	`n` is the number of byte-pairs (from 1 to 4,000). `max` indicates a maximum storage size of 2^30 - 1 characters (2 GB).	VARCHAR	CHARACTER VARYING	If `length` = `max`: LONGTEXT Otherwise: VARCHAR(`n`)
	NTEXT	Variable-length Unicode data with a maximum string length of 2^30-1 (1,073,741,823) characters.	VARCHAR	TEXT	LONGTEXT
	TEXT	The maximum string length is 2^31-1 (2,147,483,647) bytes.	VARCHAR	TEXT	LONGTEXT
	IMAGE	Variable-length binary data from 0 to 2^31-1 (2,147,483,647) bytes.	VARBINARY	BYTEA	LONGBLOB
Spatial (geography and geometry)	GEOGRAPHY	N/A	VARCHAR	Not supported	BLOB
Spatial (geography and geometry)	GEOMETRY	N/A	VARCHAR	Not supported	BLOB
XML	XML ( [ CONTENT \| DOCUMENT ] xml_schema_collection )	N/A	VARCHAR	XML	LONGTEXT
Other	UNIQUEIDENTIFIER	N/A	VARCHAR	CHARACTER(36)	CHAR(36)
	SQL_VARIANT	N/A	Not supported	Not supported	VARCHAR(200)
	HIERARCHYID	N/A	Not supported	Not supported	VARCHAR(200)
	SYSNAME	N/A	VARCHAR	CHARACTER VARYING(128)	VARCHAR(200)

Data synchronization from Db2 for LUW

The following table lists the data type mappings when synchronizing data from a Db2 for LUW source instance to a heterogeneous destination instance, such as MySQL.

Note

If data from the source instance exceeds the value range supported by Data Transmission Service (DTS), DTS reduces the data's precision in the destination instance.

Type	Db2 for LUW type	Value	MySQL type
Integer	SMALLINT	-32,768 to +32,767	SMALLINT
	INTEGER	-2,147,483,648 to +2,147,483,647	INT
	BIGINT	-9,223,372,036,854,775,808 to +9,223,372,036,854,775,807	BIGINT
Decimal	DECIMAL(precision-integer, scale-integer)	p <= 38	DECIMAL
	FLOAT(integer)	Valid values: 1 to 53. Values from 1 to 24 indicate single precision. Values from 25 to 53 indicate double precision.	FLOAT
	DECFLOAT(precision-integer)	N/A	DECIMAL(65,10)
Date and time	DATE	0001-01-01 to 9999-12-31	DATE
	TIME	00:00:00 to 24:00:00	TIME
	TIMESTAMP(integer)	0001-01-01-00.00.00.000000000000 to 9999-12-31-24.00.00.000000000000; 0 <= p <= 12	DATETIME
String	CHARACTER(integer)	254	CHAR \| VARCHAR
	VARCHAR(integer)	32,672	VARCHAR
	CHARACTER(integer) FOR BIT DATA	254	BLOB
	CLOB	2,147,483,647	LONGTEXT
	GRAPHIC(integer)	127	CHAR(length*4)
	VARGRAPHIC(integer)	16,336	CHAR(length*4)
	DBCLOB(integer)	1,073,741,823	VARCHAR \| LONGTEXT
	BLOB	2,147,483,647	LONGBLOB
Other types	XML	2,147,483,647	VARCHAR \| LONGTEXT

Data synchronization from Db2 for i

This topic describes the data type mappings for data synchronization from a Db2 for i source instance to a heterogeneous destination instance, such as MySQL.

Note

If the value range of data from the source instance exceeds the range supported by DTS, data written to the destination instance may lose precision.

Type	Db2 for i	Value range	MySQL
Integer type	SMALLINT	-32,768 to +32,767	SMALLINT
	INTEGER	-2,147,483,648 to +2,147,483,647	INT
	BIGINT	-9,223,372,036,854,775,808 to +9,223,372,036,854,775,807	BIGINT
Decimal type	DECIMAL(precision-integer, scale-integer)	p <= 63	DECIMAL
	NUMERIC	N/A	DECIMAL
	FLOAT(integer)	N/A	FLOAT
	DECFLOAT(precision-integer)	N/A	DECIMAL(65,10)
Date and time type	DATE	0001-01-01 to 9999-12-31	DATE
	TIME	00:00:00 to 24:00:00	TIME
	TIMESTAMP(integer)	0001-01-01-00.00.00.000000000000 to 9999-12-31-24.00.00.000000000000; 0 <= p <= 12	DATETIME
String type	CHAR(integer)	32,765	CHAR \| VARCHAR
	VARCHAR(integer)	32,739	VARCHAR
	CHAR(integer) FOR BIT DATA	N/A	BLOB
	CLOB	2,147,483,647	LONGTEXT
	GRAPHIC(integer)	16,382	CHAR
	VARGRAPHIC(integer)	16,369	VARCHAR
	DBCLOB(integer)	1,073,741,823	LONGTEXT
	BINARY	32,765	BINARY
	VARBIN	32,739	VARBINARY
	BLOB	2,147,483,647	LONGBLOB
Other types	DATALINK	N/A	VARCHAR \| LONGTEXT
	ROWID	40	VARCHAR \| LONGTEXT
	XML	2,147,483,647	VARCHAR \| LONGTEXT

Data synchronization from TiDB

When synchronizing data from a self-managed TiDB source instance to a heterogeneous destination instance, such as AnalyticDB for MySQL 3.0 or Lindorm, data types are mapped as follows:

TiDB data type	MySQL data type
BIGINT	BIGINT
BIGINT UNSIGNED	DECIMAL(20,0)
BINARY	BINARY
BIT	BIT
BOOL\ BOOLEAN	TINYINT
CHAR	CHAR
DATE	DATE
DATETIME	DATETIME
DECIMAL	DECIMAL
DOUBLE	DOUBLE
ENUM	ENUM
FLOAT	FLOAT
INT	INT
INT UNSIGNED	BIGINT
INTEGER	INTEGER
JSON	JSON
MEDIUMBLOB/LONGBLOB TINYBLOB / BLOB	MEDIUMBLOB/LONGBLOB TINYBLOB or BLOB
MEDIUMINT	MEDIUMINT
SET	SET
SMALLINT	SMALLINT
SMALLINT UNSIGNED	INT
TEXT/LONGTEXT	TEXT/LONGTEXT
TIME	TIME
TIMESTAMP	TIMESTAMP
TINYINT	TINYINT
TINYINT UNSIGNED	SMALLINT
VARBINARY	VARBINARY
VARCHAR	VARCHAR
YEAR	YEAR