PostgreSQL 官方擴充模組

ServBay 作為一套強大的本地 Web 開發環境，整合了多種開發者工具，其中也包含了功能完善的 PostgreSQL 資料庫。PostgreSQL 的強大來源不僅限於其核心功能，還在於豐富的官方擴充生態圈。ServBay 預先安裝了大多數 PostgreSQL 官方擴充模組，讓開發者可以直接在本地環境中輕鬆啟用與使用這些模組，大幅擴展資料庫的能力，靈活滿足各式複雜應用需求。

本文件將介紹多種常用的 PostgreSQL 官方擴充模組，並說明如何在 ServBay 中安裝及使用它們。

常用擴充模組概覽

PostgreSQL 官方提供眾多擴充模組，涵蓋從效能監控到資料型態、全文檢索等多元功能。以下是開發者日常經常遇到且非常實用的常見擴充模組：

1. pg_stat_statements

• 功能：收集並記錄伺服器執行過的所有 SQL 語句統計資訊，例如呼叫次數、總執行時間、處理列數等。
• 用途：協助資料庫管理員與開發者分析查詢效能，識別慢查詢與最佳化瓶頸。
• 安裝命令：

  CREATE EXTENSION pg_stat_statements;

  注意：安裝後通常還需要在 postgresql.conf 配置 shared_preload_libraries = 'pg_stat_statements'，並重啟 PostgreSQL 服務才能完全啟用並開始收集資料。ServBay 提供方便的設定介面讓你快速修改設定。

2. pg_trgm

• 功能：提供基於三元組（trigram）的文字相似度測量函數與索引支援。
• 用途：實現高效的模糊比對、相似查詢與全文搜尋，特別適合處理拼字錯誤或文字變化的情境。
• 安裝命令：

  CREATE EXTENSION pg_trgm;

3. hstore

• 功能：新增一種資料型態，支援在單一 PostgreSQL 欄位中儲存 key-value 鍵值集合。
• 用途：適用於儲存動態屬性、非結構化或半結構化資料，例如設定檔、原始資料或用戶偏好，無須預先定義嚴謹表結構。
• 安裝命令：

  CREATE EXTENSION hstore;

4. citext

• 功能：提供大小寫不敏感的文字字串（case-insensitive text）資料型態。
• 用途：解決需要進行不分大小寫比對的場合，如儲存與比對用戶名稱、電子郵件或標籤，可避免查詢中頻繁使用 LOWER()，簡化應用程式邏輯。
• 安裝命令：

  CREATE EXTENSION citext;

5. uuid-ossp

• 功能：提供多種生成通用唯一識別碼（UUID）的函數，支援多種 UUID 標準版本（如 version 1、3、4、5）。
• 用途：為主鍵、Session ID、分散式系統等需求產生全局唯一識別碼。
• 安裝命令：

  CREATE EXTENSION "uuid-ossp";

  注意：擴充模組名稱含有連字號，執行 CREATE EXTENSION 命令時請以雙引號包覆。

6. intarray

• 功能：為整數陣列（integer arrays）提供額外的處理函數與運算子。
• 用途：適用於需要直接於資料庫層進行整數陣列集合運算（交集、聯集）、查詢、排序等複雜操作的場合。
• 安裝命令：

  CREATE EXTENSION intarray;

7. btree_gin

• 功能：為 B-tree 資料型態（如整數、文字、日期等）增加 GIN（Generalized Inverted Index）索引支援。
• 用途：GIN 索引多用於索引多值資料型態（如陣列、hstore、tsvector），利用 btree_gin 可在其複合資料型態中的 B-tree 欄位上建立 GIN 索引，提升查詢效率。
• 安裝命令：

  CREATE EXTENSION btree_gin;

8. btree_gist

• 功能：為 B-tree 型別增加 GiST（Generalized Search Tree）索引功能。
• 用途：GiST 為一通用索引架構，透過 btree_gist 可在可用 B-tree 型別資料上建立 GiST 索引，適用於需支援多種查詢型態（如範圍、包含等）或多欄位複合索引的場合。
• 安裝命令：

  CREATE EXTENSION btree_gist;

