The first thing one should do is to look at the destination database to see which objects need to be validated after migrating a database from MySQL to PostgreSQL.
Table Definitions
Table definitions are exposed in MySQL like this:
- If you’re using the MySQL client, you should run this SQL statement: DESC table_name
- If you’re using phpMyAdmin go to the left pane, highlight the table, and from there select the ‘Structure’ tab
In PostgreSQL, you can run this statement to expose the definition: \d table_name
Once every column has equal sizes, default values and types in the PostgreSQL table that is generated from the MySQL conversion process, you know it’s been properly converted. You can see the values for appropriate conversions in this table for the various data types of MySQL:
Remember, while there are equivalencies in the data types of PostgreSQL and MySQL, some are exactly the same while others are a bit different. Check this table for types mapping before you begin migrating from MySQL to PostgreSQL.
| 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 |
While PostgreSQL uses SERIAL type, along with its modifications, for integer-like columns, in MySQL the equivalent argument is known as ‘auto_increment’, and what it does is automatically increase the value of the field whenever a new row is put in.
| MySQL | PostgreSQL |
| BIGINT AUTO_INCREMENT | BIGSERIAL |
| INTEGER AUTO_INCREMENT | SERIAL |
| SMALLINT AUTO_INCREMENT | SMALLSERIAL |
| TINYINT AUTO_INCREMENT | SMALLSERIAL |
In MySQL, every type of integer is able to have an UNSIGNED attribute. This applies to bigint, int, smallint and tinyint. This attribution means that you have to use only positive numbers, with a greater upper range within the span of acceptable values. This is not the case in PostgreSQL. You can see in the following table how you need to map unsigned MySQL types into PostgreSQL.
| MySQL | PostgreSQL |
| BIGINT UNSIGNED | NUMERIC(20) |
| INT UNSIGNED | BIGINT |
| MEDIUMINT UNSIGNED | INTEGER |
| SMALLINT UNSIGNED | INTEGER |
| TINYINT UNSIGNED | INTEGER |
Remember, in MySQL you can enter the following value into date columns: ‘000-00-00’. You can’t do this in PostgreSQL. Instead, you’ll need to put NULL rather than that value when you migrate data. There is the danger of breaking database logics by doing this though, and in such a case you’ll need to use a different mapping.
Data
You can visually compare fragments from PostgreSQL and MySQL tables to validate the data you’ve converted. You can do it like this:
- If you’re using the MySQL client, to explore data fragments, run this SQL statement SELECT * FROM the_table LIMIT start_position, number_of_rows
- If you’re using phpMyAdmin go to the left pane, where you’ll highlight the table. After that select the ‘Browse’ tab
In PostgreSQL the process is similar, with the SELECT-query, but there are a number of differences.
SELECT * FROM the_table LIMIT number_of_rows OFFSET start_position;
You’ll also need to make sure that both your MySQL tables and your PostgreSQL tables have the same row count. You can do that in both MySQL and PostSQL with this query:
SELECT COUNT(*) FROM the_table;
Indexes
In MySQL, this is how you list indexes:
- If you’re using the MySQL client, you can run the following SQL statement:
SHOW INDEXES FROM the_table;
- If you’re using phpMyAdmin go to the left pane, where you’ll highlight the table. After this you can go to the ‘Structure’ tab. Once you select that, after the table structure all the indexes will be listed.
In PostgreSQL, you’ll be able go to the bottom of the table definition to see information about indexes. You can generate this table by using this command: \d table_name
Foreign Keys
In MySQL, if you want to expose foreign keys information, you’ll need to do the following:
- If you’re using the MySQL console client, you should run the following SQL statement <<<SQLCODE4>>>
- If you’re using phpMyAdmin, go to the left pane where you will highlight the table, after that go to the ‘Structure’ tab and below the table you will select the ‘Relations view’ link.
In PostgreSQL, you can get the same information by using the service table “information_schema”:
SELECT
tabcon.constraint_name, tabcon.table_name, keycol.column_name,
concol.table_name AS ftable_name,
concol.column_name AS fcolumn_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’;
Views
Comparing the SELECT-statement of each view in PostgreSQL and MySQL is really the only way you can make sure that all views have been converted correctly, keeping these two DBMS’s differences in SQL dialects in mind. Because this requires extensive knowledge of database programming, it is not included in this article. You can, however, easily access a list of all views in both the destination and source databases in both DBMS.
In MySQL, you can use the following query to expose the list of the views in the database:
SHOW FULL TABLES IN `database name` WHERE TABLE_TYPE LIKE ‘VIEW’;
You can perform the same function in PostgreSQL by using this query:
SELECT table_name FROM INFORMATION_SCHEMA.views;