Migrating from Postgres to MySQL

By Adnan Kukic | February 9, 2023

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:

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:

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

So the data could be represented as:

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:

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:

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:

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:

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:

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:

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:

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:

SELECT id, full_name, address, ST_asText(location) FROM customers;

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:

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

And our data in the orders collection looks like:

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! :)