16th Aug 2022 6 minutes read

PostgreSQL vs. MySQL: Is It Difficult to Switch?

Table of Contents

A Byte of History
PostgreSQL vs. MySQL
- Data Types
- Syntax
- IDEs
PostgreSQL vs. MySQL: There Is Nothing to Fear!

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 use BOOL or BOOLEAN in SQL statements and transforms it into TINYINT(1) automatically.
The integer type is called INT in both MySQL and PostgreSQL. However, PostgreSQL also accepts the aliases INTEGER and INT4.
To manage date and time separately, both PostgreSQL and MySQL provide the types DATE and TIME. However, the data type to manage date and time together is TIMESTAMP in PostgreSQL and TIMESTAMP or DATETIME in MySQL.
To manage floating numbers, PostgreSQL uses the types REAL and DOUBLE PRECISION (4 and 8 bytes, respectively), while MySQL uses FLOAT and DOUBLE.
Both PostgreSQL and MySQL use the type SMALLINT for signed two-byte integers, but MySQL further provides the type TINYINT 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.

Tags: