在學習具體的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.