Basic differences when working with MySQL and PostgreSQL databases Amateurish overview

Continuing my acquaintance with the PostgreSQL database, with the existing skills of working in the MySQL database, I discovered a number of interesting and useful features that in practice were often lacking in MySQL. The purpose of this review is not to create an endless dispute, which is better, but to give an easy comparison, which is usually discussed by programmers at the lunch break in the nearest cafe. In comparison, gaining new knowledge and experience, so it's worth it.

1. Vacancies of many companies often write the required knowledge through a slash MySQL / PostgreSQL

In my opinion, these are completely different databases and, therefore, simply putting a slash between them, taking into account only writing similar SQL queries, is not entirely correct. All the same, it takes a couple of months for PostgreSQL to start feeling confident in the psql client after MySQL.

What I would single out at the compilation stage from the sources of these two databases,
')
1.1 PostgreSQL does not have engine types (MySQL - innodb, mysql, archive, etc.), but has a bunch of extensions, like PHP, that can be additionally installed, expanding the possibilities. It seems that PostgreSQL is a kind of framework that is stuffed with functionality.
1.2 Deploying a MySQL server comes down essentially to starting the server (systemctl start mysql.conf, service mysql start), whereas in PostgreSQL you need to have a separate user (in the operating system) to start the server, deploy a separate cluster (cool word, but in fact the same as in MySQL - several databases on one server)
1.3 Physical indication of the location of new tables on disk (tablespace) at the SQL level for PostgreSQL.

etc.

2. Differences in clients when querying the database - psql and mysql.

2.1 What is clearly not enough in MySQL is the similarity of the \ watch command in psql, which allows you to repeat the execution of an SQL query by specifying seconds (analogue to the watch utility -n). This is usually convenient in order to keep track of how the migration is going (filling data in tables)

select NOW() \watch 1; 

2.2 In PostgreSQL, by default, all executed queries do not display the execution time, unlike MySQL, you need to additionally specify the \ timing command. Repeating the command disables the option. This technique is often found in many PostgreSQL settings, in contrast to MySQL, where it needs to be written longer. At the same time, in PostgreSQL, when you look at the reference information, the current value of the setting you are viewing is displayed next to the round brackets. Very comfortably. The only bad thing is that one font of the test goes, not immediately visually quickly perceived.

2.3 In PostgreSQL, you can quickly view the query history from psql with the \ s command, whereas in the MySQL client you need to use the up / down keys using the readline library functionality (or watch the command history separately from the mysql client). This is often necessary when testing a re-use query for indexes. It would be more convenient in PostgreSQL, after dialing \ s instead of copying the query, to dial the query number, as is done with the profile in MySQL.

3. There are many common points that, for example, are more convenient in MySQL, and more complex in PostgreSQL.

For example, the output of a select result that does not fit horizontally. Both database clients have a vertical output. For MySQL, it is enough to add \ G to the end of the query, whereas in PostgreSQL you need to perform \ pset expanded at the beginning. When you need to quickly review, the PostgreSQL option in my opinion is not very convenient.

4. A particularly interesting point in PostgreSQL, unlike MySQL, is closer integration with the bash shell.

You can execute shell commands from psql (like in the vim editor:! Pwd), save the result to variables and then use it in generating SQL queries. In MySQL, this can also be done, but in longer and not always convenient ways.

5. PostgreSQL stands out for the special use of environment variables (export) as opposed to MySQL.

You feel it right after you compiled the sources and start to “deploy” the server, indicating the path to the database directory (-D or PGDATA).

On it I think, to finish the fluent amateurish review. As I already wrote, the goal is not to position a particular database, but to gain additional experience through comparison. For myself, specifically learning PostgreSQL is an additional competitive technical advantage.