9. cube

• 功能：新增一種資料型態，可表示多維 cube 或點，並提供對應操作符與函數。
• 用途：適合處理多維資料，如簡單地理空間資訊（二維/三維點）、資料倉儲中的多維分析，以及特定科學運算。
• 安裝命令：

  CREATE EXTENSION cube;

10. fuzzystrmatch

• 功能：提供多種函數判斷字串間的相似度或距離，例如 Levenshtein Distance、Soundex、Metaphone。
• 用途：適合字串模糊比對、拼字檢查、資料清理與去重等應用。
• 安裝命令：

  CREATE EXTENSION fuzzystrmatch;

11. plpgsql

• 功能：PostgreSQL 的程式語言，允許用戶撰寫存儲過程、觸發器函數及匿名程式碼區塊。
• 用途：於資料庫伺服器端實作進階商業邏輯、自動化任務、資料處理流程。plpgsql 通常為預設安裝之語言，無需手動安裝，但你仍可用 CREATE EXTENSION 明確建立或重新啟用。
• 安裝命令：

  CREATE EXTENSION plpgsql;

12. tablefunc

• 功能：提供多個函數以處理與轉換整張表格資料，最著名為 crosstab 交叉表。
• 用途：實現交叉表查詢（Pivot Table），將單欄唯一值轉為多欄，常用於報表與資料分析。
• 安裝命令：

  CREATE EXTENSION tablefunc;

13. dblink

• 功能：自一個 PostgreSQL 資料庫內部連結並查詢另一個 PostgreSQL 資料庫。
• 用途：解決跨資料庫查詢、資料遷移等需求，無需在應用層做跨庫邏輯。
• 安裝命令：

  CREATE EXTENSION dblink;

14. postgres_fdw

• 功能：提供外部資料包裝器（Foreign Data Wrapper），能像本地資料表般存取遠端 PostgreSQL 資料表。
• 用途：實現跨資料庫資料整合與聯合查詢，比 dblink 更強大、彈性高，並支援複雜查詢最佳化。
• 安裝命令：

  CREATE EXTENSION postgres_fdw;

15. pgcrypto

• 功能：提供多種加密、解密函數，包括雜湊運算、對稱加密及公鑰加密等。
• 用途：於資料庫層即時完成資料加密、密碼雜湊儲存、簽章驗證等資訊安全相關應用。
• 安裝命令：

  CREATE EXTENSION pgcrypto;

以上僅為 ServBay 所提供 PostgreSQL 官方擴充模組的一部分。ServBay 致力於涵蓋 PostgreSQL 社群幾乎所有主流官方擴充，以滿足開發者多元的開發需求。

在 ServBay 中安裝與使用 PostgreSQL 擴充模組

在 ServBay 環境中安裝並使用 PostgreSQL 擴充模組非常直覺。擴充模組需安裝於特定資料庫，代表每個需要該模組的資料庫都須分別啟用。

前置條件：

• 請確保 ServBay 已運行且 PostgreSQL 資料庫服務已啟動。

操作步驟：

1. 連接目標 PostgreSQL 資料庫： 開啟終端機應用程式（如 macOS 的 Terminal 或 iTerm2），並使用 psql 命令工具連至欲安裝擴充的資料庫。預設情況下，ServBay 安裝的 PostgreSQL 使用者名稱為 postgres，預設資料庫名稱亦為 postgres。

   psql -U your_username -d your_database

   例如，連接預設的 postgres 資料庫：

   psql -U postgres -d postgres

   若連接時需要密碼，請輸入於 ServBay 內設定之 PostgreSQL root/postgres 使用者密碼。

