Menu
AWS Schema Conversion Tool
User Guide (Version 1.0)

PostgreSQL to MySQL Conversion Reference

AGGREGATE FUNCTION

Item Issue Resolution

bit_and, bit_or

Issue 6680: Check working of function with null values

Perform a manual conversion.

OVER/ORDER BY/WITHIN GROUP/FILTER

Issue 6678: MySQL doesn't support function %s with OVER/ORDER BY/WITHIN GROUP/FILTER clause

Perform a manual conversion.

ALTER

Item Issue Resolution

ALTER

Issue 6676: Automatic conversion of the ALTER statement isn't supported

Perform a manual conversion.

Arithmetic Operators

Item Issue Resolution

Arithmetic operators and string type

Issue 6681: Unable to perform an automated migration of arithmetic operations with string and other types

Make cast operands to the expected type.

ARRAY

Item Issue Resolution

ARRAY

Issue 6608: MySQL doesn't support arrays

Perform a manual conversion.

Buildin function

Item Issue Resolution

DateTime

Issue 6677: MySQL does not support time zone

Perform a manual conversion.

BUILT-IN SQL FUNCTIONS

Item Issue Resolution

BUILT-IN SQL FUNCTIONS

Issue 6667: MySQL doesn't support the %s function

Create a user-defined function.

CREATE

Item Issue Resolution

CREATE

Issue 6094: Unable to convert object due to %s not created

Review the %s object.

CURSOR

Item Issue Resolution

CURSOR with clause NEXT, PRIOR, FIRST, LAST, ABSOLUTE, RELATIVE, FORWARD or BACKWARD

Issue 6639: MySQL doesn't support the direction clause

Perform a manual conversion.

CURSOR with MOVE option

Issue 6640: MySQL doesn't support the MOVE option

Perform a manual conversion.

CURSOR with options SCROLL or NO SCROLL

Issue 6638: MySQL doesn't support the SCROLL option in cursors

Revise your code to eliminate cursors with the SCROLL option.

REFCURSOR

Issue 6337: MySQL doesn't support a variable of REFCURSOR type

Try using temporary tables.

Databases

Item Issue Resolution

ALTER DATABASE

Issue 6101: MySQL doesn't support this clause in an ALTER DATABASE statement

Perform a manual conversion.

CREATE DATABASE, DROP DATABASE

Issue 6100: The database-to-database migration is not provided

Use the schema-to-database migration plan.

DATA TYPES

Item Issue Resolution

CAST( ... AS BIT | BIT VARYING | VARBIT | DECIMAL | NUMERIC | REAL | FLOAT4 | DOUBLE PRECISION | FLOAT8 | CHARACTER | CHAR | CHARACTER VARYING | TEXT | TIME WITH TIME ZONE | TIME(p) WITH TIME ZONE | TIMESTAMP WITH TIME ZONE | TIMESTAMP(p) WITH TIME )

Issue 6673: MySQL doesn't support %s type in the CAST function, and a loss of precision or accuracy of data is possible

Check if the data violates these restrictions. If so, perform a manual migration.

CAST( ... AS BOX | POINT | LINE | LSEG | PATH | POLYGON | CIRCLE | TXID_SNAPSHOT)

Issue 6674: MySQL doesn't support %s type in the CAST function

Check if the data violates these restrictions. If so, perform a manual migration.

DELETE

Item Issue Resolution

RETURNING

Issue 6069: MySQL doesn't support the DELETE statement with the RETURNING option

To perform this operation, divide the DELETE statement with the RETURNING clause into a DELETE statement with following INSERT statements and use the same key conditions in each SELECT.

WHERE CURRENT OF

Issue 6672: MySQL does not support delete by cursor

Perform a manual conversion.

Domains

Item Issue Resolution

CREATE DOMAIN, ALTER DOMAIN, DROP DOMAIN

Issue 6050: MySQL doesn't support domains

Perform a manual conversion.

DYNAMIC SQL

Item Issue Resolution

RETURN QUERY EXECUTE

Issue 6334: MySQL doesn't support the dynamic SQL statement RETURN QUERY EXECUTE

Review and modify the execution string.

Error Handling

Item Issue Resolution

GET [CURRENT] DIAGNOSTICS ... PG_ CONTEXT

Issue 6665: MySQL doesn't support the GET [ CURRENT ] DIAGNOSTICS command

Perform a manual conversion.

