Data type mappings between heterogeneous databases - Data Transmission Service

Heterogeneous databases have different data types. When Data Transmission Service (DTS) migrates data between heterogeneous databases, DTS converts the data types of the source database to those of the destination database during schema migration. This topic describes the data type mappings for you to evaluate the impact of data migration on your business.

Overview

You can view the data type mappings between heterogeneous databases based on the following migration scenarios:

Migrate data from a PolarDB for MySQL cluster, an ApsaraDB RDS for MySQL instance, or a self-managed MySQL database
Migrate data from an Oracle database
Migrate data from an SQL Server database
Migrate data from a self-managed TiDB database
Migrate data from a Db2 for LUW database
Migrate data from a Db2 for i database
Migrate data from a Teradata database

Important

If the data type of the time field in the source database is TIMESTAMP WITH TIME ZONE and the data type of the time field in the destination database is a heterogeneous data type, such as DATETIME, the time zone information in the time field will be lost.

Migrate data from a PolarDB for MySQL cluster, an ApsaraDB RDS for MySQL instance, or a self-managed MySQL database

The following table describes the data type mappings between a source MySQL database and a heterogeneous destination database. The source database can be a PolarDB for MySQL cluster, an ApsaraDB RDS for MySQL instance, or a self-managed MySQL database. The destination database can be an AnalyticDB for MySQL V2.0 cluster or an AnalyticDB for PostgreSQL instance.

Note

If the value range of the data to be migrated from the source database exceeds the range supported by DTS, the accuracy of the data migrated to the destination database decreases.

Type	Data type in the source database	Value range	Data type in AnalyticDB for MySQL	Data type in AnalyticDB for PostgreSQL
Integer	BIT[(M)]	1 ~ 64	VARCHAR	BIT[(M)]
	TINYINT[(M)]	-128 ~ 127	TINYINT	SMALLINT
	TINYINT[(M)] [UNSIGNED]	0 ~ 255	SMALLINT	SMALLINT
	SMALLINT[(M)]	-32768 ~ 32767	SMALLINT	SMALLINT
	SMALLINT[(M)] [UNSIGNED]	0 ~ 65535	INT	INTEGER
	MEDIUMINT[(M)]	-8388608 ~ 8388607	INT	INTEGER
	MEDIUMINT[(M)] [UNSIGNED]	0 ~ 16777215	INT	INTEGER
	INT[(M)]	-2147483648 ~ 2147483647	INT	INTEGER
	INT[(M)] [UNSIGNED]	0 ~ 4294967295	BIGINT	BIGINT
	BIGINT[(M)]	-9223372036854775808 ~ 9223372036854775807	BIGINT	BIGINT
	BIGINT[(M)] [UNSIGNED]	0 ~ 18446744073709551615.	DECIMAL(20,0)	NUMERIC(20)
Decimal	DECIMAL[(M[,D])]	M: 0 to 65 D: 0 to 30	DECIMAL[(M[,D])]	DECIMAL[(M[,D])]
	FLOAT(p)	1.175494351E-38 ~ 3.402823466E+38	FLOAT	REAL
	DOUBLE[(M,D)]	2.2250738585072014E-308 ~ 1.7976931348623157E+308	DOUBLE	DOUBLE PRECISION
Date and time	DATE	1000-01-01~9999-12-31 Note The format is YYYY-MM-DD.	DATE	DATE
	DATETIME[(fsp)]	1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.999999 Note The format is YYYY-MM-DD hh:mm:ss[.fraction], in Coordinated Universal Time (UTC).	DATETIME	TIMESTAMP
	TIMESTAMP[(fsp)]	1970-01-01 00:00:01.000000 ~ 2038-01-19 03:14:07.999999 Note The format is YYYY-MM-DD hh:mm:ss[.fraction], in UTC.	TIMESTAMP	TIMESTAMP WITH TIME ZONE
	TIME[(fsp)]	-838:59:59.000000 ~ 838:59:59.000000 Note The format is hh:mm:ss[.fraction], in UTC.	TIME	TIME WITHOUT TIME ZONE
	YEAR[(4)]	1901 to 2155, or 0000	INT	INTEGER