2. 安裝擴充模組： 連線成功後，進入 psql 指令介面。輸入 CREATE EXTENSION 指令安裝想用的擴充模組，將 module_name 替換為模組名稱。

   CREATE EXTENSION module_name;

   例如要安裝 pg_trgm 擴充模組：

   CREATE EXTENSION pg_trgm;

3. 驗證是否安裝成功： 安裝完成後，可使用 \dx 查詢目前資料庫已安裝的所有擴充模組，確認目標模組是否新增成功。

   \dx

範例：啟用 pg_trgm 擴充模組

以下是在 psql 連線到 postgres 資料庫並啟用 pg_trgm 擴充模組的完整互動範例：

# 在終端機輸入指令連線目標資料庫
psql -U postgres -d postgres

# 成功連線後，出現 psql 提示符
psql (16.3 (ServBay))
Type "help" for help.

postgres=# -- 查詢目前已裝的擴充（預設多只含 plpgsql 與 pgcrypto）
postgres=# \dx
                  List of installed extensions
   Name   | Version |   Schema   |         Description          
----------+---------+------------+------------------------------
 pgcrypto | 1.3     | public     | cryptographic functions
 plpgsql  | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

postgres=# -- 安裝 pg_trgm 擴充模組
postgres=# CREATE EXTENSION pg_trgm;
CREATE EXTENSION

postgres=# -- 再查詢已安裝擴充，確認 pg_trgm 已被加入
postgres=# \dx
                                    List of installed extensions
   Name   | Version |   Schema   |                            Description                            
----------+---------+------------+-------------------------------------------------------------------
 pg_trgm  | 1.6     | public     | text similarity measurement and index searching based on trigrams
 pgcrypto | 1.3     | public     | cryptographic functions
 plpgsql  | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)

postgres=# -- 安裝成功，此時即可在本資料庫使用 pg_trgm 所提供的各式函數與運算子

查詢 ServBay 中 PostgreSQL 可用擴充模組

在安裝前，了解 ServBay 中 PostgreSQL 所附所有可用擴充模組列表也很有幫助，有助於你發掘更多潛在強大功能。

只需連線到任一 PostgreSQL 資料庫並執行下列 SQL 查詢，就能獲得完整可用擴充清單：

SELECT * FROM pg_available_extensions ORDER BY name;

執行後將顯示表格，列出 ServBay 所整合的 PostgreSQL 版本支援的所有擴充，包括名稱、預設版本、當前資料庫是否已安裝以及模組描述。

以下示範回傳結果（實際欄位與版本依你所用的 ServBay/PostgreSQL 版本為準）：

             name             | default_version | installed_version |                                                       comment                                                       
