Menu
AWS Schema Conversion Tool
User Guide (Version 1.0)

MySQL to PostgreSQL Supported Schema Conversion

The following sections list the schema elements from a MySQL database and whether they are supported for automatic conversion to PostgreSQL using the AWS Schema Conversion Tool.

DDL

Alter Statements

ALTER TABLE

Clause Automatically Converted Details

ADD CONSTRAINT

Yes

ADD INDEX

Yes

Create Statements

CREATE INDEX

Clause Automatically Converted Details

INDEX

Yes

UNIQUE index

Yes

CREATE PROCEDURE

Clause Automatically Converted Details

PARAMETERS: IN, OUT, INOUT

Yes

With any output parameters

Yes

Without output parameters

Yes

CREATE TABLE

Clause Automatically Converted Details

Regular tables

Yes

Temporary tables

Yes

CREATE TRIGGER

Clause Automatically Converted Details

FOR EACH ROW Triggers

Yes

Trigger event (Insert / Update / Delete )

Yes

Trigger time (Before / After)

Yes

CREATE VIEW

Clause Automatically Converted Details

With ALGORITHM OPTION

Partial

Issue 8824: ALGORITHM option is not supported

With CHECK OPTION

Yes

Naming Objects

Clause Automatically Converted Details

Schemas

Yes

Tables, Columns

Yes

DML

Clauses

FROM

Clause Automatically Converted Details

CROSS JOIN

Yes

INNER JOIN

Yes

join_condition: ...ON conditional_expr

Yes

join_condition: … USING (column_list)

Yes

LEFT JOIN

Yes

LEFT OUTER JOIN

Yes

NATURAL LEFT JOIN

Yes

NATURAL LEFT OUTER JOIN

Yes

NATURAL RIGHT JOIN

Yes

NATURAL RIGHT OUTER JOIN

Yes

RIGHT JOIN

Yes

RIGHT OUTER JOIN

Yes

STRAIGHT_JOIN

Yes

Subqueries in the FROM Clause

Yes

tables_list

Yes

Statements

DELETE

INSERT

Clause Automatically Converted Details

IGNORE

Partial

Issue 8831: PostgreSQL doesn't have an option similar to IGNORE for DML statements

LOW_PRIORITY | DELAYED | HIGH_PRIORITY

Partial

Issue 8830: PostgreSQL doesn't have an option similar to LOW_PRIORITY for DML statements

ON DUPLICATE KEY UPDATE

No

Issue 8829: PostgreSQL doesn't have an analog of clause ON DUPLICATE KEY UPDATE

SELECT-statement for insert

Yes

SET col1=expr1,..

Yes

VALUES(,…,)|VALUE(,…,)

Yes

SELECT

Clause Automatically Converted Details

DISTINCT

Yes

GROUP BY ASC, DESC

Yes

GROUP BY col_name,…

Yes

GROUP BY expression

Yes

GROUP BY mixed options

Yes

GROUP BY position,…

Yes

GROUP BY WITH ROLLUP

No

Issue 8653: PostgreSQL doesn't support the option GROUP BY ROLLUP

HAVING

Yes

HIGH_PRIORITY

Partial

Issue 8836: PostgreSQL doesn't have an option similar to %s for DML statements

LIMIT offset, row_count

Yes

LIMIT row_count OFFSET offset

Yes

MAX_STATEMENT_TIME = N

Partial

Issue 8836: PostgreSQL doesn't have an option similar to %s for DML statements

ORDER BY col_name,…

Yes

ORDER BY expression

Yes

ORDER BY mixed options

Yes

ORDER BY position,…

Yes

SQL_BIG_RESULT

Partial

Issue 8836: PostgreSQL doesn't have an option similar to %s for DML statements

SQL_BUFFER_RESULT

Partial

Issue 8836: PostgreSQL doesn't have an option similar to %s for DML statements

SQL_CACHE

Partial

Issue 8836: PostgreSQL doesn't have an option similar to %s for DML statements

SQL_CALC_FOUND_ROWS

Partial

Issue 8836: PostgreSQL doesn't have an option similar to %s for DML statements

