The basic differences when working with data bases MySQL and PostgreSQL Amateurish browse
Continuing their familiarity with the PostgreSQL database, with existing skills in MySQL database and found a number of interesting and useful features which in practice is often not enough in MySQL. The objective of this review is not to create endless debate which is better, but to give a slight comparison that is usually discussed by programmers on a lunch break at the nearest cafe. In comparison gains new knowledge and experience, so it's worth it.
1. Vacancies many companies quite often write the knowledge required by a slash MySQL/PostgreSQL
In my opinion this is a completely different database and so just putting a slash between them given only in the attention of writing similar SQL queries is not entirely correct. Still a couple of months for PostgreSQL to start to feel confident in the psql client after MySQL.
What I would highlight to compile from source these two databases,
1.1 PostgreSQL does not have the types of engines (MySQL — innodb, mysql, archive, etc.), but has a lot of extensions, like PHP, which you can optionally put, expanding opportunities. The impression that PostgreSQL is a kind of frame, which is stuffed with functionality.
1.2 deployment of a MySQL server to remain confined essentially to run the server (systemctl start mysql.conf service mysql start), whereas in PostgreSQL you need to create a separate user (the operating system) to start the server, deploy a separate cluster (cool word, but it is essentially the same as in MySQL — multiple databases on the same server)
1.3 Physical location of the new tables on disk (tablespace) level of SQL for PostgreSQL.
etc.
2. Differences in clients when queries to the database — psql and mysql.
2.1 What is clearly missing in MySQL is the sort of team \watch in psql that allows it, putting seconds to rerun the SQL query (similar to utilities watch-n). It is usually convenient in order to track the migration (filling the data in the tables)
the
2.2 In PostgreSQL by default, all running queries do not display execution time, in contrast to MySQL, you need to specify a command \timing. Reissue the command disables the option. This technique is often found in many settings, PostgreSQL, unlike MySQL, where you need to write more longer. Thus, in PostgreSQL, when you look at the help information, close the round brackets displays the current value of the viewed settings. Very convenient. Only bad thing is that one font test is not immediately visually perceived quickly.
2.3 In PostgreSQL, you can quickly view the history of queries from psql command \s, whereas in the MySQL client you need to use the up/down keys activates the functionality of the readline library (or to view the history of commands, separately from the mysql client). It is often necessary, when testing a re-query to use indexes. It would be more convenient in PostgreSQL after a set of \s instead of copying the request, dial the number of the request as is done with profile in MySQL.
3. There are many General points which, for example, in MySQL and in PostgreSQL is more complex.
For example, the output of the select result, which does not fit horizontally. Both database clients has a vertical output. For MySQL it is sufficient to add to the end of the query, \G, while in PostgreSQL in the beginning you need to implement \pset expanded. When you need to be quick to see the version of PostgreSQL in my opinion it is not very convenient.
4. A particularly interesting point in PostgreSQL, unlike MySQL, it's closer integration with the bash shell.
From psql to execute a shell command (similar to vim the editor :! pwd), save the result in variables and then used in generating SQL queries. In MySQL it's possible to make one, but longer and not always comfortable ways.
5. PostgreSQL stands out with special affection to the use of environment variables (export) unlike MySQL.
You feel it immediately after you've compiled the source code and start to “deploy” the server, indicating the path to the database directory (-D or PGDATA).
This I think, to finish their quick Amateur review. As I wrote, the goal is not positioning of one or the other database, and to obtain additional experience through comparison. For myself specifically the study of PostgreSQL is a competitive technical advantage.
Article based on information from habrahabr.ru
1. Vacancies many companies quite often write the knowledge required by a slash MySQL/PostgreSQL
In my opinion this is a completely different database and so just putting a slash between them given only in the attention of writing similar SQL queries is not entirely correct. Still a couple of months for PostgreSQL to start to feel confident in the psql client after MySQL.
What I would highlight to compile from source these two databases,
1.1 PostgreSQL does not have the types of engines (MySQL — innodb, mysql, archive, etc.), but has a lot of extensions, like PHP, which you can optionally put, expanding opportunities. The impression that PostgreSQL is a kind of frame, which is stuffed with functionality.
1.2 deployment of a MySQL server to remain confined essentially to run the server (systemctl start mysql.conf service mysql start), whereas in PostgreSQL you need to create a separate user (the operating system) to start the server, deploy a separate cluster (cool word, but it is essentially the same as in MySQL — multiple databases on the same server)
1.3 Physical location of the new tables on disk (tablespace) level of SQL for PostgreSQL.
etc.
2. Differences in clients when queries to the database — psql and mysql.
2.1 What is clearly missing in MySQL is the sort of team \watch in psql that allows it, putting seconds to rerun the SQL query (similar to utilities watch-n). It is usually convenient in order to track the migration (filling the data in the tables)
the
select NOW() \watch 1;
2.2 In PostgreSQL by default, all running queries do not display execution time, in contrast to MySQL, you need to specify a command \timing. Reissue the command disables the option. This technique is often found in many settings, PostgreSQL, unlike MySQL, where you need to write more longer. Thus, in PostgreSQL, when you look at the help information, close the round brackets displays the current value of the viewed settings. Very convenient. Only bad thing is that one font test is not immediately visually perceived quickly.
2.3 In PostgreSQL, you can quickly view the history of queries from psql command \s, whereas in the MySQL client you need to use the up/down keys activates the functionality of the readline library (or to view the history of commands, separately from the mysql client). It is often necessary, when testing a re-query to use indexes. It would be more convenient in PostgreSQL after a set of \s instead of copying the request, dial the number of the request as is done with profile in MySQL.
3. There are many General points which, for example, in MySQL and in PostgreSQL is more complex.
For example, the output of the select result, which does not fit horizontally. Both database clients has a vertical output. For MySQL it is sufficient to add to the end of the query, \G, while in PostgreSQL in the beginning you need to implement \pset expanded. When you need to be quick to see the version of PostgreSQL in my opinion it is not very convenient.
4. A particularly interesting point in PostgreSQL, unlike MySQL, it's closer integration with the bash shell.
From psql to execute a shell command (similar to vim the editor :! pwd), save the result in variables and then used in generating SQL queries. In MySQL it's possible to make one, but longer and not always comfortable ways.
5. PostgreSQL stands out with special affection to the use of environment variables (export) unlike MySQL.
You feel it immediately after you've compiled the source code and start to “deploy” the server, indicating the path to the database directory (-D or PGDATA).
This I think, to finish their quick Amateur review. As I wrote, the goal is not positioning of one or the other database, and to obtain additional experience through comparison. For myself specifically the study of PostgreSQL is a competitive technical advantage.
Комментарии
Отправить комментарий