Skip to content

Migrating from Postgres to MySQL

Learn how to migrate from Postgres to MySQL Postgres vs MySQL incompatibilities and more.

Migrating from Postgres to MySQL

Choosing a data store is one of the most important decisions you'll make when building software applications. The good and bad news though, is that there is an abundance of options. Depending on the type of application you're building, you may opt for a relational database like MySQL or Postgres, a non-relational database like MongoDB or CouchDB, a graph database like Neo4j, or one of the many other alternatives that each bring their own benefits and drawbacks.

It is a difficult choice and sometimes you realize that the database you initially went with no longer serves the needs of your application and you have to make the choice to migrate.

In this blog post, we'll take a look at how you can migrate your database schema from PostgreSQL to MySQL. MySQL and PostgreSQL or Postgres are both relational databases that have a lot of similarities, but also have a fair amount of differences that can make migration a challenge.

A detailed list of the differences between PostgreSQL and MySQL can be found below:

MetricPostgresMySQL
LicensingReleased under PostgreSQL license, a free open-source license similar to BSD or MIT licensesSource code is available under the terms of the GNU General Public License
ACIDYesYes
TriggersSupports AFTER, BEFORE, and INSTEAD OF triggers for INSERT, UPDATE, and DELETE statementsSupports AFTER and BEFORE triggers for INSERT, UPDATE, and DELETE statements
Unsigned integerNo supportA column can be made not to accept negative values
Materialized viewsSupportedNot by default, but PlanetScale Boost gives you cached queries even more efficient than materialized views
ANSI/ISO SQL complianceFully compliantMostly compliant
Drop temporary tableNo TEMP or TEMPORARY keyword in DROP TABLE statementSupports the TEMP or TEMPORARY keyword in the DROP TABLE statement, allowing you to remove just the temporary table
Table partitioningSupports RANGE, LIST, and HASHSupports RANGE, COLUMN, LIST, HASH, KEY, and either HASH or KEY for composite partitioning

These differences are important to keep in mind when making the decision to migrate from Postgres to MySQL and vice versa. With that said, let's dive into how we can migrate an existing Postgres database to MySQL.

An example migration from Postgres to MySQL

In this article, we'll take the approach of migrating from PostgreSQL to MySQL manually. While there are software tools, ORMs, and other approaches we can take that might abstract the migration from one database to another, and we will look at those approaches in subsequent tutorials, here we want to explore what you'll need to consider at a low level.

For our sample migration, we are going to compare an instance of PostgreSQL and what a migration to MySQL might look like. For PostgreSQL, we'll use a locally hosted server. For MySQL, rather than setting up a local database, we'll test using PlanetScale's hosted MySQL offering. If you don't already have a PlanetScale account, you can sign up now.

Differences between our Postgres and MySQL schema

In our PostgreSQL schema, we have three tables: a products table that holds information about our inventory, a customers table that holds information about our customers, and an orders table that keeps records of orders placed by our customers.

The products table in PostgreSQL looks like the following:

SQL
CREATE TABLE products
(
id SERIAL,
name VARCHAR,
description VARCHAR,
price INTEGER
);

So the data could be represented as:

id (SERIAL)name (VARCHAR)description (VARCHAR)price (INTEGER)
1Achieving PlanetScaleAchieving PlanetScale teaches you how to think bigger and more horizontally!50
2The Database that CouldFollow the adventures of the database that could accomplish anything it wanted100

VARCHAR in Postgres vs MySQL

Looking at the data types within this table, the migration to MySQL should be pretty straightforward. All of the data types match up with what we have available in MySQL. While on the surface it does look pretty straightforward, there are a few things to consider. In MySQL for the varchar type, we have to set a max value, whereas in Postgres we did not. We can instead opt to use the text type instead in MySQL, or keep the same varchar type, but specify a max length. If we do opt for the varchar option, we'll have to know what the max value is, otherwise, we'll run into issues when we actually migrate the data over.

SERIAL in Postgres vs MySQL

Additionally, if we set the serial type in MySQL, it'll default the underlying data type to a bigint unsigned auto_increment. In the grand scheme of things, this won't affect how we store the data too much, but is worth noting because depending on how many records we have, a different data type may be more suitable.

The customers table in PostgreSQL looks like the following:

SQL
CREATE TABLE customers
(
id SERIAL,
full_name VARCHAR,
address VARCHAR,
location POINT
);

If we look at our sample data for this table, we'll get the following:

id (SERIAL)full_name (VARCHAR)address (VARCHAR)location (POINT)
1Robert California123 Sunny St, AZ(34.411275716904406,-111.6783709992531)
2Nick Claus785 North Pole, AL(69.72578389209082,-153.14940161799086)

Now things get a little bit more interesting. While the first three columns are something we dealt with in the previous example, the fourth column represents a data type that is available in Postgres as well as in MySQL, but behaves quite differently in practice.

Spatial data in Postgres vs MySQL: POINT

In Postgres, working with spatial data is fairly straightforward. When we define the location column as a type of Point, we can insert spatial data into it by giving it the coordinates we want. So if we wanted to add a new record into our database, we could do the following:

SQL
INSERT INTO customers (id, full_name, address, location) VALUES (3, 'Michael West', '532 Alexander St, WA', POINT(47.490272897328325, -122.27293296965925));

And our data would be properly reflected in the database as:

id (SERIAL)full_name (VARCHAR)address (VARCHAR)location (POINT)
1Robert California123 Sunny St, AZ(34.411275716904406,-111.6783709992531)
2Nick Claus785 North Pole, AL(69.72578389209082,-153.14940161799086)
3Michael West532 Alexander St, WA(47.490272897328325, -122.27293296965925)

In MySQL, however, spatial data types behave differently, and depending on the version of MySQL you're using, you may have to use a different approach to storing spatial data. If we updated our MySQL schema to align with our customers schema in our Postgres database, our table might look something like this:

SQL
CREATE TABLE customers
(
id INT NOT NULL AUTO_INCREMENT,
full_name TEXT,
address TEXT,
location POINT,
PRIMARY KEY (id)
);

And to migrate the above data into our MySQL database, we could write a query such as:

SQL
INSERT INTO customers VALUES (1, 'Robert California', '123 Sunny St, AZ', POINT(34.411275716904406,-111.6783709992531));

This will add the first record for Robert California into our database. But if we go and run a SELECT statement on that data, our output will look like the following:

id (SERIAL)full_name (TEXT)address (TEXT)location (POINT)
1Robert California123 Sunny St, AZ0x00000000010100000027DFC4AEA43441403416326E6AEB5BC0

The reason for this is because MySQL can store spatial data in multiple different formats. If we wanted to get the actual coordinates for our location, we'd have to use a spatial operator function like ST_asText, which would provide the results we want:

SQL
SELECT id, full_name, address, ST_asText(location) FROM customers;
id (SERIAL)full_name (TEXT)address (TEXT)location (POINT)
1Robert California123 Sunny St, AZPOINT(34.411275716904406,-111.6783709992531)

If we didn't want to work with spatial functions like this in MySQL, we could always break out the location into two columns. For example, location_latitude and location_longitude, and store the individual points as decimal data types. But the drawback here is that we would be required to do a little more work on our client to keep track of the faux spatial data types, and we also couldn't utilize any of the spatial functions provided by MySQL.

The orders table in PostgreSQL looks like the following:

SQL
CREATE TABLE orders
(
id UUID default gen_random_uuid(),
customer INTEGER,
products JSONB
);

And our data in the orders collection looks like:

id (UUID)customer (INT)product (JSONB)
10d4e52d-68fd-44f5-bf9b-a960cfb03de11[{"product": "Achieving PlanetScale", "price": 50}]
3de3f1a5-7a90-4273-a845-7129183edd472[{"product": "Achieving PlanetScale", "price": 50}, {"product": "The Database that Could" "price": 100}]

Handling the UUID Postgres type in MySQL

In this table, we have two fields that MySQL does not natively support, but that doesn't mean we can't have a successful migration. The UUID data type is essentially a 36 character long string. So we can set this data type to a varchar(36) in MySQL. MySQL also supports generating UUID's via a UUID() function, so when it comes to adding new records, we could utilize the UUID() function that behaves similarly to Postgres's gen_random_uuid() function.

Handling the Postgres jsonb type in MySQL