GET STACKED DIAGNOSTICS PG_DATATYPE_NAME| TABLE_NAME| SCHEMA_NAME| PG_EXCEPTION_DETAIL| PG_EXCEPTION_HINT| PG_EXCEPTION_CONTEXT

Issue 6664: MySQL doesn't support the condition information %s

Perform a manual conversion.

RAISE [exception]

Issue 6329: MySQL doesn't support the RAISE exception

Review the RAISE exception used, and if possible convert it to an exception using the SIGNAL or RESIGNAL statement.

EXCEPTIONS

Item Issue Resolution

EXCEPTIONS

Issue 6342: MySQL doesn't support the %s condition name

Review the exception used, and if possible convert it to an exception using the SIGNAL or RESIGNAL statement.

FROM

Item Issue Resolution

FROM

Issue 6603: Can't use function in from clause

Perform a manual conversion.

QUERY

Issue 6682: Can't use the table-value function in the from clause

MySQL does not support the table-value function.

FUNCTION

Functions

Item Issue Resolution

RETURN NEXT

Issue 6604: MySQL doesn't support RETURN NEXT clause

Perform a manual conversion.

VARIADIC

Issue 6605: MySQL doesn't support VARIADIC mode of an argument

Perform a manual conversion.

Indexes

Item Issue Resolution

CREATE INDEX: GiST, SPGiST, Gin methods

Issue 6091: MySQL doesn't support %s index method

Perform a manual conversion.

Expression-based indexes

Issue 6090: MySQL doesn't support indexes on expressions

Perform a manual conversion.

Ordered indexes

Issue 6092: MySQL doesn't support explicit sort ordering in the index definition

If default (ascending) sort order in the index might cause performance degradation, perform a manual conversion or rewrite statements supposed to use it.

Partial indexes

Issue 6093: MySQL doesn't support partial indexes

If the full index might cause performance degradation, perform a manual conversion.

INSERT

Item Issue Resolution

RETURNING

Issue 6172: MySQL doesn't support the INSERT statement with the RETURNING option

To perform this operation, divide the INSERT statement with the RETURNING clause into an INSERT statement with following SELECT statements and use the same key conditions in each SELECT. You can also use the last value that was generated by AUTO_INCREMENT column in the key condition.

INTERSECT, EXCEPT

Item Issue Resolution

INTERSECT, EXCEPT

Issue 6612: Query with INTERSECT/EXCEPT ALL operator was transformed

Perform a manual conversion.

LABEL

Item Issue Resolution

LABEL

Issue 6344: MySQL doesn't support labeled variables

Try rewriting variables without using labels.

LIMIT

Item Issue Resolution

LIMIT, OFFSET

Issue 6611: LIMIT/OFFSET option was omitted

Perform a manual conversion.

Materialized Views

Item Issue Resolution

CREATE MATERIALIZED VIEW

Issue 6130: MySQL doesn't support materialized views; the source materialized view is converted to a table with triggers

If this migration approach is unsuitable for any reason, perform a manual conversion.

Message from Stored Routines

Item Issue Resolution

RAISE DEBUG|LOG|INFO|NOTICE|WARNING

Issue 6332: MySQL doesn't support the RAISE statement to report messages

Try using INSERT in the log table. To do this, you must add code into AWS_POSTGRESQL_EXT.RAISE_DEBUG, AWS_POSTGRESQL_EXT.RAISE_LOG, AWS_POSTGRESQL_EXT.RAISE_INFO, AWS_POSTGRESQL_EXT.RAISE_NOTICE, and AWS_POSTGRESQL_EXT.RAISE_WARNING.

Operators and Date Functions

Item Issue Resolution

Date/Time Operators

Issue 6660: Unable to convert the %s operation with the interval value as one or more arguments

Perform a manual conversion.

Date/Time Operators

Issue 6661: The operation will return the number of days instead of the interval value

Check if the resulting type meets your requirements. If it doesn't, perform a manual conversion.

Date/Time Operators

Issue 6662: Unsupported format for the interval's value

Currently, the only supported format is a SQL ANSI format for interval literals. Perform a manual conversion.

ORDER BY

Parser Error

Item Issue Resolution

Parser Error

Issue 6663: Unable to resolve the object

Verify if object %s is present in the database. If it isn't, check the object name or add the object. If the object is present, transform the code manually.

Patterns

Item Issue Resolution

SIMILAR TO

Issue 6607: Could not convert pattern to MySQL version; check pattern manually

Perform a manual conversion.