String	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',...)	An ENUM column can have a maximum of 65,535 distinct elements.	VARCHAR	VARCHAR(128)
	SET('value1','value2',...)	A SET column can have a maximum of 64 distinct elements.	VARCHAR	VARCHAR(128)
Spatial	GEOMETRY	Geometry values of any 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 geometry values of any type	VARBINARY	POLYGON
JSON	JSON	N/A	JSON	JSON

Destination database: a DataHub project, an ApsaraMQ for Kafka instance, or a self-managed Kafka cluster

Type	Data type in the source database	Value range	Data type in DataHub	Data type in an ApsaraMQ for Kafka instance or a self-managed Kafka cluster
Integer	BIT[(M)]	1 ~ 64	BOOLEAN \| STRING	Consistent with the data types in MySQL or PolarDB for MySQL
	TINYINT[(M)]	-128 ~ 127	BIGINT
	TINYINT[(M)] [UNSIGNED]	0 ~ 255	BIGINT
	SMALLINT[(M)]	-32768 ~ 32767	BIGINT
	SMALLINT[(M)] [UNSIGNED]	0 ~ 65535	BIGINT
	MEDIUMINT[(M)]	-8388608 ~ 8388607	BIGINT
	MEDIUMINT[(M)] [UNSIGNED]	0 ~ 16777215	BIGINT
	INT[(M)]	-2147483648 ~ 2147483647	BIGINT
	INT[(M)] [UNSIGNED]	0 ~ 4294967295	BIGINT
	BIGINT[(M)]	-9223372036854775808 ~ 9223372036854775807	BIGINT
	BIGINT[(M)] [UNSIGNED]	0 ~ 18446744073709551615	BIGINT
Decimal	DECIMAL[(M[,D])]	M: 0 to 65 D: 0 to 30	DECIMAL
	FLOAT(p)	1.175494351E-38 ~ 3.402823466E+38	DOUBLE
	DOUBLE[(M,D)]	2.2250738585072014E-308 ~ 1.7976931348623157E+308	DOUBLE
Date and time	DATE	1000-01-01~9999-12-31 Note The format is YYYY-MM-DD.	TIMESTAMP
	DATETIME[(fsp)]	1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.999999 Note The format is YYYY-MM-DD hh:mm:ss[.fraction], in UTC.	TIMESTAMP
	TIMESTAMP[(fsp)]	1970-01-01 00:00:01.000000 ~ 2038-01-19 03:14:07.999999 Note The format is YYYY-MM-DD hh:mm:ss[.fraction], in UTC.	TIMESTAMP
	TIME[(fsp)]	-838:59:59.000000 ~ 838:59:59.000000 Note The format is hh:mm:ss[.fraction], in 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 or 4 GB (2^32 - 1) bytes	STRING
	LONGTEXT	4,294,967,295 or 4 GB (2^32 - 1) characters	STRING
	ENUM('value1','value2',...)	An ENUM column can have a maximum of 65,535 distinct elements.	STRING
	SET('value1','value2',...)	A SET column can have a maximum of 64 distinct elements.	STRING
Spatial	GEOMETRY	Geometry values of any 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 geometry values of any type	STRING
JSON	JSON	N/A	STRING

Destination database: a MaxCompute project, an Elasticsearch cluster, or a ClickHouse cluster

