Help Center/ Data Replication Service/ Service Overview/ Mapping Data Types/ MySQL->PostgreSQL
Updated on 2024-09-26 GMT+08:00
View PDF
Share

MySQL->PostgreSQL

Table 1 Data type mapping

Data Type (MySQL)

Data Type (PostgreSQL)

Whether to Support Mapping

BIGINT

NUMERIC|BIGINT

Yes

BINARY

BYTEA

Yes

BIT

BIT

Yes

BLOB

BYTEA

Yes

BOOLEAN

BOOL

Yes

CHAR

CHAR

Yes

DATE

DATE

Yes

DATETIME

TIMESTAMP

Yes

DECIMAL

NUMERIC

Yes

DOUBLE

FLOAT8

Yes

ENUM

VARCHAR

Yes

FLOAT

FLOAT4|FLOAT8

Yes

INT

INTEGER

Yes

LONGBLOB

BYTEA

Yes

LONGTEXT

TEXT

Yes

MEDIUMBLOB

BYTEA

Yes

MEDIUMINT

INT

Yes

SET

VARCHAR

Yes

SMALLINT

INT|SMALLINT

Yes

TEXT

TEXT

Yes

TIME

TIME

Yes

TIMESTAMP

TIMESTAMP

Yes

TINYBLOB

BYTEA

Yes

TINYINT

SMALLINT

Yes

TINYTEXT

TEXT

Yes

VARBINARY

BYTEA

Yes

VARCHAR

VARCHAR

Yes

YEAR

SMALLINT

Yes

JSON

JSON

Yes

GEOMETRY

-

No
  • DATE values supported by MySQL range from '1000-01-01' to '9999-12-31'.

    DATETIME values supported by MySQL range from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

    TIMESTAMP values supported by MySQL range from '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

    For details, see official MySQL documentation.

    For PostgreSQL, 0000-00-00 is an invalid date and will be converted to 1970-01-01 by DRS. For example, '0000-00-00' of the DATE type in MySQL is converted to '1970-01-01' by DRS, and '1000-00-31 23:59:59' of the DATETIME or TIMESTAMP type in MySQL is converted to '1970-01-01 00:00:00' by DRS.

  • TIME values supported by MySQL range from '-838:59:59' to '838:59:59'. For details, see the official MySQL documentation. For PostgreSQL, the minimum value of the TIME type is 00:00:00 and the maximum value is 24:00:00. In MySQL, if a value of the TIME type is less than 00:00:00 or greater than 24:00:00, DRS will convert it to 00:00:00.
  • YEAR value ranges supported by MySQL are 1901 to 2155 and 0000. For details, see official MySQL documentation. PostgreSQL does not have the YEAR type, so DRS will convert the YEAR type of MySQL to the SMALLINT type.
  • For MySQL databases, '0000' of the DATE type will be converted to 0 by DRS.
  • If the data type of a column is INT and the column contains the AUTO_INCREMENT attribute, DRS converts the data type of the column to SERIAL during synchronization.
Parent topic: Mapping Data Types