ROW/RECORD Type

Item Issue Resolution

ROW()

Issue 6606: MySQL doesn't support ROW() function with any, some, or all predicates

Perform a manual conversion.

ROW/RECORD

Issue 6668: MySQL doesn't support ROW/RECORD type

Perform a manual conversion.

Select

Item Issue Resolution

DISTINCT ON

Perform a manual conversion.

ONLY | *

Perform a manual conversion.

DateTime

Issue 6679: Value don't fixed on start transaction

Review your code, and if needed use a local variable

SELECT INTO

Item Issue Resolution

SELECT INTO

Issue 6666: Transformation of not strict into clause was performed

Perform a manual conversion.

SELECT, UPDATE, INSERT, DELETE

Item Issue Resolution

WITH

Issue 6127: MySQL doesn't support the WITH RECURSIVE clause

Use a stored procedure to prepare data, or rewrite your query to avoid the WITH clause.

Sequences

Item Issue Resolution

CREATE SEQUENCE, ALTER SEQUENCE, DROP SEQUENCE

Issue 6060: MySQL doesn't support sequences

Perform a manual conversion.

SQL Functions

Item Issue Resolution

COLLATION

Issue 6343: MySQL doesn't support the COLLATE option; automatic conversion cannot be performed

You must use the COLLATION settings that were assigned when the database was created.

Table Columns

Item Issue Resolution

Application data types: CIDR, INET, MACADDR, UUID, XML, JSON, JSONB, TSVECTOR, TSQUERY

Issue 6006: MySQL doesn't support %s type

Check the target data type and correct it if inappropriate.

ARRAY

Issue 6008: MySQL doesn't support array types for table column definition

Perform a manual conversion.

CIRCLE

Issue 6011: MySQL doesn't support CIRCLE as a spatial type; the approximated results of BUFFER function of POLYGON data type is used to perform a conversion

If the approximated representation is not suitable for any reason, perform a manual conversion.

DECIMAL types

Issue 6002: MySQL doesn't support %s with precision more than 65 digits and scale more than 30 digits; the loss of precision or accuracy of data is possible

Check, if the data violates these restrictions. If so, perform a manual migration.

INTERVAL

Issue 6005: MySQL doesn't support INTERVAL type

Perform a manual conversion.

LINE

Issue 6010: MySQL doesn't support infinite lines; a LINESTRING with two points on the line is used for conversion

If the two-pointed representation is not suitable for any reason, perform a manual conversion.

Range types

Issue 6009: MySQL doesn't support range types

Perform a manual conversion.

Serial types: BIGSERIAL / SERIAL8 / SERIAL / SERIAL4 / SMALLSERIAL / SERIAL2

Issue 6001: Unable to provide full migration for auto-increment table columns

MySQL supports only one auto-increment column per table and it must be a key. Decide whether the column should be an auto-incremented key or not, and if it should, alter it manually.

TIME WITH TIME ZONE

Issue 6003: MySQL doesn't support time zone information for %s type

Perform a manual conversion.

Timestamps with time zone

Issue 6004: MySQL doesn't support time zone information for %s type

Perform a manual conversion. If the time zone information has no value, you can choose DATETIME(6) as a target data type. Otherwise, try to convert the data into TIMESTAMP(6), taking into account the value of time_zone server setting.

Table Columns, Function Arguments, Local Variables

Item Issue Resolution

Bit strings

Issue 6012: Converted bit strings are right-padded by zero bits to full-byte length

If the approximated representation is not suitable for any reason, perform a manual conversion.

Tables

Item Issue Resolution

Constraints

Issue 6111: MySQL doesn't support deferrable constraints

Check your schema and ensure that non-deferrable constraints can provide the necessary level of integrity. Otherwise, perform a manual conversion on the constraints.

Constraints

Issue 6112: MySQL doesn't support exclusion constraints

Perform a manual conversion of the exclusion constraint to provide the same level of data integrity.

Constraints

Issue 6113: MySQL doesn't support check constraints; the source constraint is converted to a conditional error raising in triggers

If the approach with triggers is unsuitable, perform a manual conversion of the check constraint to provide the same level of data integrity.

Constraints

Issue 6114: MySQL doesn't support SET DEFAULT as a referential action in foreign keys

Check that SET NULL is an appropriate referential action, otherwise perform a manual conversion of the constraint.

Constraints

Issue 6115: MySQL doesn't support expressions in DEFAULT constraints, so they are converted to a BEFORE INSERT trigger