The products column, on the other hand, is of type jsonb, or binary JSON. While MySQL at the moment does not have support for the jsonb data type, it does have support for json, so if we set the data type for the products column as json, we'll get a very similar experience. While jsonb may be a more efficient way to store JSON data in a SQL database, know that even with MySQL, you can still leverage the JSON data type and do things like index JSON columns, update and modify the JSON values, return subsets of the JSON data, and much more.

The above examples explore just a few scenarios that you may run into when deciding to migrate from Postgres to MySQL. At the end of the day, migration from one database to another is not impossible, you just have to understand the differences between the two, and also understand the benefits you'll gain when you do migrate. Once you do migrate, ensure that you are following that database's best practices to get the most out of the migration. Below are a couple of other migration factors to consider before making the switch.

Other migration factors going from Postgres to MySQL

Before migrating from Postgres to MySQL, it’s important to be aware of some differences between the two database systems that may pose an issue. We’ve listed some of these below.

Complications arising from certain data models

MySQL and Postgres both share support for many data types. This ranges from traditional SQL types like String, Boolean, Integer, and Timestamp to complex data structures such as JSON, XML, and TEXT. However, it's good to keep in mind there are some Postgres data types that MySQL does not support. You can see some more information about these in the table at the top of this article.

So, even though MySQL supports the various traditional SQL types required by a variety of applications to store and process different kinds of data, such as Date, Timestamp, Character, Long Text, Float and Decimal, and Blob, potential complications may arise when trying to migrate complex data structures that may not yet be supported in MySQL. Fortunately, these issues are being addressed in newer MySQL releases.

Differences in database and SQL capabilities

Certain operations are carried out differently in MySQL and Postgres databases. Also, some features may not be supported in one while supported in the other. Knowing these may help you avoid some common pitfalls.

DROP a temporary table

Though CREATE TEMPORARY TABLE is used to create a temporary table in both MySQL and Postgres databases, only MySQL has the TEMPORARY keyword in the DROP TABLE statement. This makes it possible to drop only the temporary table in MySQL without affecting the main table.

In Postgres, this omission requires you to be more careful with your naming convention because a temporary table may have the same name as a regular table. And since you can't specify TEMPORARY in your DROP TABLE statement, you might unintentionally delete important tables.

CASCADE when truncating a table

Postgres’ TRUNCATE TABLE statement for truncating a table supports the CASCADE keyword. It also supports features like RESTART IDENTITY, CONTINUE IDENTITY, transaction-safe, and so on. RESTART IDENTITY tells Postgres to reset all identity columns when truncating a table automatically, and transaction-safe means that the truncation will be safely rolled back if the surrounding transaction doesn’t commit.

In contrast, MySQL’s TRUNCATE TABLE feature supports neither CASCADE nor transaction safe, which means that data can’t be rolled back once it’s deleted. These features are likely to be added in future MySQL releases.

Stored procedures

MySQL requires that the procedure is written in the standard SQL syntax. This contrasts with Postgres, where the Procedures are based on functions and can be written in other languages, such as Ruby, Perl, SQL, Python, JavaScript, and others.

Postgres extensions

If you're using extensions in Postgres, it can make a migration even trickier, so make sure to audit each of these individually before migrating.

Case sensitivity and IF/IFNULL support

Postgres table and column names are case sensitive if in double quotes. MySQL table and column names, on the other hand, aren't case sensitive. So when doing a migration, make sure to keep this in mind.

Moreover, MySQL allows for IF and IFNULL statements for evaluating conditions, while Postgres doesn’t.

Conclusion

Migrating between one SQL database to another, such as going from Postgres to MySQL, on the surface seems like it would be very straightforward, but the two database technologies are quite different in how they handle and store certain data types, features they do and don't support, and benefits they provide to your application.

While Postgres may have more features out of the box, MySQL is typically more performant for common use cases. Additionally, MySQL has some fantastic options, such as Vitess and PlanetScale, for scaling massive databases.

Understanding the differences between the two technologies and how they fit into your specific use case is the number one thing you should consider before choosing either, or migrating from one to the other. I hope that this article gave you some insight into what a migration from PostgreSQL to MySQL might look like.

Happy migrating! :)

Want to get the most out of MySQL with a performant database that doesn’t slow you down?