在学习具体的Postgresql用法之前，我们来思考一下Postgresql与目前最常用的关系型数据库MySQL的区别在哪里，主要集中在两者的底层结构、使用场景和特别之处。
1.前言
当我们去选择项目使用的数据库的时候，应该要考虑该项目的应用场景、数据规模等因素。每一种数据库都有它特定的应用场景，比如我们现在要讨论的Postgresql和MySQL数据库，这两种的数据库的应用场景就有所不同，下面我们就它们的相同点和不同点进行讨论。
2.Postgresql vs MySQL
此表来源于postgresqltutorial，详细区别了两者的不同点。
| 特性 | Postgresql | MySQL |
|---|---|---|
| 描述 | The world’s most advanced open source database | The world’s most popular open source database |
| 发展 | PostgreSQL is an open source project | MySQL is an open-source product |
| 实现语言 | C | C、C++ |
| 图形化工具 | PgAdmin | MySQL Workbench |
| ACID | Yes | Yes |
| 存储引擎 | Single storage engine | Multiple storage engines e.g., InnoDB and MyISAM |
| 全文检索 | Yes | Yes |
| Drop a temporary table（删除一个临时表） | No TEMP or TEMPORARY keyword in DROP TABLE statement（随着数据库的连接的断开而被删除） |
MySQL supports the TEMP or TEMPORARYkeyword in the DROP TABLE statement that allows you to remove the temporary table only.（需要手动删除） |
DROP TABLE（删除表） |
Support CASCADE option to drop table’s dependent objects e.g., tables, views, etc.,（级联操作：也就是更新、删除父表，将会同步更新、删除子表；而反过来则不变） |
Does not support CASCADE option |
TRUNCATE TABLE（删除表） |
PostgreSQL TRUNCATE TABLEsupports more features like CASCADE, RESTART IDENTITY, CONTINUE IDENTITY, transaction-safe, etc.（对于移除表中的数据，delete是可以的，但是对于一个大表，truncate是更加有效的方式，因为truncate删除表中所有行的时候不需要扫表整个表） |
MySQL TRUNCATE TABLE does not support CASCADE and transaction safe i.e,. once data is deleted, it cannot be rolled back.（永久性删除，不可以撤销） |
| 自动增加列 | SERIAL |
AUTO_INCREMENT |
| 解析功能 | Yes | No |
| Data types | Support many advanced types such as array, hstore, and user-defined type. | SQL-standard types |
| Unsigned integer | No | Yes |
| Boolean type | Yes | Use TINYINT(1) internally for Boolean |
| IP address data type | Yes | No |
| 设置列默认值 | Support both constant and function call | Must be a constant or CURRENT_TIMESTAMP for TIMESTAMP or DATETIME columns |
| CTE（Common Table Expressions） | Yes | No |
EXPLAINoutput |
More detailed | Less detailed |
| Materialized views（物化视图） | Yes（Postgresql将视图概念扩展到下一个级别，允许视图在物理上存储数据，我们将这些视图称为物化视图，物化视图会缓存复杂的查询结果，然后允许定期刷新此结果） | No |
| CHECK constraint（检查约束） | Yes | No (MySQL ignores the CHECK constraint) |
| Table inheritance（表继承） | Yes | No |
| Programming languages for stored procedures | Ruby, Perl, Python, TCL, PL/pgSQL, SQL, JavaScript, etc. | SQL:2003 syntax for stored procedures |
FULL OUTER JOIN（全外连接） |
Yes | No |
INTERSECT |
Yes（Postgresql的INTERSECT运算符将两个或多个SELECT语句的结果集合并到一个结果集中） | No |
EXCEPT |
Yes（Except运算符通过比较两个或多个quires的结果集来返回行，此返回行存在于第一查询子句而不存在第二查询子句中） | No |
| Partial indexes（部分索引） | Yes | No |
| Bitmap indexes（位图索引） | Yes | No |
| Expression indexes（表达式索引） | Yes | NO |
| Covering indexes（覆盖索引） | Yes (since version 9.2)例子1、例子2 | Yes. MySQL supports covering indexes that allow data to be retrieved by scanning the index alone without touching the table data. This is advantageous in case of large tables with millions of rows. |
| Common table expression (CTE) | Yes | Yes. (since version 8.0, MySQL has supported CTE) |
| Triggers（触发器） | Support triggers that can fire on most types of command, except for ones affecting the database globally e.g., roles and tablespaces. | Limited to some commands |
| Partitioning（分区） | RANGE, LIST | RANGE, LIST, HASH, KEY, and composite partitioning using a combination of RANGE or LIST with HASH or KEY subpartitions |
| Task Schedule（任务定时） | pgAgent | Scheduled event |
| Connection Scalability（连接规模） | Each new connection is an OS process（进程） | Each new connection is an OS thread（线程） |
| SQL compliant（SQL兼容性） | PostgreSQL is largely SQL compliant. | MySQL is partially SQL compliant. For example, it does not support check constraint. |
| Best suited | PostgreSQL performance is utilized when executing complex queries. | MySQL performs well in OLAP& OLTP systems when only read speeds are needed. |
| Support for JSON | Support JSON and other NoSQL features like native XML support. It also allows indexing JSON data for faster access. | MySQL has a JSON data type support but does not support any other NoSQL feature. |
| Default values | The default values can be changed at the system level only | The default values can be overwritten at the session level and the statement level |
| B-tree Indexes | B-tree indexes merged at runtime to evaluate are dynamically converted predicates. | Two or more B-tree indexes can be used when it is appropriate. |
| Object statistics | Very good object statistics | Fairly good object statistics |
对于上面有部分疑惑的点进行详细分析：
1、Full outer join全外连接
作用：Postgresql full outer join返回来自两个参与表的所有行，如果他们在相对的表上没有匹配，则使用null填充。full outer join组合了左外链接和右外连接的结果，并返回连接子句两侧表中的所有行（匹配或者不匹配）行。
3.Disadvantages of using MySQL（MySQL的缺点）
- Transactions related to system catalog are not ACID compliant（与系统目录相关的事务不符合ACID）
- Some time A server crash can corrupt the system catalog（有时服务器崩溃可能会破坏系统目录）
- No pluggable authentication module preventing centrally managed account（没有可插入的身份验证模块阻止集中管理账户）
- No support for roles so it is difficult in maintaining privileges for many users（不支持角色，因此很难为许多用户维护权限）
- Stored procedures are not cacheable（存储过程不可缓存）
- Tables used for the procedure or trigger are always pre-locked（用于程序或触发器的表始终是预先锁定的）
4.Disadvantages of using PostgreSQL（Postgresql的缺点）
- The current external solutions require a high learning curve（当前的外部解决方案需要很高的学习曲线）
- No upgrade facility for major releases（没有主要版本的升级工具）
- The data need to be exported or replicated to the new version（需要将数据导出或复制到新版本）
- Double storage is needed during the upgrade process（升级过程中需要双重存储）
- indexes cannot be used to directly return the results of a query（索引不能用于直接返回查询结果）
- Query execution plans are not cached（不缓存查询执行计划）
- Bulk loading operations may become CPU bound（批量加载操作可能会受CPU限制）
- Sparse Independent Software Vendor support（稀疏独立软件供应商支持）
5.What is Better?
After comparing both we can say that MySQL has done a great job of improving itself to keep relevant, but on the other side for PostgreSQL, you don't need any licensing. It also offers table inheritance, rules systems, custom data types, and database events. So, it certainly edges above MySQL.