Right now, explicitly inserted NULL values are not distinguished from those specially omitted to use default values. Also, updating values to DEFAULT is not supported at the present time. If you need that functionality, perform a manual conversion.

Constraints

Issue 6117: Unable to keep the NOT NULL constraint for %s column while converting its DEFAULT constraint based on an expression

Due to the transformation of the DEFAULT constraint based on an expression into the part of a trigger, the NOT NULL constraint is converted as another part of the same trigger.

Tables without columns

Issue 6110: MySQL doesn't support tables without columns

Perform a manual conversion.

Typed tables

Issue 6107: MySQL doesn't support unlogged tables

If a regular table is unsuitable, perform a manual conversion.

Typed tables

Issue 6108: MySQL doesn't support inherited tables

Perform a manual conversion.

Typed tables

Issue 6109: MySQL doesn't support typed tables

Perform a manual conversion.

Triggers

Item Issue Resolution

Conditional triggers

Issue 6083: MySQL doesn't support conditional triggers that fire on updates of particular column(s); a conversion will omit such a condition

If a resulting trigger must fire for updates of only the particular column(s), perform a manual conversion.

Constraint triggers

Issue 6082: MySQL doesn't support constraint triggers

Perform a manual conversion.

Disabled triggers

Issue 6087: MySQL doesn't support disabling triggers

Perform a manual conversion.

DROP TRIGGER

Issue 6086: Unable to automatically transform dropping a trigger

Perform a manual conversion.

Instead-of triggers

Issue 6084: MySQL doesn't support INSTEAD OF triggers

Perform a manual conversion.

Multiple triggers on the same event

Issue 6085: MySQL doesn't support multiple triggers on the same event; triggers are merged into one in alphabetical order by trigger name

If a result of merging is unappropriate, perform a manual conversion.

One trigger on multiple events

Issue 6081: MySQL doesn't support triggers firing on multiple events; such a trigger is converted to multiple single-event triggers with the same body

If this migration approach is not suitable for the trigger, perform a manual conversion.

Statement-level triggers

Issue 6080: MySQL doesn't support statement-level triggers

Perform a manual conversion.

Trigger functions

Issue 6650: Unable to convert the statement with TG_ARGV automatically

Perform a manual conversion.

Trigger functions

Issue 6651: Unsupported using of RETURN statement in the trigger converted to LEAVE statement

Check flow of execution and perform a manual conversion if necessary.

Trigger functions

Issue 6652: MySQL doesn't support OLD record in triggers on INSERT or NEW record in triggers on DELETE

Check, if NULL instead of a field of the unsupported record is appropriate.

Trigger on TRUNCATE event

Issue 6088: MySQL doesn't support TRUNCATE event for triggers

Perform a manual conversion.

UDT

Item Issue Resolution

CREATE TYPE, ALTER TYPE, DROP TYPE

Issue 6120: MySQL doesn't support user-defined data types

Perform a manual conversion.

Unknown

Item Issue Resolution

Unknown

Issue 6013: This syntactic element conversion is not supported yet

Perform a manual conversion.

UPDATE

Item Issue Resolution

FROM

Issue 6669: FROM clause was rewritten

Perform a manual conversion.

RETURNING

Issue 6066: MySQL doesn't support the UPDATE statement with the RETURNING option

To perform this operation, divide the UPDATE statement with the RETURNING clause into an UPDATE statement with following INSERT statements that have the specified key conditions in the SELECT part.

WHERE CURRENT OF

Issue 6670: MySQL does not support update by cursor

Perform a manual conversion.

User-Defined Aggregate Functions

Item Issue Resolution

CREATE AGGREGATE, ALTER AGREGGATE, DROP AGREGGATE

Issue 6140: Unable to convert user-defined aggregate statements

Perform a manual conversion.

VIEW

Item Issue Resolution

QUERY

Issue 6076: The SELECT statement cannot contain a subquery in the FROM clause

Rewrite the view query without a subquery, or create an auxiliary table for the subquery.

Views

Item Issue Resolution

Default values for the view's columns

Issue 6072: MySQL doesn't support default values for the view's column

If the same functionality is necessary, perform a manual conversion.

Row-level secured views

Issue 6071: MySQL doesn't support row-level security

If the same functionality is necessary, perform a manual conversion.

Temporary views

Issue 6070: MySQL doesn't support temporary views or views on temporary tables

Perform a manual conversion.