SQL_NO_CACHE

Partial

Issue 8836: PostgreSQL doesn't have an option similar to %s for DML statements

SQL_SMALL_RESULT

Partial

Issue 8836: PostgreSQL doesn't have an option similar to %s for DML statements

STRAIGHT_JOIN

Partial

Issue 8836: PostgreSQL doesn't have an option similar to %s for DML statements

Select all columns (using * and aliases)

Yes

Select all columns (using *)

Yes

Select subset of the columns

Yes

Select with calculations

Yes

Select with column heading

Yes

Select with constants

Yes

Subquery as scalar operand

Yes

UPDATE

Clause Automatically Converted Details

DEFAULT VALUES

Yes

IGNORE

Partial

Issue 8831: PostgreSQL doesn't have an option similar to IGNORE for DML statements

LIMIT

Yes

LOW_PRIORITY

Partial

Issue 8830: PostgreSQL doesn't have an option similar to LOW_PRIORITY for DML statements

Multiple-table syntax

Yes

ORDER BY

Yes

Functions

Aggregate

Clause Automatically Converted Details

AVG()

Yes

BIT_AND()

Yes

BIT_OR()

Yes

BIT_XOR()

No

COUNT()

Yes

COUNT(DISTINCT)

No

GROUP_CONCAT()

Yes

MAX()

Yes

MIN()

Yes

STD()

Yes

STDDEV()

Yes

STDDEV_POP()

Yes

STDDEV_SAMP()

Yes

SUM()

Yes

VAR_POP()

Yes

VAR_SAMP()

Yes

VARIANCE()

Yes

Bit

Clause Automatically Converted Details

BIT_COUNT()

Yes

Control Flow

Clause Automatically Converted Details

CASE

Yes

IF()

Yes

IFNULL()

Yes

NULLIF()

Yes

Conversion

Clause Automatically Converted Details

BINARY

Yes

CAST()

Yes

CONVERT()

Yes

Date and Time

Clause Automatically Converted Details

ADDDATE()

Yes

ADDTIME()

Yes

CONVERT_TZ()

Yes

CURDATE()

Yes

CURRENT_DATE(), CURRENT_DATE

Yes

CURRENT_TIME(), CURRENT_TIME

Yes

CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP

Yes

CURTIME()

Yes

DATE()

Yes

DATE_ADD()

Yes

DATE_FORMAT()

Yes

DATE_SUB()

Yes

DATEDIFF()

Yes

DAY()

Yes

DAYNAME()

Yes

DAYOFMONTH()

Yes

DAYOFWEEK()

Yes

DAYOFYEAR()

Yes

EXTRACT()

Yes

FROM_DAYS()

Yes

FROM_UNIXTIME()

Yes

GET_FORMAT()

Yes

HOUR()

Yes

LAST_DAY

Yes

LOCALTIME(), LOCALTIME

Yes

LOCALTIMESTAMP, LOCALTIMESTAMP()

Yes

MAKEDATE()

Yes

MAKETIME()

Yes

MICROSECOND()

Yes

MINUTE()

Yes

MONTH()

Yes

MONTHNAME()

Yes

NOW()

Yes

PERIOD_ADD()

Yes

PERIOD_DIFF()

Yes

QUARTER()

Yes

SEC_TO_TIME()

Yes

SECOND()

Yes

STR_TO_DATE()

Yes

SUBDATE()

Yes

SUBTIME()

Yes

SYSDATE()

No

TIME()

Yes

TIME_FORMAT()

Yes

TIME_TO_SEC()

Yes

TIMEDIFF()

Yes

TIMESTAMP()

Yes

TIMESTAMPADD()

Yes

TIMESTAMPDIFF()

Yes

TO_DAYS()

Yes

TO_SECONDS()

Yes

UNIX_TIMESTAMP()

Yes

UTC_DATE()

Yes

UTC_TIME()

Yes

UTC_TIMESTAMP()

Yes

WEEK()

No

WEEKDAY()

Yes

WEEKOFYEAR()

Yes

YEAR()

Yes

YEARWEEK()

No

Encryption and Compression