Type	Data type in the source database	Value range	MaxCompute	Elasticsearch	ClickHouse
Integer	BIT[(M)]	1 ~ 64	BOOLEAN \| STRING	BOOLEAN \| LONG Note If the data is only one byte long, we recommend that you use the BOOLEAN data type in Elasticsearch.	UInt8
	TINYINT[(M)]	-128 ~ 127	BIGINT	SHORT	Int8
	TINYINT[(M)] [UNSIGNED]	0 ~ 255	BIGINT	INTEGER	UInt8
	SMALLINT[(M)]	-32768 ~ 32767	BIGINT	SHORT	Int16
	SMALLINT[(M)] [UNSIGNED]	0 ~ 65535	BIGINT	INTEGER	UInt16
	MEDIUMINT[(M)]	-8388608 ~ 8388607	BIGINT	INTEGER	Int32
	MEDIUMINT[(M)] [UNSIGNED]	0 ~ 16777215	BIGINT	INTEGER	Int32
	INT[(M)]	-2147483648 ~ 2147483647	BIGINT	INTEGER	Int32
	INT[(M)] [UNSIGNED]	0 ~ 4294967295	BIGINT	LONG	UInt32
	BIGINT[(M)]	-9223372036854775808 ~ 9223372036854775807	BIGINT	LONG	Int64
	BIGINT[(M)] [UNSIGNED]	0 ~ 18446744073709551615	BIGINT	LONG	UInt64
Decimal	DECIMAL[(M[,D])]	M: 0 to 65 D: 0 to 30	DOUBLE	DOUBLE Note If the DECIMAL value contains a decimal point, we recommend that you use the TEXT data type in Elasticsearch to ensure data consistency.	DECIMAL
	FLOAT(p)	1.175494351E-38 ~ 3.402823466E+38	DOUBLE	FLOAT	Float32
	DOUBLE[(M,D)]	2.2250738585072014E-308 ~ 1.7976931348623157E+308	DOUBLE	DOUBLE	Float64
Date and time	DATE	1000-01-01~9999-12-31 Note The format is YYYY-MM-DD.	DATETIME	DATE Note The format is YYYY-MM-DD. For more information, see format.	DATE32
	DATETIME[(fsp)]	1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.999999 Note The format is YYYY-MM-DD hh:mm:ss[.fraction], in UTC.	DATETIME	DATE Note The DATE data format is yyyy-MM-dd'T'HH:mm:ss, in UTC. If the DATE data is accurate to microseconds, the data format is yyyy-MM-dd'T'HH:mm:ss.S. For more information, see format.	DATETIME64
	TIMESTAMP[(fsp)]	1970-01-01 00:00:01.000000 ~ 2038-01-19 03:14:07.999999 Note The format is YYYY-MM-DD hh:mm:ss[.fraction], in UTC.	DATETIME	DATE Note The DATE data format is yyyy-MM-dd'T'HH:mm:ss, in UTC. If the DATE data is accurate to microseconds, the data format is yyyy-MM-dd'T'HH:mm:ss.S. For more information, see format.	DATETIME Note The DATETIME data does not contain information about the time zone.
	TIME[(fsp)]	-838:59:59.000000 ~ 838:59:59.000000 Note The format is hh:mm:ss[.fraction], in UTC.	STRING	DATE Note The format is YYYY-MM-DD. For more information, see format.	STRING
	YEAR[(4)]	1901 to 2155, or 0000	STRING	DATE Note The DATE format is yyyy, in UTC. For more information, see format.	Int16
String	CHAR[(M)]	0 to 255 characters	STRING	TEXT	STRING
	VARCHAR(M)	0 to 65,535 characters	STRING	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',...)	An ENUM column can have a maximum of 65,535 distinct elements.	STRING	KEYWORD	ENUM
	SET('value1','value2',...)	A SET column can have a maximum of 64 distinct elements.	STRING	KEYWORD	STRING
Spatial	GEOMETRY	Geometry values of any 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 If the data is only one byte long, we recommend that you use the BOOLEAN data type in Elasticsearch.	STRING
	MULTILINESTRING	N/A	STRING	GEO_SHAPE	STRING
	MULTIPOLYGON	N/A	STRING	GEO_SHAPE	STRING
	GEOMETRYCOLLECTION	A collection of geometry values of any type	STRING	GEO_SHAPE	STRING
JSON	JSON	N/A	STRING	OBJECT Note If the data is only one byte long, we recommend that you use the BOOLEAN data type in Elasticsearch.	STRING

Destination database: a Tablestore instance

Data type in the source database	Data type in Tablestore
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 value: 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

Destination database: a Lindorm instance

Data type in the source database	Data type in Lindorm
BOOLEAN	BOOLEAN
BIT	BOOLEAN
TINYINT	TINYINT
SMALLINT	SMALLINT
INTEGER	INTEGER
BIGINT	BIGINT
FLOAT	FLOAT
DOUBLE	DOUBLE
DECIMAL	DECIMAL
CHAR/VARCHAR/TEXT/TINYTEXT/MEDIUMTEXT/LONGTEXT	CHAR/VARCHAR
BINARY	BINARY
BLOB	VARBINARY
VARBINAY	VARBINARY
TIMESTAMP	TIMESTAMP
YEAR	INTEGER
DATE	VARCHAR
DATETIME	VARCHAR Important We recommend that you map the DATETIME data in the source database to data of the VARCHAR type in the destination database. If you want to map the DATETIME data to data of the TIMESTAMP type, data inconsistency may occur due to the difference in time zones. We recommend that you use the extract, transform, and load (ETL) feature when you configure the data synchronization task. This ensures data consistency.
TIME	VARCHAR
JSON	JSON

Migrate data from an Oracle database

The following table describes the data type mappings between a source self-managed Oracle database and a heterogeneous destination database. The destination database can be a self-managed MySQL database, a PolarDB for MySQL cluster, an AnalyticDB for MySQL cluster, an AnalyticDB for PostgreSQL instance, or a PolarDB for PostgreSQL (Compatible with Oracle) cluster.

Note

If the value range of the data to be migrated from the source database exceeds the range supported by DTS, the accuracy of the data migrated to the destination database decreases.

Type	Data type in Oracle	Value range	Data type in MySQL, PolarDB for MySQL, and PolarDB-X	Data type in ApsaraDB RDS for PPAS	Data type in AnalyticDB for MySQL	Data type in AnalyticDB for PostgreSQL	Data type in PolarDB for PostgreSQL (Compatible with Oracle)
Numeric	NUMBER(p,s)	1 to 22 bytes The argument p indicates the precision. Valid values: 1 to 38. The argument s indicates scale. Valid values: -84 to 127.	DECIMAL[(p[,s])]	NUMBER[(p[,s])]	DECIMAL \| TINYINT \| SMALLINT \| INTEGER \| BIGINT	DECIMAL \| TINYINT \| SMALLINT \| INTEGER \| BIGINT	NUMBER(p,s)
	FLOAT(p)	1 to 22 bytes The variable p indicates a pointer. Valid values: 1 to 126 bits.	DOUBLE	DOUBLE PRECISION	DOUBLE	DOUBLE PRECISION	DOUBLE PRECISION
	BINARY_FLOAT	A 32-bit floating-point number (4 bytes)	DECIMAL(65,8)	REAL	DOUBLE	DOUBLE PRECISION	REAL
	BINARY_DOUBLE	A 64-bit floating-point number (8 bytes)	DOUBLE	DOUBLE PRECISION	DOUBLE	DOUBLE PRECISION	DOUBLE PRECISION
Date and time	DATE	N/A	DATETIME	DATE	DATETIME	TIMESTAMP(0)	DATE
	TIMESTAMP [(fractional_seconds_precision)]	N/A	DATETIME[(fractional_seconds_precision)]	TIMESTAMP [(fractional_seconds_precision)]	DATETIME	TIMESTAMP	TIMESTAMP [(fractional_seconds_precision)]
	TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE	N/A	DATETIME[(fractional_seconds_precision)]	TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE	TIMESTAMP	TIMESTAMP WITH TIME ZONE	TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE
	TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE	N/A	DATETIME[(fractional_seconds_precision)]	TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE	DATETIME	TIMESTAMP WITH TIME ZONE	TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE
	INTERVAL YEAR [(year_precision)] TO MONTH	N/A	Not supported	Not supported	VARCHAR	VARCHAR(32)	INTERVAL
	INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]	N/A	Not supported	Not supported	VARCHAR	VARCHAR(32)	INTERVAL
String	CHAR [(size [BYTE \| CHAR])]	2,000 bytes	CHAR[(n)]	CHAR[(n)]	VARCHAR	CHAR	CHAR [(size [BYTE \| CHAR])]
	NCHAR[(size)]	2,000 bytes	NATIONAL CHAR[(n)]	NCHAR[(n)]	VARCHAR	VARCHAR	NCHAR[(size)]
	VARCHAR2(size [BYTE \| CHAR])	If the MAX_STRING_SIZE parameter is set to EXTENDED, the maximum size is 32,767 bytes. If the MAX_STRING_SIZE parameter is set to STANDARD, the maximum size is 4,000 bytes.	VARCHAR(n)	VARCHAR2[(n)]	VARCHAR	VARCHAR	VARCHAR2(size [BYTE \| CHAR])
	NVARCHAR2(size)	If the MAX_STRING_SIZE parameter is set to EXTENDED, the maximum size is 32,767 bytes. If the MAX_STRING_SIZE parameter is set to STANDARD, the maximum size is 4,000 bytes.	NATIONALVARCHAR[(n)]	VARCHAR2[(n)]	VARCHAR	VARCHAR	NVARCHAR2(size)
	LONG	The maximum size is 2 GB (2^31 - 1).	LONGTEXT	LONG	VARCHAR	TEXT	LONG
	RAW(size)	The maximum size is 32,767 bytes or 2,000 bytes.	VARBINARY(2000)	RAW(size)	VARBINARY	BYTEA	RAW(size)
	LONG RAW	The maximum size is 2 GB.	LONGBLOB	LONG RAW	VARBINARY	BYTEA	LONG RAW
	CLOB	The maximum size is (4 GB - 1) × DB_BLOCK_SIZE.	LONGTEXT	CLOB	VARCHAR	TEXT	CLOB
	NCLOB	The maximum size is (4 GB - 1) × DB_BLOCK_SIZE.	LONGTEXT	NCLOB	VARCHAR	TEXT	CLOB
	BLOB	The maximum size is (4 GB - 1) × DB_BLOCK_SIZE.	LONGBLOB	BLOB	VARBINARY	BYTEA	BLOB
	BFILE	The maximum size is 4 GB.	Not supported	Not supported	Not supported	Not supported	Not supported
JSON	JSON	The maximum size is 32 MB.	Not supported	Not supported	JSON	JSON	JSON
ROWID	ROWID	64 characters	Not supported	Not supported	ROWID	OID	VARCHAR
Spatial	Customization required	Not supported

Note

Destination database: MySQL, PolarDB for MySQL, or PolarDB-X
- If a CHAR field in the source database is greater than 255 bytes in length, DTS converts this field to the VARCHAR(n) type in the destination database.
- MySQL does not support the following Oracle data types: BFILE, INTERVAL YEAR TO MONTH, and INTERVAL DAY TO SECOND. Therefore, DTS does not convert these data types during schema migration.
  If a table to be migrated contains these data types, schema migration fails. You must make sure that the columns of these data types are excluded from the objects to be migrated.
- The TIMESTAMP data type of MySQL databases does not contain the time zone information. However, the TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE data types in Oracle databases provide the time zone information. Therefore, DTS converts the values of these data types to UTC time in the destination database.
Destination database: ApsaraDB RDS for PPAS
ApsaraDB RDS for PPAS does not support the TIMESTAMP[(fractional_seconds_precision)] WITH LOCAL TIME ZONE data type. DTS converts the data of this type to UTC time and then stores the data in the destination ApsaraDB RDS for PPAS instance by using the TIMESTAMP[(fractional_seconds_precision)] WITH TIME ZONE data type.
Destination database: AnalyticDB for PostgreSQL
If an Oracle data type is not supported by AnalyticDB for PostgreSQL, DTS converts the data type to BYTEA. If the conversion fails, DTS sets the field value to NULL.

Migrate data from an SQL Server database

The following table describes the data type mappings between a source SQL Server database and a heterogeneous destination database. The source database can be a self-managed SQL Server database or an ApsaraDB RDS for SQL Server instance. The destination database can be an AnalyticDB for MySQL cluster, an AnalyticDB for PostgreSQL instance, a PostgreSQL database, or a MySQL database.

Note

If the value range of the data to be migrated from the source database exceeds the range supported by DTS, the accuracy of the data migrated to the destination database decreases.

Type	Data type in SQL Server	Value range	Data type in AnalyticDB for MySQL	Data type in AnalyticDB for PostgreSQL	Data type in PostgreSQL	Data type in MySQL
Integer	BIT	1, 0, and NULL	BOOLEAN	BIT(1)		BIT
	TINYINT	0 to 255	TINYINT	SMALLINT		TINYINT
	SMALLINT	-32768 (-2^15) to 32767 (2^15 - 1)	SMALLINT	SMALLINT		SMALLINT
	INT	-2147483648 (-2^31) to 2147483647 (2^31 - 1)	INTEGER	INTEGER		INT
	BIGINT	-9223372036854775808 (-2^63) to 9223372036854775807 (2^63 - 1)	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	-922337203685477.5808 to 922337203685477.5807	DECIMAL(19, 4)	DECIMAL(19, 4)		DECIMAL
Monetary	SMALLMONEY	-214748.3648 to 214748.3647	DECIMAL(10, 4)	DECIMAL(10, 4)		DECIMAL
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
	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
	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
	SMALLDATETIME	The time is based on a 24-hour day, with seconds always zero (:00) and without fractional seconds.	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
String	BINARY [ ( n ) ]	Valid values of n: 1 to 8000.	VARBINARY	BYTEA		BINARY
	VARBINARY [ ( n \| max) ]	Valid values of n: 1 to 8000. max indicates that the maximum storage size is 2^31 - 1 bytes.	VARBINARY	BYTEA		VARBINARY
	CHAR [ ( n ) ]	Valid values of n: 1 to 8000. The storage size is n bytes.	VARCHAR	CHARACTER		If the length is greater than 65535, the data type is TEXT. If the length is greater than 255 and smaller than or equal to 65535, the data type is VARCHAR. If the length is smaller than or equal to 255, the data type is CHAR.
	VARCHAR [ ( n \| max ) ]	Valid values of n: 1 to 8000. max indicates that the maximum storage size is 2^31 - 1 bytes (2 GB).	VARCHAR	CHARACTER		If the length is greater than 65535, the data type is TEXT. If the length is smaller than or equal to 65535, the data type is VARCHAR.
	NCHAR [ ( n ) ]	n defines the string size in byte-pairs. Valid values of n: 1 to 4000. The storage size is two times n bytes.	VARCHAR	CHARACTER VARYING		VARCHAR(200)
	NVARCHAR [ ( n \| max ) ]	n defines the string size in byte-pairs. Valid values of n: 1 to 4000. max indicates that the maximum storage size is 2^30 - 1 characters (2 GB).	VARCHAR	CHARACTER VARYING		If the length is greater than 65535, the data type is TEXT. If the length is smaller than or equal to 65535, the data type is VARCHAR.
	NTEXT	Variable-length Unicode data with a maximum string length of 1,073,741,823 (2^30 - 1) bytes.	VARCHAR	TEXT		TEXT
	TEXT	The maximum string length is 2,147,483,647 (2^31 - 1) bytes.	VARCHAR	TEXT		TEXT
	IMAGE	Variable-length binary data from 0 to 2,147,483,647 (2^31 - 1) bytes.	VARBINARY	BYTEA		BLOB
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		TEXT
Other types	UNIQUEIDENTIFIER	N/A	VARCHAR	CHARACTER(36)		VARCHAR(200)
	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)

Migrate data from a self-managed TiDB database

