Tag: postgres vs mysql

Today, multiple flavors of SQL databases exist with versatile use cases, like MySQL and Postgres databases. It ends up being a hard choice: which SQL database do you want to use, which SQL database does your organization use; which SQL database is in the most demand? Our post Picking The Right SQL Database For You will help you pick the right choice for you. For the purposes of this post, we will compare PostgreSQL and MySQL databases, two of the most popular databases today.

I believe it's important to be a jack-of-all-trades in the competitive world we live in. If career opportunities isn't a big enough attraction, know that you will be able to proudly claim you are a master of two of two of the most popular SQL databases. Isn't that an amazing thing to boost to your friends?

What are PostgreSQL Databases?

PostgreSQL, also known as Postgres, is an open source object-based relational database management system (RDBMS). Moreover, its queries are natively run on pgAdmin, its GUI client. In addition, PostgreSQL is a great database language for beginners because of its broad applicability. The beauty of PostgreSQL lies in its close association to the P/SQL. Hence, the focus here is on extensibility and it offers great concurrency mechanisms through MVCC. Furthermore, PostgreSQL is default database for MacOS.

What are MySQL Databases?

MySQL builds on the original SQL, as an open-source relational database management system (RDBMS). In addition, its queries are run on the MySQL Command Line Interface (CLI) or on MySQL Workbench. MySQL is widely used today because of its simple syntax and high speed-- being one of the most popular databases today.

Why Not Other SQL Databases?

You might be wondering why I am expecting you to master these two SQL databases before moving to other flavors or types of databases-- your question is warranted. My experience has been that you should move with the flow and most times, things work out. After that, let me share an anecdote here to help explain why.

A few years ago, when I was starting my freelance career, I was posed with the challenge of designing up a database in Postgres. Now, up until that point I was only accustomed with MySQL and Oracle only-- I hadn't actually ever written queries in Postgres. It was scary as hell! The client was sure of their choice of SQL database and it's safe to say my palms were sweating, haha. However, the client cooperated, helped me through the hurdles and I used Google and FreeCodeCamp to learn that MySQL and Postgres are not dissimilar. I literally felt on the top of the world, you know? Postgres was always that buzzword I wanted to learn but never found the time to shift. In reality, it was super easy to move over and master Postgres, in time.

A Brief Overview: PostgreSQL vs MySQL Databases

Having the experience of writing queries in both SQL database flavors, I am going to tell you some of the key changes between them.

Firstly, it is important to understand that PostgreSQL is closer to Procedural SQL than MySQL.

Secondly, PostgreSQL supports all data types supported by PL/SQL unlike MySQL. Adding to that, Postgres is case-sensitive unlike MySQL. Therefore, what it means for you is that in Postgres you must take care that you capitalize the string names as necessary. This can be a key difference in terms of query writing, if you're used to case-insensitivity.

Postgres has built in support for a wide variety of data types including NoSQL whereas MySQL databases do not support NoSQL database types such as JSON. This is of particular importance.

Moreover, the way of indexing in the databases vary slightly (which we will explore later in this article).

Furthermore, Postgres is object relational database management system which allows for defining objects and inheritance. The ORDBMS model facilitates complex queries but is slower for select statements or read operations. In comparison, MySQL is just a RDBMS and does not support object definition.

On the other hand, MySQL really excels in read operations-- an important distinction to know if you're studying to be a DBA.

After that, let's look at some key operations in both these databases for a more visual comparison.

Key Operations Comparison: PostgreSQL and MySQL Databases

In this portion of the blog, let's explore some key operations and compare their queries in both Postgres and MySQL databases. This will help you visualize the differences and similarities.

Creating a New User in SQL Databases

One of the most significant and common steps on a database is to create a new user to assign roles and for better collaboration and security for the relations. This means you can assign different "privileges'" based on the roles of employees, for instance.

MySQL

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

Then, fill in the credentials that you want to create.

After that, let's verify if this user was created in our database.

SELECT user FROM mysql.user;

PostgreSQL

In Postgres, this is how to create a new user:

CREATE ROLE username LOGIN PASSWORD 'password';

Grant Privileges in SQL Databases

This refers to the privilege you assign to someone based on roles in an organization or company. It is useful to build a wall to limit access to the information in your databases to people tasked with handling them. For example, a database admin has complete control whereas a low-level employee may only have been granted the privilege to view data relevant to them.

MySQL

After that, let's look at how to grant privileges is MySQL.

GRANT ALL PRIVILEGES ON database_name TO 'user'@'localhost';

Hence, the syntax for such a query in MySQL relies on the database name for which you want to grant privilege and the user name.

PostgreSQL

Similarly, in PostgresSQL, this is how to write the same version of grant privilege query. Therefore, it's almost identical to the one above, except for the keyword "DATABASE".

GRANT ALL PRIVILEGES ON DATABASE database_name TO user;

Drop User in SQL Database

Drop User means to remove a user from the directory of a schema. This is useful for removing people who don't work at your company anymore or if you accidentally created a user.

MySQL

In MySQL, a user can be dropped with this syntax:

DROP USER 'user_name'@'localhost';

PostgreSQL

In PostgreSQL, this is the syntax for dropping a user:

REVOKE ALL PRIVILEGES ON DATABASE database_name FROM "user_name";
DROP USER user_name;

