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:
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.
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
|
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.
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 |
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.
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 | |
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 |
| ||
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 |
| ||
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 |
| ||
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 | |
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.
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.
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 |