Controlling MySQL to PostgreSQL Results

To start this process, obtaining the objects that need validating in the destination database needs to be the first step upon migration completion.

Table Definitions

For MySQL, the definitions are;

• ‘DESC table_name’ will be the console client run SQL statement in MySQL.
• For phpMyAdmin, the ‘Structure’ tab needs to be found in the left pane after highlighting the table.

Within PostgreSQL, the statement will be ‘d table_name’.

When each column has identical default value, type, and size, it is easy to say that the table definition for MySQL is converted property in the PostgreSQL that comes as a result. For each MySQL data type, we have the table of conversions that apply;

In truth, many date types are similar between MySQL and PostgreSQL but some are not equivalent. Therefore, it is vital to know the following tables when migrating from one to the next.

MySQL	PostgreSQL
BIGINT	BIGINT
BINARY(n)	BYTEA
BIT	BOOLEAN
CHAR(n), CHARACTER(n)	CHAR(n), CHARACTER(n)
DATE	DATE
DATETIME	TIMESTAMP [WITHOUT TIME ZONE]
DECIMAL(p,s), DEC(p,s)	DECIMAL(p,s), DEC(p,s)
DOUBLE	DOUBLE PRECISION
FLOAT	REAL
INT, INTEGER	INT, INTEGER
MEDIUMINT	INTEGER
NUMERIC(p,s)	NUMERIC(p,s)
SMALLINT	SMALLINT
TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB	TEXT
TINYINT	SMALLINT
TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT	TEXT
TIME	TIME [WITHOUT TIME ZONE]
TIMESTAMP	TIMESTAMP [WITHOUT TIME ZONE]
VARBINARY(n), VARBINARY(max)	BYTEA
VARCHAR(n)	VARCHAR(n)
VARCHAR(max)	TEXT

To increase the value of the field as soon as a new row is in place, MySQL has ‘auto_increment’ for integer-like columns. For the same purpose, PostgreSQL has SERIAL type and modifications.

MySQL	PostgreSQL
BIGINT AUTO_INCREMENT	BIGSERIAL
INTEGER AUTO_INCREMENT	SERIAL
SMALLINT AUTO_INCREMENT	SMALLSERIAL
TINYINT AUTO_INCREMENT	SMALLSERIAL

In MySQL, smallint, bigint, and other integer types can have an UNSIGNED attribute but this isn’t possible on PostgreSQL. When the unsigned attribute is used, only positive numbers with larger upper range of acceptable values will be taken. For the process to be successful, all unsigned types in MySQL must be mapped into PostgreSQL.

MySQL	PostgreSQL
BIGINT UNSIGNED	NUMERIC(20)
INT UNSIGNED	BIGINT
MEDIUMINT UNSIGNED	INTEGER
SMALLINT UNSIGNED	INTEGER
TINYINT UNSIGNED	INTEGER

Finally, the fact that MySQL can store ‘0000-00-00’ in date columns is yet another challenge to face as PostgreSQL does not. When migrating, many experts say that one should change values to NULLs. However, you might need to use different mapping if the technique breaks the database logics.

Data

Using MySQL and Postgres tables, converted data can be validated by simple visual comparison of different fragments. For example, MySQL allows for the following;

• SELECT * FROM the_table LIMIT start_position, number_of_rows

is the run SQL statement in the MySQL console client

• For phpMyAdmin, the ‘Browse’ tab must be selected in the left pane after highlighting the table.

In order to extract fragments of data, PostgreSQL allows for a similar syntax of SELECT-query. Using SELECT * FROM the_table LIMIT number_of_rows OFFSET start_position;

this process can be achieved.

Furthermore, the tables for MySQL and PostgreSQL will need the same count of rows. The next query allows to get the number of rows in a table with both DBMS:

SELECT COUNT(*) FROM the_table;

 Indexes

• Statement SHOW INDEXES FROM the_table; is getting indexes for the MySQL console client
• For phpMyAdmin, the ‘Structure’ tab must be selected after first highlighting the table. After doing this, all indexes will be shown.

By using the command ‘d table_name’, information can be attained regarding the indexes within PostgreSQL.

Foreign Keys

 Statement SHOW CREATE TABLE `the table name` is getting information about foreign keys for the table in MySQL console client. Foreign keys go at the bottom of the table definition.

• For phpMyAdminm, the ‘Structure’ tab should show a ‘Relations View’ link after highlighting the table.

Using the following SQL statement:

SELECT

tabcon.constraint_name, tabcon.table_name, keycol.column_name,

concol.table_name AS foreign_table_name,

concol.column_name AS foreign_column_name

FROM

information_schema.table_constraints AS tabcon

JOIN information_schema.key_column_usage AS keycol

ON tabcon.constraint_name = keycol.constraint_name

JOIN information_schema.constraint_column_usage AS concol

ON concol.constraint_name = tabcon.constraint_name

WHERE constraint_type = ‘FOREIGN KEY’ AND tabcon.table_name=’the_table_name’;

information regarding foreign keys can be extracted from the ‘information_schema’ service table in PostgreSQL.

Views

Aside from comparing the SELECT-statement for each of the views in MySQL and PostgreSQL, there is no way to verify that every view has been converted successfully. Comprehensive knowledges of database programming are required for this task. The list of all views in source and destination databases can be attained through these queries:

MySQL – SHOW FULL TABLES IN `database name` WHERE TABLE_TYPE LIKE ‘VIEW’;

PostgreSQL – SELECT table_name FROM INFORMATION_SCHEMA.views;

In order to reduce efforts of controlling database migration results, the special reliable software may be used. One of such tools is MySQL to PostgreSQL converter developed by Intelligent Converters, a software company specializing in database conversion and synchronization since 2001.