PostgreSQL vs. MySQL: Is It Difficult to Switch?
So you know PostgreSQL, but the boss has decided to switch to MySQL. What are the differences between these two SQL dialects? We discuss PostgreSQL vs. MySQL in this article.
PostgreSQL has gained popularity in the last decade, and many developers and database specialists learned PostgreSQL as their first SQL dialect. But PostgreSQL is not the only relational database; during your IT career, you will probably be asked to use another SQL dialect – like MySQL.
You may wonder whether you would struggle with MySQL after learning PostgreSQL. In this article, we’ll discuss how difficult it is to switch from PostgreSQL to MySQL.
If you are brand new to MySQL and you want to know all the fundamentals, I strongly recommend you try our complete SQL from A to Z in MySQL track. It will give you the full working proficiency you need to boost your career.
A Byte of History
Computer scientist Michael Stonebraker started developing Postgres (an evolution of an earlier database system and the forerunner of PostgreSQL) in the 1980s; in 1996, Postgres became PostgreSQL. You can read more on the history of PostgreSQL elsewhere in our blog.
MySQL was created by David Axmark and Michael Widenius as an improved version of the relational database management system mSQL. They founded the Swedish company MySQL AB to further develop the language; in 1995, the first version was released. In 2008, Sun Microsystems acquired MySQL AB and became the formal owner of the MySQL copyright and trademark. In 2010, Oracle became the new owner of MySQL through their acquisition of Sun Microsystems.
MySQL works on most operating systems, including Windows, macOS, Linux, and AIX. In its 25+ years, MySQL has known several changes; it’s currently on version 8.0. According to Stack Overflow, MySQL is the most popular database system among professionals and non-professionals, followed closely by PostgreSQL.
If you want to begin working with MySQL, I recommend the SQL Basics in MySQL course on LearnSQL.com.
PostgreSQL vs. MySQL
Both PostgreSQL and MySQL use the standard SQL query language. However, each database system uses its own version of the SQL language – i.e. they are SQL dialects. In linguistics, dialects are generally mutually comprehensible. Does this apply to PostgreSQL and MySQL? Let’s do some comparisons to find out.
Data Types
Both PostgreSQL and MySQL use the basic SQL data types like INTEGER
, TEXT
, and VARCHAR
, though with slight differences:
- The built-in
BOOLEAN
type exists in PostgreSQL but not in MySQL. You can use either the BIT type or the TINYINT type instead. Note: MySQL lets you useBOOL
orBOOLEAN
in SQL statements and transforms it intoTINYINT(1)
automatically. - The integer type is called
INT
in both MySQL and PostgreSQL. However, PostgreSQL also accepts the aliasesINTEGER
andINT4
. - To manage date and time separately, both PostgreSQL and MySQL provide the types
DATE
andTIME
. However, the data type to manage date and time together isTIMESTAMP
in PostgreSQL andTIMESTAMP
orDATETIME
in MySQL. - To manage floating numbers, PostgreSQL uses the types
REAL
andDOUBLE PRECISION
(4 and 8 bytes, respectively), while MySQL usesFLOAT
andDOUBLE
. - Both PostgreSQL and MySQL use the type
SMALLINT
for signed two-byte integers, but MySQL further provides the typeTINYINT
for one-byte integers.
Description | PostgreSQL | MySQL |
---|---|---|
Boolean | BOOLEAN | BIT / TINYINT |
Integer | INT / INTEGER / INT4 | INT |
Date and time | TIMESTAMP | DATETIME / TIMESTAMP |
Floating number | REAL / DOUBLE | FLOAT / DOUBLE |
Small Integer | SMALLINT | TINYINT / SMALLINT |
Syntax
Now, let’s check query syntax by analyzing common SQL statements in MySQL and PostgreSQL.
1. Database Creation
In PostgreSQL, character encoding may be specified during the creation of the database:
CREATE DATABASE Plants WITH ENCODING = 'UTF8' ; |
Instead of directly specifying the character encoding, MySQL uses a combination of CHARACTER SET
and COLLATE
:
CREATE DATABASE Plants CHARACTER SET utf8 COLLATE utf8_general_ci; |
2. Table Creation
Creating a table either dialect is pretty similar. However, PostgreSQL provides the pseudotype SERIAL
for creating unique identifier columns, while MySQL uses AUTO_INCREMENT
.
There is also a difference in foreign keys: PostgreSQL uses REFERENCES
to connect a column and a foreign table, whereas MySQL uses a combination of CONSTRAINT
, FOREIGN KEY
, and REFERENCES
.
In PostgreSQL:
CREATE TABLE Fruits ( id SERIAL PRIMARY KEY , name VARCHAR (25) UNIQUE , shape VARCHAR (25), color VARCHAR (25) DEFAULT 'Red' , details INT REFERENCES FruitDetails(fruit_details_id) ) ; |
In MySQL:
CREATE TABLE Fruits ( id INT AUTO_INCREMENT PRIMARY KEY , name VARCHAR (25) UNIQUE , shape VARCHAR (25), color VARCHAR (25) DEFAULT 'Red' , details INT , CONSTRAINT fk_fruit_details FOREIGN KEY (fruit_details_id) REFERENCES FruitDetails(fruit_details_id) ); |
3. INSERT Statement
The INSERT
statement is identical in both SQL dialects:
INSERT INTO Fruits ( name , shape, color) VALUES ( 'Apple' , 'Round' , 'Red' ), ( 'Banana' , 'Cylinder' , 'Yellow' ), ( 'Pear' , 'Ovaloid' , 'Yellow' ) ; |
4. SELECT Statement
The SELECT
statement is also identical in MySQL and PostgreSQL:
SELECT id, name AS "Fruit Name" , shape AS "Shape" , color AS "Color" FROM Fruits LIMIT 10 OFFSET 5; |
However, MySQL also allows the use of comma to separate the limit and the offset values:
SELECT id, name AS "Fruit Name" , shape AS "Shape" , color AS "Color" FROM Fruits LIMIT 5, 10; |
5. UPDATE Statement
The UPDATE
statement is also identical in both dialects:
UPDATE Fruits SET color = 'Green' WHERE name = 'Apple' ; |
6. Modifying a Column Type
To modify a column type, both dialects use the ALTER TABLE
statement. However, there is a slight difference.
In PostgreSQL, ALTER COLUMN
modifies the shape column:
ALTER TABLE Fruits ALTER COLUMN shape TYPE VARCHAR (30); |
In MySQL, MODIFY COLUMN is used:
ALTER TABLE Fruits MODIFY COLUMN shape VARCHAR (30); |
7. Modifying a Column Default Value
Both PostgreSQL and MySQL require you to use ALTER TABLE
and ALTER COLUMN
to set or modify a default value:
ALTER TABLE Fruits ALTER COLUMN color SET DEFAULT 'Green' ; |
IDEs
There are plenty of integrated development environments (IDEs) for PostgreSQL; the most popular is pgAdmin. For MySQL, the official IDE is MySQL Workbench.
Both IDEs are visually similar: the database hierarchy is shown on the left, with tables and views. The center part shows the currently open SQL file with its queries, while the bottom panel shows the query result.
pgAdmin, the most popular PostgreSQL IDE
MySQLWorkbench, the official MySQL IDE
PostgreSQL vs. MySQL: There Is Nothing to Fear!
In this article, we have shown that PostgreSQL vs. MySQL isn’t really much of a change; the differences are slight. Both are SQL dialects, so if you know PostgreSQL, you won’t have a problem picking up MySQL.
Don’t be afraid to learn a new SQL dialect! The learning curve is relatively short if you switch from using PostgreSQL to using MySQL. With your previous knowledge of the SQL standard, taking a quick look at the technical documentation will clarify any differences.