The following table describes the data type mappings between a source self-managed TiDB database and a heterogeneous destination database. The destination database can be a MySQL database or an AnalyticDB for MySQL V3.0 cluster.

Data type in TiDB	Data type in MySQL
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/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

Migrate data from a Db2 for LUW database

The following table describes the data type mappings between a source Db2 for LUW database and a destination MySQL database.

Note

If the value range of the data to be migrated from the source database exceeds the range supported by DTS, the accuracy of the data migrated to the destination database decreases.

Type	Data type in Db2 for LUW	Value range	Data type in MySQL
Integer	SMALLINT	-32,768~+32,767	SMALLINT
	INTEGER	-2,147,483,648~+2,147,483,647	INT
	BIGINT	-9,223,372,036,854,775,808~ +9,223,372,036,854,775,807	BIGINT
Decimal	DECIMAL(precision-integer, scale-integer)	p<=38	DECIMAL
	FLOAT(integer)	The value range is 1 to 53. If the integer is in the range of 1 to 24, the format is single precision floating-point. If the integer is in the range of 25 to 53, the format is double precision floating-point.	FLOAT
	DECFLOAT(precision-integer)	N/A	DECIMAL(65,10)
Date and time	DATE	0001-01-01~9999-12-31	DATE
	TIME	00:00:00~24:00:00	TIME
	TIMESTAMP(integer)	0001-01-01-00.00.00.000000000000~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

Migrate data from a Db2 for i database

The following table describes the data type mappings between a source Db2 for i database and a destination MySQL database.

Note

If the value range of the data to be migrated from the source database exceeds the range supported by DTS, the accuracy of the data migrated to the destination database decreases.

Type	Data type in Db2 for i	Value range	Data type in MySQL
Integer	SMALLINT	-32,768~+32,767	SMALLINT
	INTEGER	-2,147,483,648~+2,147,483,647	INT
	BIGINT	-9,223,372,036,854,775,808~ +9,223,372,036,854,775,807	BIGINT
Decimal	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	DATE	0001-01-0~9999-12-31	DATE
	TIME	00:00:00~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	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

Migrate data from a Teradata database

The following table describes the data type mappings between a source Teradata database and a destination AnalyticDB for PostgreSQL instance.

Data type in Teradata	Data type in AnalyticDB for PostgreSQL
BYTEINT	SMALLINT
SMALLINT	SMALLINT
BIGINT	BIGINT
INTEGER	INTEGER
DATE	DATE
JSON	JSON
XML	XML
CLOB	text
Float	real
CHAR	CHAR
VARCHAR	VARCHAR
Timestamp	Timestamp
TIME	TIME
Timestamp With Time Zone	Timestamp With Time Zone
Time With Time Zone	Time With Time Zone
Decimal	Decimal
Number	numeric
BYTE	bytea
VARBYTE	bytea
BLOB	bytea
PERIOD	varchar(100)
INTERVAL	varchar(100)

Data type in Teradata	Data type in AnalyticDB for PostgreSQL
SMALLINT	SMALLINT
INTEGER	INT
BIGINT	BIGINT
DECIMAL(precision-integer, scale-integer)	DECIMAL
NUMERIC	DECIMAL
FLOAT(integer)	FLOAT
DECFLOAT(precision-integer)	DECIMAL(65,10)
DATE	DATE
TIME	TIME
TIMESTAMP(integer)	DATETIME
CHAR(integer)	CHAR \| VARCHAR
VARCHAR(integer)	VARCHAR
CHAR(integer) FOR BIT DATA	BLOB
CLOB	LONGTEXT
GRAPHIC (integer)	CHAR
VARGRAPHIC (integer)	VARCHAR
DBCLOB (integer)	LONGTEXT
BINARY	BINARY
VARBIN	VARBINARY
BLOB	LONGBLOB
DATALINK	VARCHAR \| LONGTEXT
ROWID	VARCHAR \| LONGTEXT
XML	VARCHAR \| LONGTEXT