------------------------------+-----------------+-------------------+---------------------------------------------------------------------------------------------------------------------
 address_standardizer         | 3.4.2           |                   | Used to parse an address into constituent elements. Generally used to support geocoding address normalization step.
 address_standardizer_data_us | 3.4.2           |                   | Address Standardizer US dataset example
 adminpack                    | 2.1             |                   | administrative functions for PostgreSQL
 amcheck                      | 1.3             |                   | functions for verifying relation integrity
 autoinc                      | 1.0             |                   | functions for autoincrementing fields
 bloom                        | 1.0             |                   | bloom access method - signature file based index
 btree_gin                    | 1.3             |                   | support for indexing common datatypes in GIN
 btree_gist                   | 1.7             |                   | support for indexing common datatypes in GiST
 citext                       | 1.6             |                   | data type for case-insensitive character strings
 cube                         | 1.5             |                   | data type for multidimensional cubes
 dblink                       | 1.2             |                   | connect to other PostgreSQL databases from within a database
 dict_int                     | 1.0             |                   | text search dictionary template for integers
 dict_xsyn                    | 1.0             |                   | text search dictionary template for extended synonym processing
 earthdistance                | 1.1             |                   | calculate great-circle distances on the surface of the Earth
 file_fdw                     | 1.0             |                   | foreign-data wrapper for flat file access
 fuzzystrmatch                | 1.2             |                   | determine similarities and distance between strings
 hstore                       | 1.8             |                   | data type for storing sets of (key, value) pairs
 insert_username              | 1.0             |                   | functions for tracking who changed a table
 intagg                       | 1.1             |                   | integer aggregator and enumerator (obsolete)
 intarray                     | 1.5             |                   | functions, operators, and index support for 1-D arrays of integers
 isn                          | 1.2             |                   | data types for international product numbering standards
 lo                           | 1.1             |                   | Large Object maintenance
 ltree                        | 1.2             |                   | data type for hierarchical tree-like structures
 moddatetime                  | 1.0             |                   | functions for tracking last modification time
 old_snapshot                 | 1.0             |                   | utilities in support of old_snapshot_threshold
 pageinspect                  | 1.12            |                   | inspect the contents of database pages at a low level
 pg_buffercache               | 1.4             |                   | examine the shared buffer cache
 pg_freespacemap              | 1.2             |                   | examine the free space map (FSM)
 pg_jieba                     | 1.1.1           |                   | a parser for full-text search of Chinese
 pg_prewarm                   | 1.2             |                   | prewarm relation data
 pg_stat_statements           | 1.10            |                   | track planning and execution statistics of all SQL statements executed
 pg_surgery                   | 1.0             |                   | extension to perform surgery on a damaged relation
 pg_trgm                      | 1.6             | 1.6               | text similarity measurement and index searching based on trigrams
 pg_visibility                | 1.2             |                   | examine the visibility map (VM) and page-level visibility info
 pg_walinspect                | 1.1             |                   | functions to inspect contents of PostgreSQL Write-Ahead Log
 pgcrypto                     | 1.3             | 1.3               | cryptographic functions
 pgrouting                    | 3.6.2           |                   | pgRouting Extension
 pgrowlocks                   | 1.2             |                   | show row-level locking information
 pgstattuple                  | 1.5             |                   | show tuple-level statistics
 plpgsql                      | 1.0             | 1.0               | PL/pgSQL procedural language
 postgis                      | 3.4.2           |                   | PostGIS geometry and geography spatial types and functions
 postgis_sfcgal               | 3.4.2           |                   | PostGIS SFCGAL functions
 postgis_tiger_geocoder       | 3.4.2             |                   | PostGIS tiger geocoder and reverse geocoder
 postgres_fdw                 | 1.1             |                   | foreign-data wrapper for remote PostgreSQL servers
 refint                       | 1.0             |                   | functions for implementing referential integrity (obsolete)
 seg                          | 1.4             |                   | data type for representing line segments or floating-point intervals
 sslinfo                      | 1.2             |                   | information about SSL certificates
 tablefunc                    | 1.0             |                   | functions that manipulate whole tables, including crosstab
 tcn                          | 1.0             |                   | Triggered change notifications
 tsm_system_rows              | 1.0             |                   | TABLESAMPLE method which accepts number of rows as a limit
 tsm_system_time              | 1.0             |                   | TABLESAMPLE method which accepts time in milliseconds as a limit
 unaccent                     | 1.1             |                   | text search dictionary that removes accents
 vector                       | 0.7.0           |                   | vector data type and ivfflat and hnsw access methods
 xml2                         | 1.1             |                   | XPath querying and XSLT
 zhparser                     | 2.2             |                   | a parser for full-text search of Chinese
(55 rows)

請注意，上述清單僅為範例，實際在 ServBay 查詢時得到的擴充與版本資訊會因 ServBay 或 PostgreSQL 版本升級而有所不同。

總結

ServBay 預載豐富的 PostgreSQL 官方擴充，大幅簡化在本地開發環境下善用這些進階功能的流程。開發者無需手動編譯或繁瑣安裝，只需透過簡單 SQL 指令即可於資料庫啟用所需擴充，全心投入應用開發，效率顯著提升。善加利用這些擴充，你也可以簡易為專案加入高階資料庫能力，如效能分析、全文檢索、資料安全等！