As you may see above, it is necessary to revoke all the privileges we assigned above for the drop user query to run successfully in PostgreSQL. Therefore, it's more of a work to remove users in PostgreSQL once you have granted privileges.

Indexes in SQL Databases

Indexes to speed up query results in databases for large tables of data. They are highly effective in increasing the efficiency of a database-- by loading desired results quicker. Without indexes, all the data is traversed for the required output, which is slower since it must read through everything up until query is matched.

However, know that you must be careful with indexes. Improper or unsuitable usage of indexes i.e. for columns that mustn't be accessed repeatedly is ill-advised. In fact, if I may be blunt, spend a long time on improving indices because many issues originate from here. Cardinality is a key concept you should look into if you want to understand indexes.

MySQL

In MySQL, the syntax for creating index is:

CREATE INDEX index_name
    ON table_name(col_name)

PostgreSQL

In PostgreSQL, the query syntax for index creation is as follows:

CREATE INDEX index_name 
    ON table_name (column_name [ASC | DESC]);

Select Statement in SQL Databases

Select Statement returns the results from a database by writing queries with certain conditions and rules. Hence, it allows us to view the data stored in our SQL databases.

MySQL

In MySQL, the format for a select statement is the following.

SELECT col_name FROM table_name WHERE condition(s);

PostgreSQL

In PostgreSQL, this is the way of select statements.

SELECT col_name FROM table_name WHERE condition(s);

Login User in SQL Databases

Logging in a user means to access the privileges and relations or schemas linked with it. Enter these commands in the command-line interfaces (CLI). This will allow you to query based on the privilege assigned which is useful.

MySQL

mysql -u user -p

PostgreSQL

psql -U username -p password

List Databases in SQL Databases

It's quite handy to know how to list all the current databases when you have been working with a ton of databases for different purposes. It's easier to see the list of databases than have to memorize them all.

MySQL

In MySQL Command Line Interface, writing this command will return the list of databases

SHOW DATABASES;

PostgreSQL

In PostgreSQL, you have two ways to access the database list:

• PSQL
• Postgres Queries

list or \l: list all databases
SELECT datname
FROM pg_database

List Tables in SQL Databases

When you take over a new project/database schema, you want to see the list of tables in each database. This reflects on the type and quality of content in the tables and it familiarizes one with the table schema to understand the database better.

MySQL

In MySQL, you must specify a database before querying to see the list of all tables in that database

Prerequisite: USE Database_Name. This command allows you to see the tables belonging to a particular database. It's not necessary when you're already using the same database.

SHOW TABLES;

PostgreSQL

/c database_name
/dt

SELECT * FROM pg_catalog.pg_tables

Connection With Python in SQL Databases

SQL database connection with a programming language is essential today to connect a database to a programming language for APIs and the like. Moreover, it gives you more freedom to write queries with Python for your SQL database.

MySQL

import mysql.connector as mysql
mydb = mysql.connect(
  host="localhost",
  port = 3306,
  user="sesame",
  password="opense",
)

PostgreSQL

conn = psycopg2.connect(
    host="localhost",
    database="suppliers",
    user="postgres",
    password="Abcd1234")

Timestamp in SQL Databases

Timestamp function returns the current time of the machine that calls the query. It is a trivial, yet useful function since some timestamp functions vary in their use-cases. For instance, you may want to know the timestamp of a server so there is a timestamp function to do so.

MySQL

SELECT CURRENT_TIMESTAMP;

PostgreSQL

Technically, the CURRENT_TIMESTAMP() implements as NOW() in Postgres.

SELECT CURRENT_TIMESTAMP;
SELECT NOW();

Concatenation in SQL Databases

Concatenation is a useful operation to know in SQL for the purposes of formatting queries. Imagine a situation where you're expected to return the full names of an employee but you have two columns for that. Instead of creating a new column, use concatenation and return the name in real-time, making your life easy.

MySQL

Syntax for Concat in MySQL is:

SELECT CONCAT(first_name, " ", last_name) AS Name FROM authors

PostgreSQL

PostgreSQL's syntax for concat is:

SELECT 'Concat with ' || 'this' AS result_string;
SELECT CONCAT(first_name, ' ', last_name) AS Name FROM authors;

Boolean Data in SQL Databases

This is useful for the data types where you must answer in binary values- 0 or 1; Y or N. This is useful for situations when you only need a binary answer, for example an attribute with a yes or no answer.

MySQL

TINYINT(1)

PostgreSQL

BOOLEAN

Conclusion

The aim of this post was to provide you with a comprehensive comparison of key SQL operations. In other words, this post means to make your life easier switching between SQL databases for your work. As such, you can follow this or bookmark this to reference whenever you need to make any of these operations in a different SQL database.

Working in either database is easy and rewarding. PostgeSQL can be preferred for its closeness to PSQL and for the fact that it is open-source. MySQL is used for its high performance and familiarity.

In the future, you may want to explore some newer and exciting options like GraphQL. Eventually, you may want to move towards multi-model databases like Fauna that combine Graph schemas with document relation designs. Fauna is particularly exciting since it carries a lot of potential and ease-of-access for you.

To continue with more specifics, check out the oficial docs; Postgres and MySQL

If you like this post, drop a comment and share your feedback and your thoughts. I am waiting to hear from you. Have a great day!