Clause Automatically Converted Details

AES_DECRYPT()

No

AES_ENCRYPT()

No

COMPRESS()

No

DECODE()

No

DES_DECRYPT() (deprecated 5.7.6)

No

DES_ENCRYPT() (deprecated 5.7.6)

No

ENCODE()

No

ENCRYPT() (deprecated 5.7.6)

No

MD5()

No

OLD_PASSWORD()

No

PASSWORD() (deprecated 5.7.6)

No

RANDOM_BYTES()

No

SHA1(), SHA()

No

SHA2()

No

UNCOMPRESS()

No

UNCOMPRESSED_LENGTH()

No

VALIDATE_PASSWORD_STRENGTH()

No

Information

Clause Automatically Converted Details

BENCHMARK()

No

CHARSET()

No

COERCIBILITY()

No

COLLATION()

No

CONNECTION_ID()

No

CURRENT_USER(), CURRENT_USER

No

DATABASE()

No

FOUND_ROWS()

No

LAST_INSERT_ID()

No

ROW_COUNT()

No

SCHEMA()

No

SESSION_USER()

No

SYSTEM_USER()

No

USER()

No

VERSION()

No

Mathematical

Clause Automatically Converted Details

ABS()

Yes

ACOS()

Yes

ASIN()

Yes

ATAN()

Yes

ATAN2()

Yes

CEIL()

Yes

CEILING()

Yes

CONV()

No

Issue 8811: PostgreSQL doesn't support the %s function

COS()

Yes

COT()

Yes

CRC32()

Yes

DEGREES()

Yes

EXP()

Yes

FLOOR()

Yes

LN()

Yes

LOG()

Yes

LOG10()

Yes

LOG2()

Yes

MOD()

Yes

PI()

Yes

POW()

Yes

POWER()

Yes

RADIANS()

Yes

RAND()

Yes

ROUND(X)

Yes

SIGN()

Yes

SIN()

Yes

SQRT()

Yes

TAN()

Yes

TRUNCATE()

Yes

String

Clause Automatically Converted Details

ASCII()

Yes

BIN()

Yes

BIT_LENGTH()

Yes

CHAR()

Yes

CHAR_LENGTH()

Yes

CHARACTER_LENGTH()

Yes

CONCAT()

No

CONCAT_WS()

No

ELT()

Yes

EXPORT_SET()

Yes

FIELD()

Yes

FIND_IN_SET()

Yes

FORMAT()

No

Issue 8811: PostgreSQL doesn't support the %s function

FROM_BASE64()

No

Issue 8811: PostgreSQL doesn't support the %s function

HEX()

Yes

INSERT()

Yes

INSTR()

Yes

LCASE()

Yes

LEFT()

Yes

LENGTH()

Yes

LIKE

Yes

LOAD_FILE()

No

Issue 8811: PostgreSQL doesn't support the %s function

LOCATE()

Yes

LOWER()

Yes

LPAD()

Yes

LTRIM()

Yes

MAKE_SET()

Yes

MATCH

No

Issue 8811: PostgreSQL doesn't support the %s function

MID()

Yes

NOT REGEXP

Yes

NOT RLIKE

Yes

OCT()

Yes

OCTET_LENGTH()

Yes

ORD()

Yes

POSITION()

Yes

QUOTE()

Yes

REGEXP

Yes

REPEAT()

Yes

REPLACE()

Yes

REVERSE()

Yes

RIGHT()

Yes

RLIKE

Yes

RPAD()

Yes

RTRIM()

No

SOUNDEX()

No

Issue 8811: PostgreSQL doesn't support the %s function

SOUNDS LIKE

No

Issue 8811: PostgreSQL doesn't support the %s function

SPACE()

Yes

STRCMP()

Yes

SUBSTR()

Yes

SUBSTRING()

Yes

SUBSTRING_INDEX()

Yes

TO_BASE64()

No

Issue 8811: PostgreSQL doesn't support the %s function

TRIM()

Yes

UCASE()

Yes

UNHEX()

Yes

UPPER()

Yes

WEIGHT_STRING()

No

Issue 8811: PostgreSQL doesn't support the %s function

XML

Clause Automatically Converted Details

ExtractValue()

No

Issue 8811: PostgreSQL doesn't support the %s function

UpdateXML()

No

Issue 8811: PostgreSQL doesn't support the %s function

Data Types

Date and Time

Data type Automatically Converted to Details

DATE

date

DATETIME

timestamp without time zone

TIME

time

TIMESTAMP

timestamp without time zone

YEAR

smallint

YEAR (M)

smallint

JSON

Data type Automatically Converted to Details

JSON

VARCHAR(8000)

Issue 8706: PostgreSQL doesn't support %s type

Numeric

Integer Types

Data type Automatically Converted to Details

BIGINT [SIGNED]

bigint

BIGINT UNSIGNED

numeric

BOOL

boolean

Issue 8848: In MySQL the BOOLEAN type is a synonym for TINYINT

BOOLEAN

boolean

Issue 8848: In MySQL the BOOLEAN type is a synonym for TINYINT

INT [INTEGER] [SIGNED]

integer

INT [INTEGER] UNSIGNED

bigint

MEDIUMINT [SIGNED]

integer

MEDIUMINT UNSIGNED

integer

SMALLINT [SIGNED]

smallint

SMALLINT UNSIGNED

integer

TINYINT [SIGNED]

smallint

TINYINT UNSIGNED

smallint

Bit

Data type Automatically Converted to Details

BIT

bit

Fixed-Point

Data type Automatically Converted to Details

DEC

numeric

DEC (p)

numeric

DEC (p,s)

numeric

DECIMAL

numeric

DECIMAL (p)

numeric

DECIMAL (p,s)

numeric

NUMERIC

numeric

NUMERIC (p)

numeric

NUMERIC (p,s)

numeric

Floating-Point

Data type Automatically Converted to Details

DOUBLE

double precision

DOUBLE PRECISION

double precision

DOUBLE PRECISION (p)

double precision

DOUBLE PRECISION (p,s)

double precision

DOUBLE(p)

double precision

DOUBLE(p,s)

double precision

FLOAT

real

FLOAT(p)

precision from 1-24: real

precision > 24: double precision

FLOAT(p,s)

double precision

REAL

double precision

REAL(p)

double precision

REAL(p,s)

double precision

Spatial Data Types

Data type Automatically Converted to Details

GEOMETRY

VARCHAR(8000)

Issue 8706: PostgreSQL doesn't support %s type

GEOMETRYCOLLECTION

VARCHAR(8000)

Issue 8706: PostgreSQL doesn't support %s type

LINESTRING

VARCHAR(8000)

Issue 8706: PostgreSQL doesn't support %s type

MULTILINESTRING

VARCHAR(8000)

Issue 8706: PostgreSQL doesn't support %s type

MULTIPOINT

VARCHAR(8000)

Issue 8706: PostgreSQL doesn't support %s type

MULTIPOLYGON

VARCHAR(8000)

Issue 8706: PostgreSQL doesn't support %s type

POINT

VARCHAR(8000)

Issue 8706: PostgreSQL doesn't support %s type

POLYGON

VARCHAR(8000)

Issue 8706: PostgreSQL doesn't support %s type

String

Data type Automatically Converted to Details

BINARY

bytea

Issue 8706: PostgreSQL doesn't support %s type

BLOB

bytea

Issue 8706: PostgreSQL doesn't support %s type

CHAR

char

CHAR(len)

char

ENUM

CREATE ENUM user type

LONGBLOB

bytea

Issue 8706: PostgreSQL doesn't support %s type

LONGTEXT

text

MEDIUMBLOB

bytea

Issue 8706: PostgreSQL doesn't support %s type

MEDIUMTEXT

text

SET

VARCHAR(8000)

Issue 8706: PostgreSQL doesn't support %s type

TEXT

text

TINYBLOB

bytea

Issue 8706: PostgreSQL doesn't support %s type

TINYTEXT

text

VARBINARY(len)

bytea

Issue 8706: PostgreSQL doesn't support %s type

VARCHAR(len)

varchar

On this page: