現在被稱為 PostgreSQL 的物件關聯式資料庫管理系統，是根據美國加州伯克萊大學所研發的 POSTGRES 衍生而成。經過超過二十年以上的演進，PostgreSQL 現在是世界上最先進的開源資料庫系統。

2.1. 伯克萊大學 POSTGRES 專案

POSTGRES 專案是由 Michael Stonebraker 教授領導的團隊進行研發，由美國國防高等研究計劃署（DARPA, Defense Advanced Research Projects Agency）、美國陸軍研究辦公室（ARO, the Army Research Office）、美國國家科學基金會（NSF, the National Science Foundation）及美國電磁系統實驗室（ESL, Inc）所贊助。POSTGRES 專案始於 1986 年，最原始的設計，＂The design of POSTGRES＂，作為開端，其最初的資料結構模型則揭露於＂The POSTGRES data model＂。規則系統設計發表於＂The design of the POSTGRES rules system＂，而當時的關連式資料儲存的架構則刊載於＂The design of the POSTGRES storage system＂。

POSTGRES 接下進行了幾次重大的變革。第一代的＂demoware＂在 1987 年真的實作成為可用的系統，並在 1988 年的 ACM-SIGMOD 研討會中進行展示，並在 1989 年六月，釋出了第一版可供外部使用者使用的資料庫系統。為了回應當時使用者對於第一代規則系統的批評，其規則系統重新進行設計，並在隔年 1990 年的六月份，隨即推出第二版系統，搭載新的規則系統設計。第三版系統則於 1991 年發表，新增支援多重儲存管理機制，改善查詢處理器，並又改寫了規則系統。如此直到 Postgres 95 誕生之前，主要都專注於移植性及可信賴度的發展。

POSTGRES 接下來開始被運用在許多不同的研究和產品上，財務資料分析系統、噴氣引擎效能監控系統、小行星追蹤資料庫、醫療資訊系統、以及數個地理資訊系統。POSTGRES 也被好幾所大學用於其教學工具。最後，由 Illustra Information Technologies（後來併入 Informix，而 Informix 目前為 IBM 所擁有）技術移轉，並將其商業化。於 1992 年末，POSTGRES 成為 Sequoia 2000 scientific computing project 主要的資料管理系統。

在 1993 年間，用戶數量呈現倍數成長，伴隨而來的是大量的程式碼維護與服務支援，占去絕大部份原來應該進行研究的時間。為了減少維運的負擔，伯克萊的 POSTGRES 專案正式終止於 4.2 版。

2.2. Postgres95

1994年，Andrew Yu 和 Jolly Chen 在 POSTGRES 增加了 SQL 語法的直譯器，並且以新的 Postgres95 為名，在網路上開放讓全世界的人使用，他們成為伯克萊 POSTGRES 原始碼最初的繼承者。

Postgres95 的程式碼是完全以 ANSI C 開發，並且輕量化了 25%。許多內部的改良增進了效率及可維護性。當時 Wisconsin Benchmark 進行測試，Postgres95 在 1.0.x 時的效能比原始的 POSTGRES 4.2 快了約 30% 至 50%。除了一些錯誤修正之外，還有下面這些主要的改良：

• 原有的 PostQUEL 以 SQL（實作於伺服器端）所取代。（連接介面在 PostQUEL 之後便採 libpq 函式庫）子查詢一直到 PostgreSQL 出現之前都還未支援，但在 Postgres95 便已能使用自訂的 SQL 函數，聚合函數 Aggregate function 則被重新實作。GROUP BY 查詢語句也在此時被加入。

• 提供新的工具 psql 可進行互動式的 SQL 操作，採用的是 GNU Readline 的技術，大量地取代了老舊的管理工具。

• 提供新的前端函式庫 libpgtcl，支援 Tcl-based 用戶端程式。還有一個簡易的 shell 接口 pgtclsh，使用新的 Tcl 命令來和 Postgres95 伺服器進行操作。

• 重新改寫了大型物件的交換介面，只保留大型物件翻轉（inversion）作為儲存大型物件的唯一機制。（移除了 inversion 檔案系統）

• 淘汰了實例層級（instance-level）的規則系統，但其規則仍用於重構規則所使用。

• 製作了一個簡短的說明，介紹標準的 SQL 功能，並隨 Postgres95 原始碼發佈。

• 使用 GNU make（取代 BSD make）來編譯程式碼。除此之外，Postgres95 也支援使用未修正的 GCC 編譯器（修正高精度資料對齊問題）。

2.3. PostgreSQL

1996 年，「Postgres95」這個名稱很明顯不再適合。於是我們選擇了新的名稱「PostgreSQL」來呈現出與原始 POSTGRES 之間的源由，也彰顯了結合 SQL 力量的意義。同時，我們設定其版本由 6.0 開始，重回伯克萊 POSTGRES 專案的版號序列。

許多人持續使用「Postgres」（現在已經很少使用全大寫字母表現）來代表 PostgreSQL，是因為傳統，也可能是因為比較好發音。這樣的用法也廣為用於暱稱或別名。

Postgres95 的發展主要在於瞭解及定義伺服器程式既有的問題，而 PostgreSQL 則更重視系統的能力與爭議性的功能上，不過所有的工作是全面性的。

更多有關於 PostgreSQL 的發展，請參閱 附錄 E。

PostgreSQL 是美國加州伯克萊大學資訊科學系基於 POSTGRES 4.2 所研發的物件關聯式資料庫管理系統（ORDBMS, Object-Relational Database Management System）。POSTGRES 中的許多重要概念成為日後一些商用資料庫系統重要的一部份。

PostgreSQL 由伯克萊大學公開其原始碼所誕生，它支援了大多數的標準 SQL 語法，並提供許多先進的功能：

• 複雜查詢（complex queries）

• 外部索引鍵（foreign keys）

• 觸發器（triggers）

• 可更新檢查表（updatable views）

• 事務完整性（transactional integrity）

• 多版本併行控制（multiversion concurrency control）

同時，PostgreSQL 也支援讓使用者能以自己的方式進行擴充。比如透過新增：

• 資料型別（data types）

• 函數（functions）

• 操作（operators）

• 聚合函數（aggregate functions）

• 索引方法（index methods）

• 過程式語言（procedural languages）

並且基於自由許可證，任何人都能夠以任何目的，免費地使用、修改、與散布 PostgreSQL，不論是個人使用、商業用途還是學術研究。

以下所提到慣例，用於指令的語法描述上（均為半型字元）：

• 中括號（[ 和 ]）指可選擇是否輸入的選項。（在 Tcl 指令的語法中，習慣使用問號 ? 來表達這樣的可選擇性）

• 大括號（{ 和 }）及垂直線（|）指的是必須要輸入的部份。

• 連續句點（...）指的是該段落可以允許不斷重覆。

為了使說明更簡潔：

• SQL 指令會跟在命令提示字元 => 之後

• Shell 命令會跟在命令提示字元 $ 之後。雖然一般而言，提示字元可能不會顯示。

關於操作人員的定義：

• 管理者（Administrator） 一般的定義是負責安裝及運行資料庫系統的人

• 使用者（User） 指的是任何正在使用資料庫的人，或者正要使用任何 PostgreSQL 相關系統的人。

這些定義不應該被解釋得太過嚴格，在本文件中，對於系統管理的工作，並沒有固定的假設。

本手冊是 PostgreSQL 的官方手冊。由 PostgreSQL 開發人員和其他志願者在 PostgreSQL 軟體開發的同時所撰寫的。它描述了目前 PostgreSQL 版本正式支援的所有功能。

為了使有關 PostgreSQL 的大量資訊易於管理，本書劃分為幾個部分。每個部分針對的是不同需求的使用者，或針對處於 PostgreSQL 經驗不同階段的使用者：

• 第一部分是對新使用者的入門簡介。

• 第二部分將介紹 SQL 查詢語言環境，包括資料型別、函數以及使用者層級的效能調教。每個 PostgreSQL 使用者都應該閱讀此部份的內容。

• 第三部分則介紹伺服器的安裝及管理。任何維運 PostgreSQL 伺服器的人，無論是供私人使用還是提供給其他人使用，都應該閱讀此部分。

• 第四部分描述了 PostgreSQL 用戶端的程式設計介面。

• 第五部分為進階使用者提供有關資料庫服務進階功能的資訊。主題包括使用者定義的資料型別與函數。

• 第六部分包含有關 SQL 命令、用戶端和伺服器程式的參考資訊。這部分以命令或程序分類結構化資訊。

• 第七部分包含了對 PostgreSQL 開發人員可能有用的各種資訊。

• 1.1. 安裝：從無到有，安裝一個 PostgreSQL 資料庫系統。

• 1.2. 基礎架構：認識 PostgreSQL 的資料庫架構。

• 1.3. 建立一個資料庫：建立第一個 PostgreSQL 資料庫。

• 1.4. 存取一個資料庫：開始存取你的 PostgreSQL 資料庫。

除了此份文件之外，PostgreSQL 還有其他的參考資源：

維基（Wiki）

PostgreSQL的 wiki 記錄了這個專案的 常見問題與解答（FAQ），待辦事項（TODO），以及其他更多不同主題的資訊。

PostgreSQL wiki 也有台灣中文的頁面喔。

網站（Web Site）

PostgreSQL 的官方網站，有最新軟體的釋出訊息，讓你能夠和 PostgreSQL 相處得更棒！

郵件列表（Mailing Lists）

郵件列表的功能，是一個為您解答疑問的好地方，你也可以分享使用經驗給其他同好，或直接和開發者溝通。詳情請參閱 PostgreSQL 的官方網站。

你！（Yourself!）

PostgreSQL 是一個開源的專案，也就是說，它仰賴社群的每一個人給予支持。當你開始使用 PostgreSQL，你會需要其他人的幫助，可能是透過文件或是郵件列表的功能。請考慮也可以回饋您的知識。在閱讀郵件列表和回答疑問的同時，如果你學到了未被文件記載的知識時，請寫下來，並且供獻出來。如果你撰寫了一些程式碼增加了特別的功能，也希望能夠回饋到社群之中。

第一個測試確認你是否能夠存取一個資料庫服務，就是嘗試去建立一個資料庫。一個執行中的 PostgreSQL 服務可以管理許多個資料庫。一般來說，每一個專案或使用者會分開使用不同的資料庫。

你的系統管理員也可能已經為你建立了一個資料庫，如果是這樣的話，那你可以略過本節說明，直接進入到下一節的內容。

要建立一個新的資料庫，在本例中取名叫「mydb」，你可以使用以下的命令：

$ createdb mydb

如果在這個步驟沒有產生任何回應，那就是成功了。你可以跳過本節剩餘的部份。

但你如果看到如下的訊息：

createdb: command not found

這個訊息代表 PostgreSQL 並沒有被正確的安裝。不是它沒有被安裝好，那就是你的命令路徑設定並未包含這個指令。 嘗試使用下列這個包含絕對路徑的指令看看：

$ /usr/local/pgsql/bin/createdb mydb

命令路徑在你的系統可能會有些不同。洽詢你的系統管理員，或著檢查安裝步驟以修正這個情況。

另一種回應可能是如此：

createdb: could not connect to database postgres: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

這代表了資料庫服務尚未啓動，或者它並不存在於createdb預設連線的位置。同樣地，檢查安裝的步驟或洽詢系統管理者。

而另一種回應也可能是：

createdb: could not connect to database postgres: FATAL:  role "joe" does not exist

這裡指出你用來連線的使用者名稱。這種情況可能會發生在你的資料庫管理員並未建立屬於你的資料庫。（PostgreSQL 的使用者帳戶是獨立於作業系統的使用者帳戶的）如果你是資料庫管理員，請參閱第 21 章，進行建立資料庫帳戶。你必須是 PostgreSQL 初始安裝的管理者（通常是 postgres），以建立第一個一般資料庫使用者的帳戶。這個情況也可能發生在，你被發配的 PostgreSQL 使用者名稱有別於你的作業系統使用者名稱，如果是這樣的話，那你需要在指令上使用 -U 選項，或者設定 PGUSER 環境變數，以指定你的 PostgreSQL 使用者名稱。

如果你有一個資料庫帳戶，但你並沒有建立資料庫的權限，你將會看到下列訊息：

createdb: database creation failed: ERROR:  permission denied to create database

並非每一個使用者都被授權可以建立一個新的資料庫。如果 PostgreSQL 拒絕你建立資料庫，那麼系統管理者就需要賦予你建立資料庫的權限。洽詢你的系統管理者，如果是這種情況的話。如果你是自行安裝 PostgreSQL，那麼你應該以你啓動資料庫服務的使用者登入作業系統，再嘗試這個操作。

你也可以建立資料庫，但使用其他的名稱。PostgreSQL 允許在資料庫系統中建立無限制數量的資料庫。資料庫名稱必須是以英文字母為開頭，總長度限制為 63 位元組。一個簡便的方式是，建立一個與你使用者名稱同名的資料庫。許多工具會預設假定資料庫名稱和你同名，所以這可以省略一些文字的輸入。要建立這樣的資料庫，只要簡單地輸入：

$ createdb

如果你不再使用你的資料庫，你可以移除它。舉例來說，你是 mydb 這個資料庫的擁有者（建立者），你可以使用下列指令來消毁它：

$ dropdb mydb

（對這個指令來說，資料庫名稱並不會預設使用你的使用者同名資料庫。你必須明確地指定名稱）這個動作會完全地移除所有和這個資料庫相關的檔案，並且沒有回復的可能，所以要進行這個動作的話，請一定要考慮清楚。

更多有關於 createdb 和 dropdb 的說明，請參閱 createdb 和 dropdb 的相關章節。

你需要先進行安裝，才能開始使用 PostgreSQL。當然，PostgreSQL 也可能已經被安裝在你的系統之中，因為你的作業系統預設套件包含了 PostgreSQL，或其他系統管理者已先行安裝。如果是這樣的話，那麼你應該先瞭解作業系統的資訊，或向你的系統管理員取得存取方式的資訊。

如果你並不確定 PostgreSQL 是否已經可以使用，那麼你也可以自行安裝試試。這樣做並不是很困難，而且是很好的操作練習。PostgreSQL 可以以一般使用者進行安裝，它並不需要系統管理者（root）的權限才能安裝。

如果你打算自行安裝 PostgreSQL，你可以參考的指令進行，完成之後再回到這裡，以瞭解下面有關設定環境變數的內容。

如果你的系統管理者並非以預設的方式安裝，你可能還有一些額外的工作要做。例如，如果資料庫主機其實是遠端的伺服器，你會需要設定 PGHOST 的環境變數，將其指向資料庫主機的網路名稱。而 PGPORT 變數也是必須要設定的。最基本的情境是，如果你嘗試啓動一個應用程式，而它回報它無法取得資料庫連線時，你就必須洽詢你的系統管理者。而如果系統管理者就是你自己，那麼你應該依文件再確認你的環境設定是正確的。如果你仍然並不清楚前面所描述的事項，請詳細閱讀下一節的內容。

本章適合初學資料庫的朋友閱讀，以簡單的語法範例，實際操作以瞭解資料庫的運作方式。事實上，更複雜的資料庫行為，也不脫這個基本的操作模式。

歡迎來到 PostgreSQL 的新手教學。在這個部份裡的內容，主要提供有關於 PostgreSQL 各項功能的簡介、關連式資料庫概念、以及 SQL 語法的入門說明。我們只假設您俱備一些電腦系統基本操作，並不需要很專業的 Unix 或程式設計經驗。這裡主要提供一些實用的經驗，還有 PostgreSQL 系統中重要部份的介紹。在這個部份並不會進行所有議題的詳細說明。

在你閱讀完新手教學之後，也許可以繼續閱讀第二部份：更多有關於 SQL 語法的標準知識；或者到第四部份：瞭解如何開發 PostgreSQL 的應用程式；而如果你需要建置及管理你的資料庫伺服器的話，請參閱第三部份的內容。

要從資料表（table）中取出資料，稱作資料表的查詢。要進行這個行為，你需要 SQL 中的 SELECT 指令。這個指令由幾個部份所組成，回傳列表（select list，想要回傳的欄位）、資料表列表（資料來源的資料表）、選擇性的條件定義（指定一些限制條件）。舉個例子來說，要取得資料表 weather 中所有的資料的話，請輸入：

SELECT * FROM weather;

這裡的星號 * 表示「所有欄位」。下列的指令會回傳相同的結果。

SELECT city, temp_lo, temp_hi, prcp, date FROM weather;

其輸出結果將會如下所示：

     city      | temp_lo | temp_hi | prcp |    date
---------------+---------+---------+------+------------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27
 San Francisco |      43 |      57 |    0 | 1994-11-29
 Hayward       |      37 |      54 |      | 1994-11-29
(3 rows)

你可以在回傳列表中撰寫一些運算表示式，而不只是簡單的欄位引用。舉例來說，你可以輸入：

SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;

這應該會產生這樣的結果：

     city      | temp_avg |    date
---------------+----------+------------
 San Francisco |       48 | 1994-11-27
 San Francisco |       50 | 1994-11-29
 Hayward       |       45 | 1994-11-29
(3 rows)

注意，「AS」被用來重新命名輸出的欄位。（選用）

查詢語句可以加上「WHERE」來設定限制條件，以指定哪些列才需要被回傳。WHERE 的內容是一個布林（truth value）表示式，而只有在其運算值為真（true）時，該列才會被回傳。一般的布林運算子（AND, OR, NOT）都是被允許出現在表示式中的。舉例來說，下列的指令將會回傳 San Francisco 在雨天的天氣數值：

SELECT * FROM weather
    WHERE city = 'San Francisco' AND prcp > 0.0;

結果：

     city      | temp_lo | temp_hi | prcp |    date
---------------+---------+---------+------+------------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27
(1 row)

你可以將結果進行排序：

SELECT * FROM weather
    ORDER BY city;

     city      | temp_lo | temp_hi | prcp |    date
---------------+---------+---------+------+------------
 Hayward       |      37 |      54 |      | 1994-11-29
 San Francisco |      43 |      57 |    0 | 1994-11-29
 San Francisco |      46 |      50 | 0.25 | 1994-11-27

在這個例子之中，其次序並沒有完全地被指定，所以你可能會得到 San Francisco 的列以另一種次序呈現。而你如果以下列指令查詢的話，那你就會得到如上但固定的結果：

SELECT * FROM weather
    ORDER BY city, temp_lo;

你可以在查詢時去除重覆的列：

SELECT DISTINCT city
    FROM weather;

     city
---------------
 Hayward
 San Francisco
(2 rows)

再一次，其結果的次序可能每次都不同，你可以同時使用 DISTINCT 及 ORDER BY 來確保能得到一致性的查詢結果：

SELECT DISTINCT city
    FROM weather
    ORDER BY city;

一旦你已經建立一個資料庫，你就可以開始以下列方式進行存取：

• 執行 PostgreSQL 互動式的終端程式，稱作 psql，它可以讓你輸入、編輯、執行 SQL 指令。

• 使用既有的圖型化介面工具，例如 pgAdmin 或是支援 ODBC 或 JDBC 的辦公室軟體，以建立並輸入資料到資料庫裡。不過這部份並未包含在這份手冊之中。

• 自行撰寫一個程式，可以使用許多種程式語言來完成。這個部份將會在第 IV 章中進行介紹。

在這份指南中，你可能會先使用 psql 來進行一些嘗試。你可以藉由下列指令開始操作 mydb 這個資料庫：

$ psql mydb

如果你並未指明資料庫名稱，那麼它預設會以你的使用者名稱作為資料庫名稱。在先前的章節使用 createdb 時，你已經知道這個隱含的規則了。

在 psql 中，你會以下列訊息開始：

psql (12.4)
Type "help" for help.

mydb=>

最後一行也可能是：

mydb=#

這表示你是資料庫的超級使用者（superuser），如果你是自行安裝 PostgreSQL 的話，大概就會是這個情況。 作為一個超級使用者，表示你不會受限於任何存取控制。不過在這份指南中，這並不是重要的事。

如果你在啓動 psql 時遭遇了一些問題，那麼請回到前一節。createdb 和 psql 的行為很類似，如果前者正常，後者也應該如期運行。

最後一行會輸出的是 psql 的提示字串，它表示 psql 正在等待你輸入 SQL 查詢語句。試試下面的指令吧：

mydb=> SELECT version();
                                         version
------------------------------------------------------------------------------------------
 PostgreSQL 12.4 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
(1 row)

mydb=> SELECT current_date;
    date
------------
 2016-01-07
(1 row)

mydb=> SELECT 2 + 2;
 ?column?
----------
        4
(1 row)

psql 程式中也內建了一些非 SQL 的命令。他們會以倒斜線（ ）起頭。舉例來說，你可以輸入下列指令以取得一些有關 PostgreSQL 所支援的 SQL 語法資訊：

mydb=> \h

要離開 psql 的話，請輸入：

mydb=> \q

如此的話，psql 將會結束，並回到你的命令列介面之中。（想瞭解更多內建指令，在 psql 提示字串後輸入 \? 。）完整的 psql 說明，都記載在 psql 頁面之中。在這份指南中，我們並未使用這些功能，但你可以在需要的時候使用他們。

本篇談的是如何回報問題到 PostgreSQL 官方組織，而您目前閱覽的正體中文手冊並非由官方提供，所以如果您希望指出的問題是本手冊的相關問題，請透過 討論區 或 台灣 PostgreSQL 使用者社群 所提供的聯絡資訊進行回報。

如果你在 PostgreSQL 中發現了問題，我們會很希望可以得到通知。你的問題回報可以讓 PostgreSQL 變得更值得信任，因為百密仍有一疏，PostgreSQL 無法保證在任何平台或任何情況下，都一定是完美無缺的。

下面的建議提供你在回報問題時能夠更有效率。你不一定要完全遵照下面的方式，但如果你試著遵循的話，對大家都有幫助。

我們無法保證可以立即修正所有的錯誤。但如果那個問題是明顯的、關鍵的、或是有重大影響的，那就會有人進行瞭解。也可能會回覆你更新你的資料庫版本，如果是因為版本問題的話。我們也可能會判定該錯誤不會被修正，在我們進行重大修改之前；又也許它不容易簡單處理，而且有其他更重要的需求排程已經在進行中。如果你需要立即性的支援，請接洽當地的商業服務。

5.1. 確認錯誤

在報告錯誤之前，請再三閱讀文件，以確認你真的在進行你正在嘗試的事情。 如果從文件中不清楚是否可以做某事，請回報；這是屬於文件的一個錯誤。如果確實證明一個程式與文件所描述地不同，那就是一個錯誤。這可能包括但不限於以下情況：

• 程式以致命信號（fatal signal）或程式中某個問題造成作業系統錯誤訊息而終止。（反例可能是「磁盤已滿」的訊息，因為您必須自己修復。）

• 程式對於任何輸入都產生錯誤輸出結果。

• 程式拒絕接受有效的輸入 （如文件中所定義的）。

• 程式接受了無效的輸入，卻沒有警告或錯誤消息。但請記住，您對於無效輸入的認知可能來自於我們對傳統做法的延伸或相容性。

• PostgreSQL 在支援的平台上，按照指示進行編譯，構建或安裝卻失敗了。

這裡的「程式」是指任何可執行文件，不僅僅是後端執行的程序。

緩慢或資源匱乏不一定是一個錯誤。閱讀文件或在某個郵件列表中提問，可以幫助你調整應用程式。不符合 SQL 標準不代表是錯誤，除非明確聲明相容某個特定的功能。

在繼續之前，請檢查 TODO 列表和常見問題解答，看看您的錯誤是否已知。 如果您無法瞭解 TODO 列表中的資訊，請報告你的問題。我們至少可以做的是使 TODO 列表更清楚。

5.2. 回報內容

關於錯誤報告最重要的是陳述所有事實並且只有事實。不要對錯誤的可能性進行推測，比如用「似乎（it seemed to do）」字句進行描述或程式的哪個部分有故障。如果你不熟悉實作的方法，你可能會猜測錯誤，而無法幫助我們。即使你有知識性的解釋，也應該是對事實的補充而不是替代它們。如果我們要修復這個錯誤，我們還是首先要看到它發生。報告裸露的事實是相對簡單的（你可以從屏幕上複製貼上它們），但是經常重要的細節被忽略，因為有人認為這並不重要，或者認為報告被理解是應當的。

每個錯誤報告中都應包含以下內容：

• 程式執行步驟的確切順序是重現問題所必需的。這應該是獨立的；如果輸出應該依賴於資料表中的資料，那麼沒有包含前面的 CREATE TABLE 和 INSERT 語句的 SELECT 語句是不夠的。我們沒有時間來對你的資料庫進行逆向工程，如果我們需要建立自己的資料庫的話，我們很可能會錯過這個問題。

  SQL 相關問題的測試最佳格式是可以透過 psql 執行並重現問題。（請確認您的 ~/.psqlrc 啟動設定中沒有任何內容。）建立這個檔案的一個簡單方式是使用 pg_dump 來轉出設定該情境的資料表宣告及資料，然後加入產生問題的查詢語句。我們希望您盡量減少您的問題規模，但這並不是絕對必要的。如果錯誤是可重現的，我們會以任何一種方式找到它。

  如果您的應用程式使用某些其他客戶端界面（如 PHP），請嘗試突顯那些問題查詢語句。我們應該不會設置一個網頁伺服器來重現您的問題。無論如何，請記得提供確切的輸入檔案；不要猜測「大文件」或「中型數據庫」等問題的可能性。因為這些訊息不夠精確，沒有參考價值。

• 你所得到的輸出。請不要說「不能用（didn't work）」或「壞掉了（crashed）」。如果出現錯誤訊息，請列出來，即使您並不瞭解它。如果程式終止是因為作業系統錯誤，請說明哪個系統錯誤。如果沒有發生任何事情，也如實說明。即使您的測試案例的結果是當機或其他明顯的情況，也不一定會在我們的平台上發生。如果可以的話，最簡單的方法是從終端視窗中複製輸出內容。

  注意

  如果您要報告錯誤訊息，請取得該訊息最詳細的形式。在 psql，事先輸入 \set VERBOSITY verbose。 如果從伺服器記錄取得訊息，請在執行時將參數 log_error_verbosity 設定為 verbose，以便記錄所有詳細訊息。

  如果是嚴重錯誤的情況下，客戶端報告的錯誤訊息可能不包含所有可用的信息，還請查看資料庫伺服器的系統記錄輸出。如果你沒有保留伺服器的系統記錄，那麼這是開始這樣做的好時機。

• 你所期待的輸出情況對於情境說明非常重要。如果你只是寫「這個命令給我這樣的輸出」或「這結果不是我的預期」，我們可以自己運行它，檢視該輸出結果，並認為它看起來正常，正是我們的預期。我們不應該把時間花在解讀你的命令背後確切的語意。特別是不要僅僅說「這不是 SQL 或 Oracle 所做的那樣」。從 SQL 挖掘所謂正確的行為並不是一件有趣的事情，我們更不會知道所有其他關連式資料庫的做法。（如果你的問題是當機，很明顯地你可以省略此項。）

• 任何命令列選項和其他啟動選項，包括您從預設值修改的任何相關的環境變數或設定檔案。再次提醒，請提供確切的訊息。如果您正在使用預先封裝好的套件，在開機時啟動資料庫伺服器，您應該嘗試瞭解它如何進行。

• 所有你所做的與安裝說明不同之處。

• PostgreSQL 的版本。你可以執行 SELECT version(); 來找到你正在連線的資料庫系統版本。大多數的程式工具也會支援 --version 選項；至少 postgres --version 和 psql --version 都可以使用。如果功能或選項不存在，那麼您的版本已經太舊而無法進行升級。如果您運行預先編譯的套件（如 RPM），請說明，包括該套件可能具有的子版本。如果您正在使用 Git 某個快照，請說明快照版本，包括提交碼（commit hash）。

  如果您的版本比 10 還舊，我們幾乎肯定會告訴您進行升級。每個新版本都有很多錯誤修復和改進，所以很可能在 PostgreSQL 的舊版本中遇到的錯誤已經被修復了。我們只能對使用較早版本的 PostgreSQL 伺服器提供有限的支援；如果你的需求多於我們所能提供的，請考慮取得商業支援合約。

• 執行平台資訊。這包括作業系統核心名稱和版本，C 語言函式庫，中央處理器、記憶體資訊等等。在大多數情況下，報告系統供應商和版本是足夠的，但不要假設每個人都知道「Debian」究竟是什麼，或是每個人都運行在 i386 上。如果您有安裝問題，則還需要你的機器上有關系統工具組的訊息（編譯器、make 工具等等）。

不要擔心你的錯誤報告會因此而變得冗長。這是一個事實過程的呈現。第一次報告所有事情，對我們比較好的做法是，儘量把事實從你身上擠出來。 另一方面，如果你輸入的檔案很大，持平來說，首先要問是否有人有興趣去研究它。這裡有一篇 文章，概述了有關報告錯誤的更多建議。

不要花所有的時間來指出輸入中的哪些變化會使問題消失。這可能對解決問題沒有幫助。如果事實證明該錯誤不能立即解決，您仍然有時間找到並分享您的解決方案。此外，再一次，不要浪費你的時間猜測為什麼這個問題會存在。我們會很快找到。

在撰寫錯誤報告時，請避免混淆術語。這個軟體整體來說被稱為「PostgreSQL」，有時候會簡稱為「Postgres」。如果你特別在談論後端的程序，請明確指出，而不要只是說「PostgreSQL 當掉了」。其中一個後端程序的當掉與主要的「postgres」程序當掉有很大的不同；當你的意思是某個後端程序終止時，請不要說「伺服器當掉了」，反之亦然。此外，例如交互式前端的「psql」用戶端程序與後端完全分離的。請嘗試具體說明問題是在用戶端還是伺服器端。

5.3. 回報錯誤到哪裡？

一般來說，將錯誤報告發送到錯誤報告的郵件列表：<pgsql-bugs@postgresql.org>。你需要為你的電子郵件使用描述性主題，可能是錯誤消息的一部分。

另一種方法是填寫官方網站上提供的錯誤報告網頁式表單。用這個方式的話，它也會把內容轉寄到<pgsql-bugs@postgresql.org>郵件列表之中。

如果您的錯誤報告隱含有安全疑慮，並且您希望它不會在公共討論區中立即顯示，請不要將其發送到pgsql-bugs。安全問題可以非公開地回報至<security@postgresql.org>。

不要向任何用戶的郵件列表發送錯誤報告，像是<pgsql-sql@postgresql.org>或<pgsql-general@postgresql.org>。這些郵件列表用於回答用戶的問題，用戶通常不希望收到錯誤報告。更重要的是，他們不太可能修復它們。

另外，請不要向開發者的郵件列表發送報告<pgsql-hackers@postgresql.org>。這個列表用於討論 PostgreSQL 的開發，如果我們可以將問題報告分開來，那將是很棒的。 不過，我們可能會選擇討論你的錯誤報告pgsql-hackers，如果那個問題需要更多討論的話。

如果你的問題是使用手冊，回報問題的最佳位置是使用手冊的郵件列表<pgsql-docs@postgresql.org>。請具體說明文件的哪一部分讓你不滿意。

如果您的錯誤是非支持平台上的可移植性問題，請寄送郵件到<pgsql-hackers@postgresql.org>，讓我們（和你）可以將 PostgreSQL 移植到你的平台上。

注意

不幸由於垃圾郵件數量，所有上述電子郵件地址都是封閉的郵件列表。也就是說，您需要訂閱列表才能發布。（然而，您不需要訂閱使用錯誤報告 Web 表單。）如果你想發送郵件但不想接收郵件列表，您可以訂閱並設置您的訂閱選項為「nomail」。想得到更多訊息的話，請寄一封郵件到 <majordomo@postgresql.org>，內文請輸入「help」一個單字，就會自動回覆你進一步的訊息。`

INSERT 指令被用來將資料以資料列（row）的形式，新增至資料表（table）之中：

INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');

注意，所有的資料型別都有明確的輸入格式。只要不是簡單的數值內容，都必須要以單引號（'）括住，如同在本例中的形式。日期時間型別（date type）的資料內容就比較有彈性，但在這個導覽之中，我們仍然使用較固定的格式來表現。

地理資訊型別（point type）需要有座標組作為輸入，如下所示：

INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');

到目前為止，語法的使用需要你依照欄位宣告的次序擺放，而另一種語法可以允許你明確地指定資料相對應的欄位：

INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
    VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');

你可以將欄位以不同的次序擺放，甚或略去某些欄位，例如，precipitation 欄位（prcp）內容未知：

INSERT INTO weather (date, city, temp_hi, temp_lo)
    VALUES ('1994-11-29', 'Hayward', 54, 37);

許多開發者會認為，在撰寫習慣上，明確指定欄位是比較好的方式。

請執行下列的指令，你將會擁有後續章節所需要的範例資料。

你可能需要使用 COPY 這個指令從文字檔案來載入大量的資料。這個指令會比 INSERT 要快上許多，因為 COPY 指令的設計就是為了大量資料輸入而產生的。它少了一些彈性，但提供了效率上的最佳表現。使用範例如下所示：

COPY weather FROM '/home/user/weather.txt';

資料來源的檔案必須存在於後端的伺服器之中，並且可被 PostgreSQL 使用者（postgres）所存取，注意不是用戶端的主機，因為後端伺服器的服務需要直接讀取該檔案。你可以取得更多詳細說明，在 COPY 指令的說明頁面。

在開始使用之前，你需要瞭解基本的 PostgreSQL 系統架構。瞭解 PostgreSQL 如何回應操作，有助於讓你更清楚理解接下來的說明。

以資料庫的術語來說，PostgreSQL 採用了主從式架構（client/server）。PostgreSQL 會在進行下列操作時保持連線：

• 伺服器的執行程序，負責管理資料庫的檔案、受理用戶端的連線要求、執行相對應的資料庫動作。這樣的資料庫伺服端程式稱之為「postgres」。

• 用戶端的程式用來發起資料庫操作的行為，其設計的形態很廣泛：可能是文字介面的工具、圖型介面的程式、將資料庫內容顯示成網頁的網際網路伺服器、甚或是專用的資料庫管理工具。有一些用戶端程式是由 PostgreSQL 官方所提供，大部份由第三方的其他使用者所開發。

如同一般的主從式架構，用戶端與伺服端可以是兩台不同的主機，而他們透過 TCP/IP 的網路協定溝通。你應該將這個觀念謹記在心，因為某些在用戶端可以被存取的檔案，在伺服端可能就無法存取（或使用不同的檔案名稱）。

PostgreSQL 伺服器可以管理來自多個用戶端的同時連線。為了達到這樣的功能，它會自我複製（fork）成新的執行程序，一對一地處理每一個連線。這個部份進一步來說，用戶端和新的伺服器執行程序之間的溝通，並不需要原始的 postgres 執行程序介入。也就是說，主要的資料庫服務執行程序會持續等待其他用戶端的連線，協助安排好其與伺服端執行程序的配對之後便完全交接，再回到等待的狀態。（當然，使用者完全不會察覺這些行為，在此說明僅僅是為了整體性的概念描繪）

你可以創建一個新的資料表，為它取一個名字，並且宣告所有的欄位名稱與其資料型別：

你可以把上述內容在 psql 中輸入，包含換行字元不會影響判讀。psql 是以分號作為指令結束的判定。

空白（包含「空白」、「定位符號」和「換行符號」）都可以自由使用在 SQL 指令當中。這表示你可以將指令以不同的形式排版，甚至全部寫都在一行也沒問題。使用破折號，連續2個（＂--＂），表示緊接的內容只是註解，直到該行結束為止。PostgreSQL 是不分大小寫字母的，包括各類關鍵字和描述語，除非是使用雙引號括起來的文字。（更精確地說，沒有被雙引號括起來的識別字，都會轉為小寫字母進行識別）

varchar(80) 表示指定一個資料型別，它可以儲放任意 80 個字元以內的字串。int 是一般認知的整數型別。real 表示資料是單精確度的浮點數。date 顧名思義，就是日期時間型別。（本例中欄位名稱和型別都使用 date，這可能是方便，也可能是困擾，端看你如何使用。）

PostgreSQL 支援標準的資料型別 int, smallint, real, double precision, char(N), varchar(N), date, time, timestamp, interval，也支援了複合型的地理資料型別。PostgreSQL 可以自訂組合任意數量的資料型別。語法上，資料型別名稱並不是保留關鍵字的範圍，除非特定的標準 SQL 支援需求之外。

第二個例子用來儲存城市及其所在的地理位置：

point 型別是一個 PostgreSQL專屬資料型別的範例。

最後，應該被點出來的是，如果你不再需要一個表格，或者想要重新以別的方式創建它，那麼你可以以下列的指令來移除它：

你可以使用 UPDATE 指令以列為單位來更新資料。假設你發現氣溫的數值測量在 11 月 28 日之後都多了 2 度。你可以以下列語法來修正這些資料：

查看一下這些更新後的資料：

在前面的章節，我們介紹了如何使用 SQL 來存取 PostgreSQL 的基本方式。接下來，我們將會討論更多先進的功能，SQL 的管理功能以及防止資料遺失或損毁。最後，我們也會介紹一些 PostgreSQL 的延伸功能。

這個章節偶爾會引用的範例，試著去改寫或是優化他們，所以閱讀過上一章也是很有用的。在這一章中有一些範例是來自於 tutorial 目錄中的 advanced.sql，這個檔案有一些範例資料可以載入，但載入方式在此就不再贅述。（請參閱 的內容）

到目前為止，我們的一個查詢都只涉及到一個資料表。其實可以在同一個查詢中，同時查詢多個資料表，或者在同一個資料表之中同時處理多個資料列的資料。在一個查詢之中，涉及到同一個或多個不同的資料表中的資料，稱作為交叉查詢（join）。舉個例子來說，你希望同時列出天氣和城市位置的資料。要完成這項工作，我們需要關連資料表 weather 中的 city 欄位與表格 cities 中的 name 欄位，然後回傳符合條件的資料。

注意

這只是一個概念式的模形，交叉查詢（join）會以更有效率的方式運行，並非真正需要比較每一種組合是否符合條件，不過這些過程對於使用者而言並不會產生操作或結果上的差異。

下列查詢會產生交叉查詢的結果：

在這個結果中可以觀察到兩件事情：

• 不會有關於 Hayward 的結果出現。這是因為在資料表 cities 中未有 Hayward 的資料，所以交叉查詢會忽略資料表 weather 中未能關連的資料。關於這點，我們很快就會有解決辦法。

• 有兩個欄位顯示了城市的名稱。這樣是正確的，因為來自於資料表 weather 和 cities 的欄位被串連起來了。實務上，這樣的結果並不令人滿意，所以也許你可以明確地指出輸出的欄位，取代「 * 」的使用：

**練習：**試試看，當 WHERE 表示式被省略的話，查詢語句的意義會怎麼樣？

因為所有的欄位都使用不同的名稱，所以解譯器會自動發現他們所屬的資料表為何。如果在兩個資料表之中，存在有相同名稱的欄位時，你最好明確指出確定的欄位，如下所示：

多數開發者認為，在交叉查詢中，明確指出確定的欄位名稱，是良好的撰寫習慣。這樣查詢就不會因為有相同的欄位名稱而產生錯誤。而相同名稱的欄位可能是開發後續才加入的，未指明的話，就可能造成意外的結果。

交叉查詢也可以寫成如下的另一種形式：

這種語法並不如上述的常見，但我們會在這裡說明，以幫助你在後續章節的學習。

現在我們要回到前面的問題，把 Hayward 的資料放在輸出的結果之中。我們要在查詢中做的是，掃描資料表 weather，找到有所關連的每一列資料；沒有關連到的資料列，我們要填上「空值」（null）在資料表 cities 相對的欄位之中。這樣的查詢我們稱作「外部交叉查詢」（outer join）。（先前的交叉查詢為「內部交叉查詢」（inner join））。這樣的查詢指令如下所示：

這種查詢稱作為「左側外部查詢」（left outer join），因為這個交叉查詢，放在左側的資料表中的資料列，一定會在結果中至少出現一次，而右側的資料表中，則只有輸出有關連到左側資料表的資料列。當左側資料表的資料列，並沒有在右側資料表中被關連到時，屬於右側資料表的欄位就會被填上空值輸出。

**練習：**也有「右側外部交叉查詢」（right outer join）和「完全外部交叉查詢」（full outer join），試著找出他們都做了些什麼。

我們也可以對同一個資料表做交叉查詢，稱作為「自我交叉查詢」（self join）。接下來的範例，假設我們希望找到所有氣溫範圍的天氣資料。所以我們需要讓 temp_lo 及 temp_hi 兩個欄位，和其他的 temp_lo 及 temp_high 相比較。我們可以用下列的查詢來符合需求：

這裡我們重新命名了資料表 weather 為 W1 及 W2，以在交叉查詢中區分左側及右側。你也可以在其他查詢中使用這個技巧，以節省輸入的複雜度，例如：

你將會在後續內容中，不斷練習到這樣的使用方式。

要把某些資料列從資料表中移除，就使用 DELETE 這個指令。假設你對於 Hayward 這個城市的天氣不再感興趣了，那麼你可以執行下列指令，來刪除資料表中的這些資料：

所有關於 Hayward 的資料都被刪除了。

這個指令有一個應該要特別注意的情況：

沒有任何限制的條件，DELETE 將會刪去所有該資料表中的資料，使成為空的資料表。資料庫系統並不會在這個動作執行前和你確認！

交易（Transaction），是所有資料庫的基礎概念。基本上來說，一個交易指的是，一系列的執行步驟包裹在一起，其結果只有全部成功或全部失敗兩種情況的操作行為。而其即時的執行狀態，對於其他同時在進行的交易而言，相互之間都是不可見的。如果在執行過程中產生了錯誤而造成整個交易無法完成，那麼所有的指令都不會對資料庫原來的內容產生影響。

舉例來說，某個銀行資料庫存放著各個客戶的存款資訊，也存放著分行的存款總額資訊。假設我們想要轉帳 $100.00，從 Alice 的帳號轉到 Bob 的帳戶。可以很直觀地依敘述，直接以下列指令執行：

UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
UPDATE branches SET balance = balance - 100.00
    WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Alice');
UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Bob';
UPDATE branches SET balance = balance + 100.00
    WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Bob');

這些指令的細節在這裡並不重要，重要的是，有好幾個更新資料的動作要被執行。我們銀行的營業員需要保證所有的更新資料都要完成，或是保持原樣。如果因為系統錯誤，而造成 Bob 收到 $100.00，但 Alice 卻沒有轉出金額，就不是應該發生的事。又或是 Alice 轉出了現金，而 Bob 卻沒有轉入金額，她也不會是開心的客戶。我們需要具有保證交易安全的方法，也就是如果在執行過程中，有部份出了錯，那麼即使是已經執行的部份，也不會對資料庫產生影響。把這些更新資料的指令，包裝在一個交易之中，就是這個保證交易安全的方法。這樣的交易稱作為 atomic：從其他的交易的角度來看，整個行為只有完全執行，亦或是什麼都沒有做，兩種結果而已。

我們也希望有某個保證是，一旦某個交易被完成了，那麼會由資料庫系統發出通知，使它確實是永久性的資料，即使發生短暫的當機之後，資料也不會遺失。舉例來說，如果我們正在進行 Bob 的提款系統操作行為，在他走出銀行大門之後，我們不要有任何可能性使他的提款記錄消失。一個具備交易安全的資料庫，會將這裡交易裡的更新行為，在它們被回報完成之前，都記錄在長效型儲存裝置上（也就是磁碟機）。

交易安全資料庫的另一個重要性質是， atomic update 的概念：當多個交易同時在進行時，每一個交易都不能夠看到其他交易未完成交易的資料狀態。舉個例子，如果某個交易正在進行總計所有分行的餘額，它不會只包含 Alice 的分行的提款，或不計算 Bob 的分行的存款，反之亦然。所以交易必須是全有全無的結果，而不只是資料庫資料的永久性，還包含了交易執行過程的可視性。一個未完成的交易直到完全完成之前，其間資料的改變，對其他的交易而言都看不見；而當交易完成的同時，資料的改變也同時全部呈現出來。

在 PostgreSQL 中，所謂的交易，是以 SQL 的 BEGIN 及 COMMIT 兩個指令相夾的過程。 所以我們前述的銀行交易實際上會像這樣：

BEGIN;
UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
-- etc etc
COMMIT;

如果在交易的過程之中，我們決定不要完成交易（也許我們發現 Alice 的帳戶餘額不足），我們可以使用 ROLLBACK 指令來取代 COMMIT，那麼所有資料的變更都會取消。

PostgreSQL 一般將每一個 SQL 指令都視為一個交易來執行。如果你並沒有使用 BEGIN 指令，那麼每一個個別的指令就會隱含 BEGIN 先行，然後如果成功的話，COMMIT 也自動執行。一系列被 BEGIN 和 COMMIT 包夾的區域，有時候就稱為交易區塊。

注意

有一些用戶端程式會自動加入執行 BEGIN 及 COMMIT 指令，使得你不需要要求就獲得交易區塊的效果。請詳閱你所所用的工具文件。

還有一種交易的控制更為細緻，就是使用交易儲存點（savepoint）。交易儲存點允許你可以選擇性地取消部份交易，而只成交剩下的部份。使用 SAVEPOINT 指令定義一個交易儲存點之後，你可以使用 ROLLBACK，回復該交易狀態到交易儲存點。所有在交易儲存點之後所造成的資料庫變更，都會被回復，但交易儲存點之前的變更會暫時留存。

在回復到交易儲存點之後，它仍然可以繼續進行，而你可以多次回到該儲存點。相反地，如果你確定你不要再回復到某個特定的交易儲存點時，它也可以被釋放出來，系統資源也可以獲得舒解。記得，釋放或回復到一個交易儲存點時，將會自動釋放所有在那之後的交易儲存點。

所有這些過程都發生在交易區塊之中，所有沒有任何改變會讓其他資料庫連線所發現。當你確認完成了交易區塊的時候，完成交易的動作就會讓其他的連線知道，也能發現資料的改變；同時，回復的動作也會再也無法執行了。

記得這個銀行的資料庫，假設我們從 Alice 的帳號提出了 $100.00，然後存入了 Bob 的帳戶之中，隨後又發現應該要存到 Wally 的帳戶。我們可以使用交易儲存點來完成這個過程：

BEGIN;
UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
SAVEPOINT my_savepoint;
UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Bob';
-- oops ... forget that and use Wally's account
ROLLBACK TO my_savepoint;
UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Wally';
COMMIT;

當然，這個例子是過度於簡化了，但這呈現出在交易區塊中使用交易儲存點，有著更多的可能性。進一步來說，ROLLBACK TO 是唯一能夠控制交易區塊執行流程的方式，當系統產生錯誤時，可以縮小回復的範圍，而不是只能全部回復再執行。

回想一下在第 2 章中的表格 weather 及 cities，思考下列問題： 你想要保證沒有另一個人可以新增在 cities 中沒有的城市資料到 weather 中。這就是所謂資料關連性的管理。在簡單的資料庫系統當中，可能會這樣實作：先檢查 cities 中是否已有對應的資料，然後再決定資料表 weather 中新增或拒絕新的天氣資料。這個辦法還有很多問題，而且很不方便，所以 PostgreSQL 可以幫助你解決這個需求。

新的資料表宣告如下所示：

CREATE TABLE cities (
        city     varchar(80) primary key,
        location point
);

CREATE TABLE weather (
        city      varchar(80) references cities(city),
        temp_lo   int,
        temp_hi   int,
        prcp      real,
        date      date
);

現在嘗試新增一筆不合理的資料：

INSERT INTO weather VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28');

ERROR:  insert or update on table "weather" violates foreign key constraint "weather_city_fkey"
DETAIL:  Key (city)=(Berkeley) is not present in table "cities".

外部索引鍵或簡稱外部鍵（foreign key）的行為可以讓你的應用程式變得容易調整。我們在這個導覽中不會再深入這個簡單的例子了，但你可以在第 5 章取得進一步的資訊。正確地使用外部索引鍵，可以改善資料庫應用程式的品質，所以強烈建議一定要好好學習它。

繼承是一個物件導向資料庫的概念，它開啓了資料庫設計的更多可能性。

讓我們創建兩個資料表：cities 和 capitals。很自然地，首都（capitals）也是城市（cities），所以你希望有個方式，可以在列出所有城市時，同時也包含首都。如果你真的很清楚的話，你可以建立如下的結構：

CREATE TABLE capitals (
  name       text,
  population real,
  altitude   int,    -- (in ft)
  state      char(2)
);

CREATE TABLE non_capitals (
  name       text,
  population real,
  altitude   int     -- (in ft)
);

CREATE VIEW cities AS
  SELECT name, population, altitude FROM capitals
    UNION
  SELECT name, population, altitude FROM non_capitals;

這樣的查詢結果會是正確的，不過它有點不是很漂亮，當你需要更新一些資料的時候。

有一個更好的方法是這樣：

CREATE TABLE cities (
  name       text,
  population real,
  altitude   int     -- (in ft)
);

CREATE TABLE capitals (
  state      char(2)
) INHERITS (cities);

在這個例子中，capitals 繼承了 cities 的所有欄位（name, population, altitude）。欄位 name 的資料型別是文字型別（text），是一個 PostgreSQL 內建的資料型別，它允許字串長度是動態的。然後宣告 capitals 另外多一個欄位，state，以呈現它是屬於哪一個州。在 PostgreSQL，一個資料表可以繼承多個其他的資料格。

舉個例子，下面的查詢可以找出所有的城市名稱，包含各州的首都，而其海拔高過於 500 英呎以上：

SELECT name, altitude
  FROM cities
  WHERE altitude > 500;

回傳結果：

   name    | altitude
-----------+----------
 Las Vegas |     2174
 Mariposa  |     1953
 Madison   |      845
(3 rows)

另一方面，下面的查詢可以列出非首都的城市，且其海拔在 500 英呎以上：

SELECT name, altitude
    FROM ONLY cities
    WHERE altitude 
>
 500;

   name    | altitude
-----------+----------
 Las Vegas |     2174
 Mariposa  |     1953
(2 rows)

這裡的「ONLY」（cities之前），指的是這個查詢只要在資料表 cities 上就好，不包含繼承 cities 其他資料表。這裡許多我們都已經討論的指令 — SELECT、UPDATE、DELETE — 都支援 ONLY 這個修飾字。

注意

雖然繼承經常被使用，但尚未整合唯一性限制或外部索引鍵的功能，這限制了它的可用性。詳情請參考 5.9 節的說明。

窗函數（window function）提供了在一個資料表中，進行資料列與資料列之間的關連運算。這部份可以和彙總函數的功能相呼應。然而，窗函數並無法像彙總函數一樣，把多個資料列運算合併為單一資料列的結果。取而代之的是，這些資料列仍然是分開並列的狀態。在這樣的情境下，窗函數能讓查詢結果的每一個資料列，都得到更多資訊。

這裡有一個列子，試著比較每一個員工他的薪資及他的部門平均薪資的情況：

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;

  depname  | empno | salary |          avg          
-----------+-------+--------+-----------------------
 develop   |    11 |   5200 | 5020.0000000000000000
 develop   |     7 |   4200 | 5020.0000000000000000
 develop   |     9 |   4500 | 5020.0000000000000000
 develop   |     8 |   6000 | 5020.0000000000000000
 develop   |    10 |   5200 | 5020.0000000000000000
 personnel |     5 |   3500 | 3700.0000000000000000
 personnel |     2 |   3900 | 3700.0000000000000000
 sales     |     3 |   4800 | 4866.6666666666666667
 sales     |     1 |   5000 | 4866.6666666666666667
 sales     |     4 |   4800 | 4866.6666666666666667
(10 rows)

前面三個欄位是由資料表 empsalary 直接取得，每一個資料列就是該資料表的每一個資料列列。而第四個欄位則呈現整個資料表中，與其 depname 相同的平均薪資。（這實際上就是由非窗函數的 avg 彙總而得，只是 OVER 修飾字讓它成為窗函數，透過「窗」的可見範圍做計算。）

窗函數都會使用 OVER 修飾字，然後緊接著窗函數及其參數。這是在語法上使其有別於一般函數或非窗函數的彙總。OVER 區段需要確切指出如何分組要被窗函數計算的資料列。PARTITION BY 在 OVER 中，意思是要以 PARTITION BY 之後的表示式來分組或拆分資料列的資料。對於每一個資料列而言，窗函數的結果是，透過所有和該資料列相同分組的資料，共同運算而得。

你也可以控制列被窗函數處理的次序，透過在 OVER 中加入 ORDER BY。（窗內的 ORDER BY 不見得需要對應到資料列輸出的次序）例子如下：

SELECT depname, empno, salary,
       rank() OVER (PARTITION BY depname ORDER BY salary DESC)
FROM empsalary;

  depname  | empno | salary | rank 
-----------+-------+--------+------
 develop   |     8 |   6000 |    1
 develop   |    10 |   5200 |    2
 develop   |    11 |   5200 |    2
 develop   |     9 |   4500 |    4
 develop   |     7 |   4200 |    5
 personnel |     2 |   3900 |    1
 personnel |     5 |   3500 |    2
 sales     |     1 |   5000 |    1
 sales     |     4 |   4800 |    2
 sales     |     3 |   4800 |    2
(10 rows)

如上所示，rank 函數為每個有使用 ORDER BY 的分組，標記一系列數字的次序。rank 不需要特定的參數，因為它標記的範圍一定是整個 OVER 所涵蓋定的範圍。

窗函數所計算的範圍，是一個虛擬資料表的概念，是由 WHERE、GROUP BY、HAVING、或其他方式虛擬出來的。舉例來說，當某個資料列被 WHERE 過濾掉時，它也不會被任何窗函數看見。一個查詢中可以包含多個窗函數，透過不同 OVER 修飾字的指定，將資料做不同觀點的處理。但他們都會在一個相同的虛擬資料表中進行處理。

我們已經瞭解如果次序不重要的話， ORDER BY 可以被省略；且如果所有的資料列都只區分成一組的話，其實 PARITION BY 也可以省略。

還有另一個窗函數相關的重要概念：對於每一個資料列來說，它會在分組中還有個分組，另稱作窗框（window frame），有一些窗函數只對窗框裡的資料列進行處理，而不是整個分組。預設的情況是，如果 ORDER BY 被指定了，以 ORDER BY 排序後，那麼窗框的範圍就是從分組的第一列到該列為止，而在那之後資料列的值都會相同。當 ORDER BY 被省略的時候，預設窗框的範圍就是整個分組( 有一些選項可以透過其他方式定義 window frame，但本文並不會涵蓋它們。有關詳細資訊，請參閱第 4.2.8 節。)。下面是使用 sum 的例子：

SELECT salary, sum(salary) OVER () FROM empsalary;

 salary |  sum  
--------+-------
   5200 | 47100
   5000 | 47100
   3500 | 47100
   4800 | 47100
   3900 | 47100
   4200 | 47100
   4500 | 47100
   4800 | 47100
   6000 | 47100
   5200 | 47100
(10 rows)

上面可以看到，因為在 OVER 裡面沒有 ORDER BY，窗框就等於整個分組，甚至因為沒有 PARTITION BY，所以等於整個資料表。換句話說，每一個資料列總和都是整個資料表的總計，所以我們在每一個資料列中都得到相同的結果。但如果我們加入了 ORDER BY 之後，結果將會不同：

SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;

 salary |  sum  
--------+-------
   3500 |  3500
   3900 |  7400
   4200 | 11600
   4500 | 16100
   4800 | 25700
   4800 | 25700
   5000 | 30700
   5200 | 41100
   5200 | 41100
   6000 | 47100
(10 rows)

這裡的總和就是從第一筆（最小），加計到每一列，包含薪資相同的每一列（注意薪資相同的）。

窗函數只允許出現在 SELECT 的輸出列表及 ORDER BY 子句裡，在其他地方都是被禁止的，像是 GROUP BY，HAVING，WHERE等區段。這是因為窗函數在邏輯上，都是在他們處理完之後才進一步處理資料的。也就是說，窗函數是在非窗函數之後才執行的。這意指在窗函數中使用非窗函數是可以的，但反過來就不行了。

如果有一個需要在窗函數處理完再進行過濾或分組的查詢的話，你可以使用子查詢。舉列來說：

SELECT depname, empno, salary, enroll_date
FROM
  (SELECT depname, empno, salary, enroll_date,
          rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos
     FROM empsalary
  ) AS ss
WHERE pos < 3;

上面的查詢只會顯示內層查詢的次序（rank）小於 3 的資料。

當一個查詢使用了多個窗函數的話，它就會分別使用 OVER 子句來描述，但如果相同的分組方式要被多個函數所引用的話，就重覆了，也容易出錯。這種情況可以使用 WINDOW 子句來取一個別名，來取代 OVER。舉個例子：

SELECT sum(salary) OVER w, avg(salary) OVER w
  FROM empsalary
  WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);

更多窗函數的細節可以參閱 4.2.8 節、9.22 節、7.2.5 節、及 SELECT 指令的說明頁。

PostgreSQL 還有許多這份導覽中未能介紹到的功能，這裡主要是針對新鮮的 SQL 使用者所準備的內容。 這些功能將會在後續的章節進行更詳細的討論。

如果你覺得你需要更多介紹的資訊，可以到 PostgreSQL 的官方網站取得更多訊息。

在這個部份介紹如何在 PostgreSQL 中使用 SQL 語言。首先，我們從一般性的 SQL 語法開始說明，然後解釋如何建立結構來保存資料，如何充實資料庫，以及如何查詢資料的方法。中段的部份列出 SQL 指令中的資料型別與函數。最後剩餘的部份，將會針對一些調教資料庫的重要議題進行說明。

這個部份的內容設計讓初學者可以循序漸進地完整瞭解該主題，而不需要反覆前後查閱。各章的內容設計上都是獨立的，所以進階的使用者可以分別閱讀他們需要的部份。在這個部份的內容，針對於主題式的單元描述。需要瞭解詳情的讀者，請參閱第 6 部份中，個別指令的說明頁面。

在這個部份裡的讀者，應該要知道如何連線到一個 PostgreSQL 資料庫，並且執行 SQL 指令。如果不熟悉這些操作的讀者，建議先閱讀第 1 部份的內容。SQL 指令一般是使用終端工具 psql，但其他具有類似功能的程式也可以使用。

讓我們回到 2.6 節的查詢範例。假設關連天氣資訊和城市位置的結果，是你的應用中特別常用的，但你並不想要每次都要輸入一長串的查詢語句。那麼，你可以為這個查詢語句建立一個「檢視表（View）」，你可以取一個名字，當你需要使用的時候，你可以把它當作一個資料表來使用：

CREATE VIEW myview AS
    SELECT city, temp_lo, temp_hi, prcp, date, location
        FROM weather, cities
        WHERE city = name;

SELECT * FROM myview;

妥善地使用檢視表，對於良好的 SQL 資料庫設計而言，是很關鍵的部份。檢視表允許你可封裝你的資料表結構與細節，當你的應用系統在逐步發展成熟的過程中，扮演一致性的資料介面。

檢視表可以用在大多數資料表可以使用的地方。而用檢視表來封裝其他檢視表的情況，也不少見。

PostgreSQL 是一個關連式資料庫管理系統（RDBMS）。這表示它是一個管理關連性質資料的系統。關連性，基本上在數學裡是以資料表（table）的形式來表現的。今天，以資料表為形式儲存資料是很常見的事，它是很自然的表現，但也有很多其他組識資料庫的方式。在 Unix-like 的作業系統中，檔案和目錄是一個階層式資料庫的案例。更先進的發展是採用物件導向式的資料庫。

每一個資料表是很多資料列（row）的集合。而每一個資料列則以許多相同集合的欄位（column）所組成。每一個欄位都被指定了特定的資料型別。每一個資料列中欄位的次序是固定的。很重要且必須記得的是，SQL 並不保證資料列在資料表中的次序（雖然他們可以在顯示的時候被明確表現）。

一個資料庫中集合了許多資料表，而很多的資料庫則被一個 PostgreSQL 服務所管理，形成一個資料庫叢集。

如同其他的關連式資料庫產品，PostgreSQL 也支援彙總查詢的功能。彙總查詢指的是能夠把多個資料列的資料經過計算，產生單一結果的功能。舉例來說， count、sum、avg（平均值）、max（最大值）、min（最小值）都是彙總查詢的函式。

這裡的例子，我們可以得到所有低溫中的最大值：

SELECT max(temp_lo) FROM weather;

 max
-----
  46
(1 row)

如果我們想要知道，這個數值是發生在哪一個城市？也許可以試試：

SELECT city FROM weather WHERE temp_lo = max(temp_lo);   

WRONG

不過，這行不通，因為 max 不能使用在 WHERE 條件式當中。（會有這樣的限制，是因為 WHERE 條件式目的是要判斷有哪些資料列的資料應該被彙總計算，所以很明顯地，這件事必須要在彙整計算前發生，這就產生了矛盾。）所以，像本例的查詢一般會使用子查詢（subquery）來取得適當的結果：

SELECT city FROM weather
    WHERE temp_lo = (SELECT max(temp_lo) FROM weather);

     city
---------------
 San Francisco
(1 row)

這樣就對了，因為子查詢是一個獨立的查詢，它可以獨立進行彙總查詢，有別於括號以外的查詢語句。

彙總查詢和 GROUP BY 一起使用會很方便的。舉例來說，我們可以得到每個城市所觀測到的最高氣溫：

SELECT city, max(temp_lo)
    FROM weather
    GROUP BY city;

     city      | max
---------------+-----
 Hayward       |  37
 San Francisco |  46
(2 rows)

這個查詢對每個城市都輸出一列的結果。每一個彙總的結果，將整個資料表，以關連到的城市進行計算。 而我們可以進一步過濾資料內容，使用 HAVING：

SELECT city, max(temp_lo)
    FROM weather
    GROUP BY city
    HAVING max(temp_lo) < 40;

  city   | max
---------+-----
 Hayward |  37
(1 row)

如果限制所有 temp_lo 的數值必須要小於 40 （WHERE temp_lo < 40）的話，也可能得到相同的結果。 最後，如果我們只關心以＂S＂開頭的城市的話，可以這樣做：

SELECT city, max(temp_lo)
    FROM weather
    WHERE city LIKE 'S%'            -- (1)
    GROUP BY city
    HAVING max(temp_lo) < 40;

1. LIKE 運算子進行特徵比對運算，這將會在 9.7. 特徵比對中進一步說明。

這裡很重要的是，瞭解 SQL 中 WHERE 和 HAVING 之間的行為。其根本上的差異是：WHERE 會在合併和彙總計算之前進行選擇資料的動作（也就是它控制著，哪些資料需要被彙總計算）；而 HAVING 是在合併及彙整計算之後，才進行過濾資料的動作。所以，在 WHERE 條件式當中，絕不可以使用彙整運算式；另一方面，HAVING 條件式總是使用彙整運算式。（嚴格來說，你也可以不在 HAVING 條件式中使用彙整運算式，但很少人這樣使用，通常就會改寫到 WHERE 條式件當中，那會更有效率。）

在先前的例子當中，我們可以把城市名稱的限制放在 WHERE 條件式之中，因為它不需要彙總。這將會比放在 HAVING 條件式中更有效率，因為這樣可以避免合併及彙整運算整個表格，不用浪費時間在本來就會被過濾掉的資料上。

「資料表」（table）在關連式資料庫中的角色很接近在紙上畫一個「資料表」：包含了列與欄。欄的數量與次序是固定的，而每個欄位都有一個名稱。列的數量是變動的—它表示在當下有多少資料被存在資料庫中。SQL 並不保證列在資料表中的次序。當讀取資料表的時候，除非明確要求要排序，不然列與列之間是不存在固定的次序。這些將在中進一步說明。進一步來說，SQL 並沒有給每一列一個唯一性的識別，所以在資料表中是有可能存在有完全相同內容的列。這是 SQL 架構下的數學模型結果，通常不是理想的結果。在這章之後，我們會說明如何處理這個問題。

每一個欄位都有一個資料型別。資料型別限制了儲存於該欄位的資料內容，同時也設定了資料儲存的型態，使得該資料可以直接用於計算。舉個例子，一個被宣告為數字型別的欄位，就不能放進任何文字字串，而儲存於此欄位中的資料，可用於數學計算。相反地，一個被宣告為字元字串的欄位，可以儲存任何型能的資料，但就無法用於數學計算了，雖然也有其他操作可以進行字串串接。

PostgreSQL 擁有許多內建的資料型別，可以適應許多應用系統。使用者也可以自訂他們所需的資料型別。大多數內建的資料型別都有顯而易見的名稱與用法，所以我們打算在再做詳細的說明。有一些常用的資料型別，像是 integer 用於整數，numeric 用於浮點數，text 用於字串，date 則是日期，time 是時間，而 timestamp 則同時包含日期和時間。

要建立一個資料表，你可以使用 指令。這個指令你至少要指定一個名稱給新的資料表，還有每一個欄位的名稱與資料型別。例如：

這個建立一個叫作 my_first_table 的資料表，它包含了兩個欄位。第一個欄位叫作 first_column，其資料型別為 text；第二個欄位名稱為 second_column，資料型別為 integer。表格與欄位名稱的規則依 中所介紹的識別字語法，但也有一些例外。注意欄位列表是用逗號分隔，並且包含於括號之中。

當然，前面的例子明顯只是做做樣子而已。一般來說，你會將你的資料表欄位以實際用途來命名，所以我們來看一下更實際的例子：

（numeric 資料型別可以儲存浮點數，用於典型的貨幣計量。）

小技巧

當你建立了許多相關的資料表時，建立最好選擇一個用於命名表格及欄位的規則。舉例來說，有一個規則是使用單數或複數名詞來取名表格，兩者都有些人喜歡使用。

一個資料表中有多少欄位是有限制的，依欄位型別而定，上限通常是 250 個到 1600 個之間。不過，宣告到這麼多的欄位是非常罕見，而且應該是有問題的設定。

企圖要移除一個不存在的資料表，會產生錯誤。不過，在 SQL 腳本中，在建立資料表前嘗試移除是很常見的，通常會忽略錯誤訊息，所以不論資料表是否已經存在，腳本都能如預期執行。（如果你需要的話，你也可以使用 DROP TABLE IF EXISTS 來避免產生錯誤訊息，但這並不是標準 SQL 語法。）

這章中說明 SQL 的使用語法。從這裡建立後續章節所需的理解基礎，然後進一步瞭解 SQL 如何使用去定義及修改資料。

我們也建議已經熟悉 SQL 語法的使用者，仔細地閱讀本章，因為這裡包含了一些有別於其他 SQL 資料庫或專屬於 PostgreSQL 的規則和觀念。

PostgreSQL 允許函數呼叫的時候，使用編號或名稱記號。名稱記號特別好用在於有很多參數的時候，因為它能讓參數與實際的引數有更明確的關連，也更有信賴感。使用編號記號的話，函數呼叫就會依其宣告時的參數次序給予編號；而使用名稱記號的話，參數就會依宣告時的名稱配對，不需要次序對應。

不論哪一種記號方式，如果在宣告時有設定預設值的話，那就不一定要在呼叫時設定其值。不過這點對名稱記號特別好用，因為任何參數的組合都可以省略，而編號記號時就只有從最右邊的參數開始省略。

PostgreSQL 也支援混合式的記號方式，也就是同時使用編號，也使用名稱。在這個例子中，編號的參數會先使用，然後名稱的參數在其之後使用。

接下來的例子，將會描繪所有三種記號方式，都使用下列的函數定義：

CREATE FUNCTION concat_lower_or_upper(a text, b text, uppercase boolean DEFAULT false)
RETURNS text
AS
$$
 SELECT CASE
        WHEN $3 THEN UPPER($1 || ' ' || $2)
        ELSE LOWER($1 || ' ' || $2)
        END;
$$
LANGUAGE SQL IMMUTABLE STRICT;

函數 concat_lower_or_upper 有兩個必要的參數，a 與 b。然後有一個參數是選擇性的，uppercase 的預設值是 false。參數 a 和 b 的文字會被連結起來，然後依 uppercase 的設定，強制轉換為大寫或小寫字母。這個函數定義的其他部份在這裡並不重要（詳情請參閱第 37 章）。

4.3.1. 使用編號記號（Positional Notation）

編號記號是 PostgreSQL 傳統的參數呼叫方式，如下所示：

SELECT concat_lower_or_upper('Hello', 'World', true);
 concat_lower_or_upper 
-----------------------
 HELLO WORLD
(1 row)

所有的參數會依序指定。結果是全大寫，因為 uppercase 設定為 true。另一個例子如下：

SELECT concat_lower_or_upper('Hello', 'World');
 concat_lower_or_upper 
-----------------------
 hello world
(1 row)

這裡的 uppercase 省略了，所以會使用預設值 false，結果就以小寫字母輸出。在編號的記號方式時，參數的省略是由右至左，只有具有預設值的部份才能省略。

4.3.2. 使用名稱記號（Named Notation）

使用名稱作為參數記號方式的話，每一個參數名使用「=>」來指定其所代表的表示式，如下所示：

In named notation, each argument's name is specified using=>to separate it from the argument expression. For example:

SELECT concat_lower_or_upper(a => 'Hello', b => 'World');
 concat_lower_or_upper 
-----------------------
 hello world
(1 row)

再一次省略 uppercase，所以它自動設為 false。使用名稱記號的一項好處就是參數不用固定次數，如下例所示：

SELECT concat_lower_or_upper(a => 'Hello', b => 'World', uppercase => true);
 concat_lower_or_upper 
-----------------------
 HELLO WORLD
(1 row)

SELECT concat_lower_or_upper(a => 'Hello', uppercase => true, b => 'World');
 concat_lower_or_upper 
-----------------------
 HELLO WORLD
(1 row)

有一種比較舊的語法是使用「:=」，因為相容性而保留下來：

SELECT concat_lower_or_upper(a := 'Hello', uppercase := true, b := 'World');
 concat_lower_or_upper 
-----------------------
 HELLO WORLD
(1 row)

4.3.3. 混用記號

混用記號指的就是混合使用編號及名稱來設定參數。然而，如前所述，名稱參數不能先於編號參數。例如：

SELECT concat_lower_or_upper('Hello', 'World', uppercase => true);
 concat_lower_or_upper 
-----------------------
 HELLO WORLD
(1 row)

在上面的查詢中，a 和 b 兩個參數以編號指定，而 uppercase 就以名稱指定。在本例子，只有增加一點點內容而已。使用比較複雜的函數時，會有許多參數設定了預設值，以名稱或混合的方式來設定參數，可以節省許多撰寫的程式碼，也可以減少出錯的可能性。

注意

名稱記號和混用記號目前不能用於彙總函數的呼叫（但如果是用於窗函數是就可以）。

欄位可以指定一個預設值。當新的列被插入，某些欄位卻沒有指定其值時，這些欄位將會被填入相對應的預設值。資料處理的過程中，當有欄位的值不確定時，也會被設定為其預設值。（關於資料處理的詳細內容，請參閱第 6 章。）

如果預設值並沒有明確被指定時，預設值就會是 null。一般來說空值是可接受的情況，因為空值可以表示「未知的資料」的意義。

在表格定義時，預設值接在資料型別後宣告，如下所示：

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric 
DEFAULT 9.99
);

預設值也可以是運算表示式，會在資料插入的同時進行運算（不是在表格建立時）。常見的例子是 timestamp 欄位，會設定一個 CURRENT_TIMESTAMP 的預設值，使其在資料插入時設定為當下的時間。另一個例子是產生「序列數」，這在 PostgreSQL 中，通常以下列語法來表現：

CREATE TABLE products (
    product_no integer 
DEFAULT nextval('products_product_no_seq')
,
    ...
);

這裡的 nextval() 函數會從序列物件取得下一個數字（參閱 9.16 節）。這個例子也常簡化為：

CREATE TABLE products (
    product_no 
SERIAL
,
    ...
);

有關 SERIAL 的簡寫方式，將在 8.1.4 節中說明。

當你建立了一個表格，而你發現出了點錯，或者應用需求有一些改變，那麼你可以移除它再重新建立。但這可能不會一個好的選擇，當表格中已經儲存了許多資料時，或者表格正在被其他的資料庫物件所參考中（例如外部鍵參考）。所以 PostgreSQL 提供了一系列的指令來修改現存的表格。注意到這和更新表格內資料的概念是不同的：在這裡，我們主要針對的是調整表格的定義或結構。

你可以：

• 加入欄位

• 移除欄位

• 加入限制條件

• 移除限制條件

• 改變預設值

• 改變欄位資料型別

• 變更欄位名稱

• 變更表格名稱

所有這些動作都透過 ALTER TABLE 指令來進行，你可以參考該頁面取得詳細資訊。

5.5.1. 加入欄位

要加入一個新欄位，請使用下面的指令：

ALTER TABLE products ADD COLUMN description text;

這個新的欄位預設會以預設值填入（如果你沒有使用 DEFAULT 子句來宣告的話，那會使用 NULL）。

你也可以在新增同時建立限制條件：

ALTER TABLE products ADD COLUMN description text CHECK (description <> '');

事實上，所有在 CREATE TABLE 的選項都可以在這裡使用。要記得的是，預設值必須要符合限制條件的設定，否則這個欄位會無法加入。順帶一提的是，你也可以隨後再加入限制條件（隨後說明），在你更新好新的欄位資料內容後。

小技巧

加入一個欄位，並且設定預設值，會更新表格的裡的每一個資料列（為了存入新的欄位內容）。然而，無預設值的話，PostgreSQL 就不會在實體上真正進行更新的動行。所以如果你的新欄位大多數的內容都不是預設值的話，那麼就建議不要在加入欄位時設定預設值。之後再使用 UPDATE 來分別更新其內容，然後再以隨後的介紹來更新預設值的設定。

5.5.2. 移除欄位

要移除一個欄位，請使用下列指令：

ALTER TABLE products DROP COLUMN description;

不論資料在該欄位是否消滅，表格的限制條件都會同步再次啓動檢查。所以，如果欄位是被外部鍵所參考的話，PostgreSQL 不會就這樣移除它。你可以宣告同步刪去與此欄位相關的物件，加上 CASCADE：

ALTER TABLE products DROP COLUMN description CASCADE;

請參閱 5.13 節，瞭解詳細的處理機制。

5.5.3. 加入限制條件

要加入限制條件，請使用表格限制條件的語法，例如：

ALTER TABLE products ADD CHECK (name <> '');
ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;

要加入 NOT NULL 限制條件的話，就不能寫成表格的限制條件，請使用這樣的語法：

ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;

加入的限制條件會立即開始檢查，所以當下的資料內容必須要能符合條件才能加入成功。

5.5.4. 移除限制條件

要移除限制條件，你需要先知道它的名稱。如果你在宣告時有命名的話，那就使用那個名稱，否則你得找出系統自動命名的名稱。其所使用的指令為「\d tablename」，會列出表格相關的資訊。或使用其他的資料庫工具應該也可以找到它。找到之後請使用下列指令來移除限制條件：

ALTER TABLE products DROP CONSTRAINT some_name;

（如果你的限制條件名稱像是「$2」這樣的，不要忘記使用雙引號括住，使其可以正確地被識別為是名稱。）

在移除欄位時，你需要加入 CASCADE，如果你需要同步移除相關的限制條件的話。像是外部鍵就會依賴另一個唯一性限制或主鍵的限制條件。

下面這可以用在移除 NOT NULL 限制的欄位：

ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;

(記得 NOT NULL 是沒有名稱的。)

5.5.5. 變更欄位預設值

要設定新的欄位預設值，請使用下面指令：

ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;

注意這並不會影響到已經存在的資料，只有隨後新增的資料才會使用。

要移除任何預設值，請使用：

ALTER TABLE products ALTER COLUMN price DROP DEFAULT;

這個指令會把預設值設為空值。因為預設值本來就設為空值，所以即使刪去一個未設定預設值欄位的預設值，也不會是一種錯誤。

5.5.6. 變更欄位資料型別

要變更欄位成為另一個資料型別，請使用下列指令：

ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);

這只有在欄位內容可以被自動轉換型別時才會成功。如果存在比較複雜的轉換時，你需要加上 USING 子句來指示如何轉換資料內容。

PostgreSQL 會企圖轉換欄位預設值到任何新的型別，而所有的限制條件也會啓動檢查機制。但這些轉換可能會失敗，也可以產生意外的結果。比較好的作法是，先移除限制條件，再變更資料型別，最後再重新加入適當調整後的限制條件。

5.5.7. 變更欄位名稱

要變更某個欄位的名稱：

ALTER TABLE products RENAME COLUMN product_no TO product_number;

5.5.8. 變更表格名稱

要變更表格的名稱：

ALTER TABLE products RENAME TO items;

這一章涵蓋了如何建立資料庫結構。在關連式資料庫中，原始資料儲存在表格之中，所以在這一章裡，主要說明表格如何建立及調整，以及有什麼樣的功能可以操控所存放的資料。再來我們會討論表格如何以結構來管理，以及權限的設定。最後，我們會簡短地看一下其他的功能如何影響資料儲存，像是繼承、表格分割、view、函數、還有觸發函數。

Generated column (自動欄位)是特殊的欄位，它的內容由其他欄位的內容計算得出。相對於資料表來說，就是欄位形態的 View。Generated column 有兩種：stored 和 virtual。 Stored 的自動欄位在寫入（插入或更新）時進行計算，會像正常欄位一樣佔用儲存空間。Virtual 的自動欄位則不佔用任何儲存空間，而是在讀取時會對其進行計算。因此，虛擬的自動欄位類似於檢視表(view)，而儲存的自動欄位則類似於具體化檢視表(materialized view)（但會自動更新）。 PostgreSQL 目前僅實作了儲存的自動欄位。

To create a generated column, use the GENERATED ALWAYS AS clause in CREATE TABLE, for example:

CREATE TABLE people (
    ...,
    height_cm numeric,
    height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED
);

必須指定關鍵字 STORED 來作為自動欄位的儲存型別。相關的詳細說明，請參閱 CREATE TABLE。

自動欄位無法直接寫入資料。在 INSERT 或 UPDATE 命令中，不能為自動欄位指定內容，但可以指定關鍵字 DEFAULT。

Consider the differences between a column with a default and a generated column. The column default is evaluated once when the row is first inserted if no other value was provided; a generated column is updated whenever the row changes and cannot be overridden. A column default may not refer to other columns of the table; a generation expression would normally do so. A column default can use volatile functions, for example random() or functions referring to the current time; this is not allowed for generated columns.

Several restrictions apply to the definition of generated columns and tables involving generated columns:

• 自動欄位的表示式只能使用 immutable 函數，不能使用子查詢或以任何方式引用同筆資料以外的任何內容。

• 自動欄位的表示式不能引用另一個自動欄位。

• 自動欄位的表示式不能引用系統欄位（tableoid 除外）。

• 自動欄位不能有欄位預設值或識別定義。

• 自動欄位不能是分割區主鍵的一部分。

• 外部資料表可以具有自動欄位。有關詳細資訊，請參閱 CREATE FOREIGN TABLE。

• For inheritance:

  • If a parent column is a generated column, a child column must also be a generated column using the same expression. In the definition of the child column, leave off the GENERATED clause, as it will be copied from the parent.

  • In case of multiple inheritance, if one parent column is a generated column, then all parent columns must be generated columns and with the same expression.

  • If a parent column is not a generated column, a child column may be defined to be a generated column or not.

其他注意事項適用於自動欄位的使用。

• 自動欄位與其一般欄位分開維護存取權限。因此，可以對其進行安排，以便設定可以從自動欄位中讀取，但不能從一般欄位中讀取的特定角色。

• 從概念上講，在執行事件觸發器之前，會先更新自動欄位。因此，在 BEFORE 觸發器中對基本欄位所做的更新將先反映在自動欄位中。但是相反地，不允許在觸發器之前讀取自動欄位。

參數表示式用在許多不同的方面，像是 SELECT 指令中的回傳列表；在 INSERT 或 UPDATE 指令中指定欄位的新值；又或是在一些命令中，指出搜尋的條件等。參數表示式的結果，有時候會被稱作 scalar，以有別於表格表示式（就是一個表格）的結果。參數表示式也可以稱作 scalar expressions（賦值表示式），甚或簡化為 expressions （表示式）。表示式的語法容許其值為各種運算的單一結果，如數學、邏輯、集合、或其他運算。

參數表示式可以是下列的其中一種形態：

• 常數或文字內容

• 欄位的引用

• 函數參數的引用，在函數裡或預備指令（prepared statement）中

• 子參數表示式

• 欄位選擇表示式

• 運算子宣告

• 函數呼叫

• 彙總表示式

• 窗函數呼叫

• 型別轉換

• 校對轉換（collation expression）

• 賦值子查詢（scalar subquery）

• 陣列建構式

• 列建構式

• 其他被括號括住的參數表示式（用於群組子表示式和強制調整運算優先權）

4.2.1. 欄位引用

要引要一個欄位的話，請使用下列的形式：

4.2.2. 函數參數引用

函數參數的引用，用來指定一個不在該 SQL 指令中的值。參數是使用在 SQL 函數定義或預備查詢之中。有一些用戶端函式庫也支援將資料數值與 SQL 指令分離，在這種情境下，參數就會用來指向外部的資料數值。參數引用的形式如下：

舉個例子，有一個函數 dept 的宣告如下：

這裡的 $1 指的是函數被呼叫時的第 1 個輸入參數：

4.2.3. 子參數表示式（Subscripts）

如果表示式要產生陣列的結果的話，指定陣列中某個元素，請使用：

或是要取得陣列中多個相隣的元素，請使用：

每一個「subscript」本身都是一個表示式，必須要產生一個整數值。

一般來說，陣列表示式必須被括號起來，但如果該表示式只是一個欄位或參數的引用的話，那麼括號可以省略。然後，多個子參數表示式可以連在一起使用，當你需要陣列表達多維度的概念時。舉例如下：

4.2.4. 欄位選擇

如果一個表示式產生了複合性的型別（列型別），那麼要指定其中的某個欄位時，請使用：

一般來說，列的表示式必須被括號起來，但如果該表示式只是一個欄位或參數的引用的話，那麼括號可以省略。舉例如下：

（然而，有限制的欄位引用，實際上就是一種欄位選擇語法的特列。）有一種重要的特例是從某個複合型別的表格欄位中取其子欄位的值：

在這裡，括號是必要的，以表示 compositecol 是一個欄位名稱，但不是表格名稱。而在第二個例子中，mytable 是表格名稱，而非結構名稱。

你可以取得複合資料的所有欄位值，使用「.*」：

4.2.5. 運算子宣告（Operator Invocations）

有三種用來進行運算子宣告的語法：

4.2.6. 函數呼叫

函數呼叫的語法是，函數的名稱（可能還會加上結構名）接著一連串用括號括起來的參數列表：

舉個例子，下面的函數呼叫可以計算 2 的平方根：

注意

4.2.7. 彙總表示式

彙總表示式用在查詢時，過濾資料進行彙總函數計算的應用。彙總函數壓縮了大量資料輸入成為一個單一的輸出值，例如加總或平均數。彙總表示式的語法可以是下列其中之一：

這裡的 agregate_name 是預先就定義好的（可能還需要加上結構名稱），表示式可以是任何的函數形態，但不能包含彙總函數或窗函數。而 order_by_clause 和 filter_clause 後續進行說明。

第一種形式的彙總表示式用於每次輸入一列的情況；第二種形式和第一種相同，當 ALL 是預設的時候；第三種形式彙總不重覆的資料（或在多種表示式的時候，取不重覆的集合）；第四種形式也是每次輸入一列，但沒有限定輸入條件，通常是用於 count(*)；最後一種形式用於有次序的彙總函數，稍後說明。

大多數的彙總函數會忽略空值，所以如果表示式計算的結果是空值的話，就會忽略不計。這樣的假設除非有特別設定，對所有內建的函數都是如此。

舉例來說，count(*) 計算輸入列的個數，而 count(f1) 是計算輸入列中 f1 欄位非空值的個數，因為 count 會忽略空值；然而，count(distinct f1) 則是計算 f1 欄位不重覆又非空值的個數。

通常彙總函數在處理輸入資料時，都是未排序過的。在大多數的情況沒有關係，例如：min 最小值的計算，與其輸入的次序沒有關係。然而，還是有些彙總函數的結果，與其處理次序是有關連的，例如：array_agg 和 string_agg。ORDER BY 字句就可以達到此效果，其與一般查詢語法 ORDER BY 的用法相同，詳細說明在 7.5 節，除非該表示式無法輸出成欄位名稱或數字。舉例如下：

操作到多參數的彙總函數時，注意 ORDER BY 會處理過所有的彙總參數，例如：

但不能這樣寫：

這在語法上沒有不合法，但這表示一個單參數的彙總函數，使用了兩個排序的關鍵值（第二個完全沒用，因為它是常數）。

如果 DISTINCT 被加到 ORDER BY 子句裡的話，那麼所有的 ORDER BY 表示式都必須符合彙總函數的參數，也就是說，你不能使用不在 DISTINCT 列表中的表示式來排序。

注意

在彙總函數中使用 DISTINCT 和 ORDER BY，都是 PostgreSQL 的延伸。

把 ORDER BY 放進彙總函數的參數列表中，就如同到目前為止的描述，用於排序輸入值，進行一般性的處理或統計彙總，而排序是選擇性的。有另一種類型的彙總函數稱作有次序彙總，它們就必須要有 ORDER BY 子句，通常就是因為這些函數的計算結果，只會對某些特定次序的資料產生效果。典型的有次序彙總例子，包含排名和累計百分比計算。對於有次序彙總計算，將 ORDER BY 字句寫進 WITHIN GROUP (...) 中，如同上述最後一個語法例子。在 ORDER BY 子句中的表示式會處理每一筆輸入資料，如同一般的彚總函數，然後將其依子句中的表示式計算並排序，最後再依序轉送給彙總函數處理。（這和非處理 WITHIN GROUP 中的 ORDER BY 不同，它們不會再轉送給彙總函數。）如果有在 WITHIN GROUP 之前的表示式的話，稱作直接參數，會和有 ORDER BY 的參數有區分。不像一般的彙總參數，直接參數只會被處理一次，而不是每一筆都一次。這意思是只有在 GROUP BY 中，這些變數才會被彙總處理。這樣的限制就如同直接參數不在彙總表示式之中一樣。直接參數一般用於累計分配，只有在每一次彙整完的值才有意義。直接參數可以是空值，在這個例子中，使用的是 ()，而非 (*)。（PostgreSQL 兩種寫法都可以接受，但標準 SQL 只接受前者。）

有次序彙總查詢如下：

這裡包含了 50% 的累計，或是中間數累計，來源是表格 households 的 income 欄位。其中，0.5 是直接參數，它不影響百分累計彙整計算過程。

如果使用了 FILTER，那就只有符合 FILTER 子句條件的資料會被彙總處理，其他的資料都會被忽略掉。舉例來說：

彙總表示式只可以用於結果列表或 SELECT 中的 HAVING 子句。在其他子句中是被禁止的，像是 WHERE，因為這些子句邏輯上都是在彙總處理前就得處理資料。

4.2.8. 窗函數(Window Function)

窗函數(Window Function)呼叫指的是使用類似彙總函數的使用方式，只是僅用於查詢中部份列的選擇上。和非窗函數不同的是，這並不會只輸出為單一列—每一列都仍然分開輸出。然而，窗函數也是處理了所有該列所屬群組的其他列（PARTITION BY），依其窗函數所定義的範圍。窗函數呼叫的方式可以是下列其中之一：

要定義「窗(Window)」，請使用下列語法：

選擇性的 frame_clause 語法如下：

frame_start 及 frame_end 可以是下列語法之一：

而 frame_exclusion 可以是下列語法之一：

在這裡的表示式（expression），除了不能再包含窗函數之外，並無其他特別限制。

PARTITION BY 子句將查詢分組成為不同的分區，它們將會分別地被窗函數所處理。PARTITION BY 的行為和查詢語句中的 GROUP BY 很類似，除了它的表示式就只是表示式，而且不能產出欄位名稱或編號。沒有 PARTITION BY 的話，所有的列都會被當作一個分組進行彙總。ORDER BY 子句決定窗函數的處理次序，它也和查詢語句中的 ORDER BY 很類似，但它不能使用輸出的欄位或編號。如果沒有 ORDER BY 的話，就無法保證彙總處理的次序了。

frame_clause 指的是構成該窗的資料列，再進一步以「窗框（window frame）」拆分，是目前分區(partition)的子集合。對窗函數而言，運算會以窗框的範圍取代整合分區。窗框的指定可以是 RANGE 或 ROW 兩種模式。不論哪種模式，都 frame_start 執行到 frame_end，但如果 frame_end 省略了，預設就是到目前的列（CURRENT ROW）。

UNBOUNDED PRECEDING 的窗框始於該分區的第一列，同樣地，UNBOUNDED FOLLOWING 意指窗框結束於分區的最後一列。

在 RANGE 或 GROUPS 模式裡，如果 frame_start 設定為 CURRENT ROW 的話，表示窗框始於目前列同序的那一列（使用 ORDER BY 時，排序相同的那一列），同理，frame_end 設定為 CURRENT ROW 時，表示窗框止於排序相同的列。而在 ROWS 模式時，CURRENT ROW 指的就是自己。

In the offset PRECEDING and offset FOLLOWING frame options, the offset must be an expression not containing any variables, aggregate functions, or window functions. The meaning of the offset depends on the frame mode:

• In ROWS mode, the offset must yield a non-null, non-negative integer, and the option means that the frame starts or ends the specified number of rows before or after the current row.

• In GROUPS mode, the offset again must yield a non-null, non-negative integer, and the option means that the frame starts or ends the specified number of peer groups before or after the current row's peer group, where a peer group is a set of rows that are equivalent in the ORDER BY ordering. (There must be an ORDER BY clause in the window definition to use GROUPS mode.)

• In RANGE mode, these options require that the ORDER BY clause specify exactly one column. The offset specifies the maximum difference between the value of that column in the current row and its value in preceding or following rows of the frame. The data type of the offset expression varies depending on the data type of the ordering column. For numeric ordering columns it is typically of the same type as the ordering column, but for datetime ordering columns it is an interval. For example, if the ordering column is of type date or timestamp, one could write RANGE BETWEEN '1 day' PRECEDING AND '10 days' FOLLOWING. The offset is still required to be non-null and non-negative, though the meaning of “non-negative” depends on its data type.

In any case, the distance to the end of the frame is limited by the distance to the end of the partition, so that for rows near the partition ends the frame might contain fewer rows than elsewhere.

Notice that in both ROWS and GROUPS mode, 0 PRECEDING and 0 FOLLOWING are equivalent to CURRENT ROW. This normally holds in RANGE mode as well, for an appropriate data-type-specific meaning of “zero”.

The frame_exclusion option allows rows around the current row to be excluded from the frame, even if they would be included according to the frame start and frame end options. EXCLUDE CURRENT ROW excludes the current row from the frame. EXCLUDE GROUP excludes the current row and its ordering peers from the frame. EXCLUDE TIES excludes any peers of the current row from the frame, but not the current row itself. EXCLUDE NO OTHERS simply specifies explicitly the default behavior of not excluding the current row or its peers.

The default framing option is RANGE UNBOUNDED PRECEDING, which is the same as RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. With ORDER BY, this sets the frame to be all rows from the partition start up through the current row's last ORDER BY peer. Without ORDER BY, this means all rows of the partition are included in the window frame, since all rows become peers of the current row.

PRECEDING 和 FOLLOWING 兩個設定值，目前只能用在 ROWS 模式。它們指的是窗框的起迄於指定的一個值，表示目前列之前後多少列。而所謂的值，必須是整數表示式而不包含任何變數、彙總函數、或窗函數。其值也不能是空值或負值，但可以為零，表示只處理目前列。

預設的窗框設定是 RANGE UNBOUNDED PRECEDING，和 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 是一樣的。加上 ORDER BY 的話，這可以讓窗框起於和目前列並列的列；沒有 ORDER BY 的話，所有的列都會在分區裡，因為如此就無法判定次序，表示大家都一樣。

frame_start 的限制是不能使用 UNBOUNDED FOLLOWING，而 frame_end 不能使用 UNBOUNDED PRECEDING。frame_end 的設定也不能先於 frame_start—舉例來說，RANGE BETWEEN CURRENT ROW，使用 PRECEDING 就不可以。

如果有使用到 FILTER 的話，就只有符合 FILTER 條件式的列會被窗函數處理，其餘的列都會被忽略。只有彙總式的窗函數可以使用 FILTER 子句。

「*」語法的使用，用來把無參數的彙總函數當作窗函數來使用，例如：count(*) OVER (PARTITION BY x ORDER BY y)。「*」通常不會用於專門的窗函數上，專門的窗函數不允許參數裡有用到 DISTINCT 或 ORDER BY 的語法。

窗函數呼叫只限於 SELECT 回傳列表，及 ORDER BY 子句中。

4.2.9. 型別轉換

型別轉換指定從一種資料型別轉換為另一種資料型別。PostgreSQL 接受兩種用於型別轉換的等效語法：

CAST 語法符合 SQL 標準；帶「::」的語法是 PostgreSQL 既有的用法。

如果對於值表示式必須產生的型別沒有歧義（例如，當它被分配給資料表欄位），通常可以省略顯式的型別轉換；系統將在這種情況下自動套用型別轉換。但是，只有在系統目錄中標記為「可以隱式套用」的強制轉換才會執行自動強制轉換。其他強制轉換必須使用顯式強制轉換語法來使用。此限制旨在防止系統默默地套用令人意外的轉換。

也可以使用函數式語法來指定型別轉換：

但是，這僅適用於名稱也可以作為函數名稱使用的型別。例如，雙精度不能用這種方式，但等價的 float8 可以。而且，由於語法衝突，名稱間隔，時間和時間戳記只能使用雙引號才能用於這種方式。因此，使用類似功能的轉換語法會導致不一致，因此可能應該避免。

注意

4.2.10. 排序表示式

COLLATE 子句用於覆蓋排序規則的表示式。它附加到所套用的表示式上：

排序規則是一種可以綱要限定識別指標。COLLATE 子句比運算子更緊密；必要時可以使用括號。

如果沒有明確指定排序規則，那麼資料庫系統會從表示式中涉及的欄位中衍生一個排序規則，或者如果表示式中未包含任何欄位，則預設為資料庫的預設排序規則。

COLLATE 子句的兩個常見用法是重寫 ORDER BY 子句中的排序順序，例如：

並覆蓋具有語言環境特性結果的函數或運算子呼叫的排序規則，例如：

但是這會有錯：

因為它試圖將排序規則應用於「>」運算子的結果，該運算符是不可排序的布林資料型別。

4.2.11. Scalar 子查詢

例如，以下是每個州中最大的城市人口數量：

4.2.12. 陣列建構函數

陣列建構函數是一種使用其成員元素的值建構陣列的表示式。一個簡單的陣列建構函數由關鍵字 ARRAY，左方括號 [，陣列元素值的表示式列表（用逗號分隔），最後一個右方括號 ] 組成。例如：

可以透過巢狀的陣列建構函數來建構多維陣列。在內部的建構函數中，關鍵字 ARRAY 可以省略。例如，這些語法會產生相同的結果：

由於多維陣列必須是矩形，因此同一級別的內部建構函數必須産生具有相同維數的子陣列。套用於外部 ARRAY 建構函數的任何強制型別都會自動轉送給所有內部建構函數。

多維陣列建構函數的元素可以是任何產生適當型別陣列的東西，不僅只是一個子 ARRAY 結構。例如：

你可以建構一個空陣列，但由於不可能有一個沒有型別的陣列，所以你必須明確地將你的空陣列轉換為所需的型別。例如：

也可以從子查詢的結果中建構一個陣列。在這種形式下，陣列建構函數使用關鍵字 ARRAY 和小括號（不是中括號）的子查詢寫入。例如：

子查詢必須回傳一個資料列。如果子查詢的輸出欄位是非陣列型別，則産生的一維陣列將具有子查詢結果中每個資料列的元素，其元素型別與子查詢的輸出欄位匹配。如果子查詢的輸出欄位是一個陣列型別，則結果將是一個相同型別的陣列，但會是一個更高的維度；在這種情況下，所有子查詢資料列都必須産生具有相同維度的陣列，否則結果將不是矩形。

4.2.13. 資料列建構者

資料列建構函數是一個表示式，它使用其成員字串的值建構資料列內容（也稱為複合值）。資料建構函數由關鍵字 ROW，左括號，資料列字串的零個或多個表示式（以逗號分隔）所組成，最後則是右括號。例如：

當列表中有多個表示式時，關鍵詞 ROW 是選用的。

注意

在 PostgreSQL 8.2 之前，._ 語法在資料列建構函數中不會展開，因此寫了ROW(t._, 42) 會建立一個兩個字串欄位的資料列，其第一個是欄位是另一個資料列值。新的建構行為通常更有用。如果您需要嵌套資料列值的舊行為，請不要使用 .* 的內部資料列值，例如 ROW(t, 42)。

預設情況下，由 ROW 表示式建立的值是匿名記錄型別。如有必要，可將其轉換為指定的複合型別 - 資料表的資料列型別或使用 CREATE TYPE AS 建立的複合型別。可能需要明確表示以避免歧義。例如：

資料列建構函數可用於建構要儲存在複合型別資料表欄位中的複合內容，或者要傳遞給接受複合參數的函數。此外，可以比較兩個資料列值或用 IS NULL 或 IS NOT NULL 來測試資料列，例如：

4.2.14. 表示式運算規則

並沒有定義子表示式的運算順序。特別是，運算子或函數的輸入不一定是從左到右或以任何其他固定順序進行運算。

進一步來說，如果一個表示式的結果可以透過只運算它的某些部分來得到，那麼其他子表示式可能根本就不會被運算。 例如，如果有人寫了：

那麼 somefunc() 將（可能）根本不會被呼叫。如果有人寫了：

請注意，這與在某些程語言中發現的布林運算是從左到右的「短路」不同。

因此，將具有副作用的函數用作複雜表示式的一部分是不明智的。在 WHERE 和 HAVING 子句中依賴副作用或運算順序是特別危險的，因為這些子句作為製定執行計劃的一部分經常式會被重新運算。這些子句中的布林表示式（AND / OR / NOT 組合）可以按照布林代數法則的任何方式重新組織。

但這樣是安全的：

以這種方式使用的 CASE 構造將放棄最佳化嘗試，因此只能在必要時進行。（在這個特定的例子中，透過改寫為 y> 1.5 * x 來避免這個問題會更好。）

由於查詢規劃試圖簡化常數子表示式，因此即使資料表中的每一個資料列都具有 x> 0，以至於在執行時永遠不會走到 ELSE，也可能導致除以零的例外情況。

雖然這個特殊的例子看起來很愚蠢，但是在函數中執行的查詢中可能會出現不明顯涉及常數的情況，因為函數參數和局部變數的值可以作為常數插入到查詢中以用於查詢規劃。例如，在 PL/pgSQL 函數中，使用 IF-THEN-ELSE 語句來保護有風險的運算要比將它嵌套在 CASE 表示式中要安全得多。

同一種類型的另一個限制是，CASE 無法阻止運算其中包含的彙總表示式，因為需要在 SELECT 資料列表或 HAVING 子句中的其他表示式之前計算彙總表示式。例如，下面的查詢可能會導致一個除以零例外情況，儘管似乎已經受到保護：

min() 和 avg() 彙總運算是在所有輸入的資料列上同時計算的，因此如果任何員工的資料等於零，則在有任何測試 min() 結果的機會之前，發生除以零的錯誤。相反，使用 WHERE 或 FILTER 子句來防止有問題的輸入資料列，將可以在彙總函數之前來預防這種情況發生。

每一個表格都有幾個系統欄位，而它們是由資料庫系統預先定義好的，所以使用者在定義欄位名稱時，不能使用這些名字。（這些限制並不是因為它們是保留關鍵字，所以就算用引號括起來也不能使用。）但在一般使用時，你也不需要特別考慮這些欄位，只要瞭解會有這些欄位存在就好。

oid

每一個資料列會有一個 Object ID，不過這個欄位只有在建立表格時，加上 WITH OIDS 語法才能使用。或者也可以藉由參數 來切換使用。這個欄位的型別是 oid（和欄位名相同）。參閱 瞭解詳細資訊。

tableoid

每個表格也有一個 ID 也會記錄在每一個資料列中。這個欄位特別方便在取得表格的繼承結構（參閱 ），如果沒有這個欄位的話，要去找出資料列的來源就會很麻煩。tableoid 可以參考 pg_class 表格中的 oid 欄位，進一步取得表格的名稱。

xmin

這指的是資料列在插入資料的版本資訊。（每一個資料列的版本，都是一個獨立的資料狀態；每一次資料的更新，都會在邏輯層產生一個新的資料列版本。）

cmin

指令識別碼，會存在於新增資料的交易中。（從 0 開始）

xmax

刪除資料的交易版本資訊，如果是 0 的話，代表讓資料列不是刪除中的資料列版本。這通常是用來指出某個刪除的交易還未被完成，或某個刪除正在被回復。

cmax

指令識別碼，有數字的話表示一個刪除的交易指令，或是 0。

ctid

表示每一個資料列存在於該表格的實體位址。注意到的是，雖然 ctid 可以用來快速找到特定的資料列版本，但 ctid 是會改變的，如果有執行過 VACUUM FULL 的話。所以 ctid 如果要用於固定的資料定位的話，是不應該被考慮的選項。OID 或額外自訂序列數字，更適合用於分別邏輯上的資料列。

OID 是一個 32 位元的數字，以 cluster 為單位配發。在一個大型或長期使用的資料庫中，是有可能出現重覆的情況。所以，假設 OID 是唯一的識別是不正確的觀念，除非你還有搭配其他方法來確保唯一性。如果你需要識別表格中的資料列的話，使用序列數產生器是比較建議的作法。OID 也可以這樣用來得到一些額外的預防性功能：

• 唯一性的限制應該設定在 OID 欄位上，來確保每一個 OID 可以識別每一個資料列。當有唯一性限制存在的時候，對於已經存在的資料列就不會有重覆的 OID。（當然，這方法只能用於資料筆數在 40 億筆以下的表格。不過實務上的表格多數都少於這個數目，而且太多資料的話，效果也會變得很差。）

• OID 在多個表格間就不能假設為是唯一，你應該搭配 tableoid 來識別資料庫層級的唯一性。

• 當然，在建立表格時必須要加入 WITH OIDS 語法。在 PostgreSQL 8.1 之前，WITHOUT OIDS 是預設值。

交易識別碼也是 32 位元的數字。在一個長期運行的資料庫中，交易識別碼也可能會重覆。只要有適當的管理機制的話，這並不會是什麼嚴重的問題，詳情請參閱第 24 章。然而，長期來說（超過 10 億個交易），假定交易識別碼的唯一性是不明智的作法。

指令識別碼也是 32 位元的數字，其絕對上限是約 40 億個指令在一個交易當中，實務上這個限制並不會是問題。注意到這個限制是 SQL 指令數量的限制，而不是處理資料的限制。只有真正有改變資料庫內容的指令才會有指令識別碼。

資料型別是一種限制資料如何被儲存在表格中的方式。然而，對許多應用來說，這樣的限制還是不夠細膩。舉個例子，一個欄位包含了產品價格，當然它必須只能是正整數，但並沒有標準的資料型別可以只限制在正整數。另一個需求是，你可能想要限制的條件是依據其他的資料而定。舉例來說，在表格中的產品資訊，每一個產品編號都不能重覆。

所以，SQL 允許你在表格和欄位上定義額外的限制條件，它幫助你對資料有更多的控制能力。當某個使用者輸入資料時，違反了限制條件，錯誤訊息就會產生。這些限制條件也會限制預設值的設定。

5.4.1. 檢查

使用 CHECK 是最普遍的限制條件製定方式，它可以允許你指定某個欄位必須符合某個布林條件式的判斷。舉個例子，要滿足產品價格是正數的話，你可以使用這樣的語法：

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0)
);

如同你所看到的，限制條件會接在資料型別之後，就像是預設值的設定一樣。預設值和限制條件的設定，在語法撰寫上沒有先後次序。檢查限制條件使用關鍵字 CHECK，然後接著是一組以括號括起來的條件式。其條件式應該要包含被限制的欄位，不然就沒有任何意義。

你也可以讓該限制條件擁有另一個名稱，這樣的好處是，當錯誤訊息發生時，你可以明確得到是哪一個限制被違反了：

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric 
CONSTRAINT positive_price CHECK (price > 0)
);

如上，給予這個限制條件一個名稱，使用關鍵字 CONSTRAINT，緊接著一個限制條件的定義。（如果你沒有自行命名，系統也會自動取一個名字）

一個限制條件可以參考多個欄位。例如你設定了標準價格和優惠價格，而你需要確保優惠價格一定是比標準價格要便宜的話：

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric CHECK (discounted_price > 0),
    CHECK (price > discounted_price)
);

前兩個限制條件和前述很類似，而第三個是新的語法。它並不是只參考某個特定的欄位，而是以逗號分隔列出所有需要遵守的條件。欄位的定義和限制條件的定義，撰寫上沒有規定次序。

我們會說前兩個是欄位的限制，而第三個是表格的限制，因為它是獨立於其他的欄位定義的。欄位限制也可以寫成表格的限制方式，不過反過來通常就不行，因為一個欄位的限制，指的就是只參考到語法上它所接續的欄位而已。（PostgreSQL 並沒有強制這樣做，但如果你的語法與其他資料庫共用的話，最好還是依這樣的語法避免混用。）上面的例子也可以改寫成如此：

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0),
    CHECK (price > discounted_price)
);

或等同於：

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0 AND price > discounted_price)
);

都可以照你所喜愛的語法撰寫。

命名表格的限制條件和欄位限制條件的命名是一樣的：

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0),

    CONSTRAINT valid_discount CHECK (price > discounted_price)
);

應該要注意的是，檢查限制條件是否成立，端看條件表示式在運算後是真值（true）還是空值（null）。因為當有運算元是空值時，多數的運算結果都是空值，所以可能會有空值產生在想要限制條件的欄位之中。要確保欄位中不會出現空值的話，請參閱下一段的說明。

5.4.2. 限制無空值

限制無空值只要以下方的語法設定，就可以限制欄位不得存在空值的輸入：

CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric
);

限制無空值的語法，只能使用在欄位限制上。而限制無空值等效於以 CHECK 建立一個限制條件式為（IS NOT NULL），但在 PostgreSQL 明確使用 NOT NULL 語法的話，處理會更快速。只是它的缺點是你無法給予這樣的限制一個自訂的名稱。

當然，一個欄位可以有一個以上的限制條件。只要一個接著一個即可：

CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric NOT NULL CHECK (price > 0)
);

撰寫的次序沒有關係，也不需要去計較限制被檢查的次序。

NOT NULL 有一個相反的語法：NULL。這並非表示欄位裡只能是空值，如果這樣的話就完全沒用處了。其實這是一種簡化，將預設值設定為空值。NULL 語法並不是 SQL 標準的一部份，所以請不要用在可移植式的應用程式裡。（這僅是 PostgreSQL 為了相容其他資料庫而增加的功能）然而，有一些使用者喜歡使用它，因為在程序檔的撰寫上，很容易利用這個語法來切換限制條件。舉個例子，你可以先寫下：

CREATE TABLE products (
    product_no integer NULL,
    name text NULL,
    price numeric NULL
);

然後在需要的時候再適時加入 NOT 關鍵字即可。

小技巧

在多數資料庫設計原則上，主要欄位都應該被標示為 NOT NULL。

5.4.3. 限制唯一性

限制唯一性，確保在某個欄位或某一群欄位的資料，是在該表格中獨一無二的。語法如下：

CREATE TABLE products (
    product_no integer UNIQUE,
    name text,
    price numeric
);

這是欄位限制的語法。而：

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
UNIQUE (product_no)
);

則是表格限制的寫法。

如果想要限制一群欄位的唯一性的話，請使用表格限制的語法，欄位名稱以逗號分隔：

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
UNIQUE (a, c)
);

這表示這些欄位所包含的內容組合，在整個表格中是具有唯一性的，但任何一個欄位本身並不一定具備唯一性。

你可以命名唯一性的限制條件，語法如下：

CREATE TABLE products (
    product_no integer CONSTRAINT must_be_different UNIQUE,
    name text,
    price numeric
);

加入唯一性的限制條件，將會自動建立一個具唯一性的 B-tree 索引，其包含的欄位就如限制條件中所條列的欄位。這樣唯一性限制的語法並不能只限制某部份列的唯一性，但如果使用「部份索引 （partial index） 」的話就可以做到。

一般來說，唯一性被違反的情況是，所限制的欄位在表格中，有超過一列的資料是相等的。不過，空值並不會被計算在內。這表示說，即使設定了唯一性的限制，在被限制的欄位中，還是有可能會有多個列的資料是空值。這個設計源自 SQL 標準，但聽說有其他的 SQL 資料庫並不是這樣的規則。所以，如果要移植這個語法到其他資料庫的話，要注意這項設計有無差異。

5.4.4. 主鍵（Primary Keys）

主鍵的意思是，某一個欄位或某一群欄位，在整個表格中，其每一列的組合都是唯一的，且有宣告唯一性的限制條件，並且也包含了非空值的條件（UNIQUE 及 NOT NULL）。所以，下面的兩種語法對資料的意義相同：

CREATE TABLE products (
    product_no integer UNIQUE NOT NULL,
    name text,
    price numeric
);

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

主鍵也可以包含多個欄位，語法和宣告唯一限制條件類似：

CREATE TABLE example (
    a integer,
    b integer,
    c integer,

PRIMARY KEY (a, c)
);

加入主鍵時，會自動建立一個具唯一性的 B-tree 索引，範圍為 PRIMARY KEY 語法所定義的欄位，並且會強制將這些欄位設定為非空值（NOT NULL）。

一個表格只能有一個主鍵。（你可以使用 UNIQUE 及 NOT NULL 設定多個同樣的限制條件，在功能上幾乎是相同的，但只能有一組條件是由 PRIMARY KEY 所定義。）關連式資料庫的理論指出，每一個表格都必須要有一個主鍵。這個規則在 PostgreSQL 中並不是強制的，但通常建議最好遵循這個理論。

主鍵在用戶端文件式的資料處理上是很有用的。舉個例子，一個圖型化介面讓使用者可以修改資料，那麼可能就需要主鍵來確認每一列的唯一性，而不致於產生混淆。也有一些用途是在資料庫系統的管理上，例如，主鍵會用於外部鍵（Foreign Keys）的處理，使其可以處理表格與表格間的資料對應問題。

5.4.5. 外部鍵（Foreign Keys）

外部鍵指的是某個欄位或某一群欄位的內容，必須在另一個表格相對欄位之中，存在相同內容的資料。我們會說這樣的行為是在維護兩個表格之間的關連性。

就使用我們已經使用多次的產品表格吧：

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

讓我們假設你有一個表格用來儲存這些產品的訂單，我們要確保這些訂單內的產品確實存在。所以我們定義一個外部鍵來關連訂單的表格和產品的表格：

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products (product_no),
    quantity integer
);

這樣的話，如果 product_no 沒有出現在產品表格的話，就無法建立資料了。

我們會說像這樣的情況是，訂單表格是引用表格（referencing table），而產品表格是參考表格（referenced table）。相對地，欄位也稱為引用欄位（referencing columns）及參考欄位（referenced columns）。

你可以將這個語法簡化為：

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products,
    quantity integer
);

因為在參考表格中，不在主鍵欄位組合中的欄位，就是參考欄位。

外部鍵也可以參考一組欄位。一般來說，這樣要寫成表格限制條件形式，如下：

CREATE TABLE t1 (
  a integer PRIMARY KEY,
  b integer,
  c integer,

FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
);

當然，組合外部鍵的欄位數量，彼此之間必須要相等。

你可以給外部鍵一個名稱，使用語法與限制條件相同。

一個表格可以有許多個外部鍵，這用於表格之間多對多的關係。例如你有一些表格記錄了很多產品和訂單，但現在你要讓每一筆訂單也可以訂購多項產品（這在先前的語法並不允許）。你也許可以試試這個表格宣告：

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    product_no integer REFERENCES products,
    order_id integer REFERENCES orders,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

注意到這裡的主鍵和外部鍵是重覆的。

我們知道外部鍵不允許沒有關連到產品的訂單，但如果企圖移除一個有訂單的產品會如何呢？SQL 有幾個選項讓你直覺進行這項操作：

• 不允許移除被參考到的產品

• 同時也刪去訂單

• 其他？

要描繪這些情況，讓我們建立如上需求的多對多關連的結構：當某人要移除一個有訂單的產品（以 order_items 關連）時，我們不允許執行。而如果某人移除了一筆訂單，訂單內的項目也會同步被移除：

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    product_no integer REFERENCES products ON DELETE RESTRICT,
    order_id integer REFERENCES orders ON DELETE CASCADE,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

引用和同步刪除有兩個常見的作法。用「RESTRICT」防止參考的資料被刪除；「NO ACTION」表示當限制條件被違反時，引用欄位的資料仍會留存，然後回傳錯誤訊息，如果未指定處理方式的話，這會是預設的行為（這兩個語法根本上的不同是「NO ACTION」允許延遲檢查到交易事務的最後，而「RESTRICT」則不會。）；「CASCADE」指的是當參考的資料列被刪除時，引用的資料列也會同步被刪除。刪除時還有兩個其他的選項：SET NULL 和 SET DEFAULT，表示引用的資料會被更新為空值或其預設值。注意到，這並不是說你就可以違反限制條件。舉個例來說，如果使用了 SET DEFAULT，但預設值卻違反了外部鍵的限制，這個操作將會失敗。

類似的於 ON DELETE 的情況是 ON UPDATE，也就是在參考欄位的資料內容被更新時的情況。可以設定的動作關鍵字是相同的。在這個情況的 CASCADE 指的就是更新參考欄位的資料內容時，引用欄位的內容也會同步被更新為相同的內容。

一般來說，引用的資料列不需要滿足外部鍵的定義，如果其任一欄位內容為空值的話。而如果「MATCH FULL」加到宣告的語法之中的話，引用的資料列就必須要全部都是空值才不受外部鍵的限制（也就是部份空值的資料列就不受限制）。如果要避免空值使得外部鍵失效的話，就應該宣告相關欄位為 NOT NULL。

外部鍵所參考的欄位必須要是主鍵或是宣告其唯一性限制，這表示參考欄位會有索引存在，這使得檢查關連的過程會是很有效率的。因為在刪除或更新參考資料表時，需要檢查引用資料表的情況，所以在引用表格的欄位建立索引，也是常見的作法。因為這並不是一定需要，而還有許多的選擇在於如何索引，所以宣告外部鍵時並不會自行以引用欄位組合建立索引。

關於更新資料與刪除資料的細節在第 6 章。也可以在 CREATE TABLE 語法說明中，找到更多外部鍵的說明。

5.4.6. 除外宣告（Exclusion Constraints）

除外宣告要確保的是，如果任意兩個資料列在指定的欄位或表示式被比較時，用於特定的運算子，至少有一個比較會回傳假（false）或空值（null）。語法如下：

CREATE TABLE circles (
    c circle,
    EXCLUDE USING gist (c WITH &&)
);

詳情請參考 CREATE TABLE 中，CONSTRAINT 到 EXCLUDE 的段落。

加入除外宣告時，將會自動建立相對應的索引。

PostgreSQL 實作了資料表的繼承方式，對於資料庫設計人員來說，將會是很好用的功能。（SQL:1999 之後定義了型別繼承的功能，但和這裡所介紹的方向有許多不同。）

我們直接以一個例子作為開始：假設我們嘗試建立「城市（city）」的資料模型。每一個州（state）都會有許多城市（city），但只會有一個首都（capital）。我們想要很快地可以找到某個州的首都。這件事我們需要建立兩個資料表，一個存放首都，而另一個記載非首都的城市。只是，當我們想要取得的是所有城市，不論是否為首都，似乎會有些麻煩？這時候繼承功能就可以幫助我們解決這個問題。我們可以定義一個資料表 capitals，它是由資料表 cities 繼承而來：

CREATE TABLE cities (
    name            text,
    population      float,
    altitude        int     -- in feet
);

CREATE TABLE capitals (
    state           char(2)
) INHERITS (cities);

在這個例子中，資料表 capitals 會繼承父資料表 cities 的所有欄位。只是 capitals 會多一個欄位 state，表示它是哪個州的首都。

在 PostgreSQL 裡，一個資料表可以繼承多個資料表，而一個查詢可以引用該資料表裡的所有資料列或在其所屬的資料表的資料列，後者的行為是預設的。舉個例子，下面的查詢可以列出所有海沷在 500 英呎以上的城市名稱，州的首都也包含在內：

SELECT name, altitude
    FROM cities
    WHERE altitude > 500;

使用 2.1 節中的範例資料，將會回傳：

   name    | altitude
-----------+----------
 Las Vegas |     2174
 Mariposa  |     1953
 Madison   |      845

換句話說，下面的查詢就會查出非首都且海沷超過 500 英呎以上的城市：

SELECT name, altitude
    FROM ONLY cities
    WHERE altitude > 500;

   name    | altitude
-----------+----------
 Las Vegas |     2174
 Mariposa  |     1953

這裡「ONLY」關鍵字指的是查詢只需要包含資料表 cities 就好，而不是任何繼承 cities 的資料表都包含在內。我們先前介紹過的指令：SELECT、UPDATE、和 DELETE，都可以使用 ONLY 關鍵字。

你也可以在資料表名稱後面加上「*」，明確指出繼承的資料表都需要包含在內：

SELECT name, altitude
    FROM cities*
    WHERE altitude > 500;

注意這個「*」並不是必要的，因為這個行為本來就是預設的。這個語法用於相容舊的版本，有些版本的預設行為可能不太一樣。

在某些例子裡，也許你會希望知道哪些資料列來自於哪個資料表。有一個系統欄位稱作 tableoid，每一個資料表都會有，而它可告訴你資料列的來源：

SELECT c.tableoid, c.name, c.altitude
FROM cities c
WHERE c.altitude > 500;

這將會回傳：

 tableoid |   name    | altitude
----------+-----------+----------
   139793 | Las Vegas |     2174
   139793 | Mariposa  |     1953
   139798 | Madison   |      845

（如果你嘗試重覆執行這個例子，你可能會得到不同的 OID 值。）藉由和資料表 pg_class 交叉查詢，你可以看到實際的資料表名稱：

SELECT p.relname, c.name, c.altitude
FROM cities c, pg_class p
WHERE c.altitude > 500 AND c.tableoid = p.oid;

將會回傳：

 relname  |   name    | altitude
----------+-----------+----------
 cities   | Las Vegas |     2174
 cities   | Mariposa  |     1953
 capitals | Madison   |      845

另一個可以得到相同結果的方式是，使用 regclass 別名型別，這個型別會將 OID 轉換成名稱輸出：

SELECT c.tableoid::regclass, c.name, c.altitude
FROM cities c
WHERE c.altitude > 500;

在使用 INSERT 或 COPY 指令時，繼承並不會自動轉存資料。在我們的例子中，下面的 INSERT 指令將會失敗：

INSERT INTO cities (name, population, altitude, state)
VALUES ('Albany', NULL, NULL, 'NY');

我們可能會希望資料以某種方式轉送到資料表 capitals 中，但這不會發生：INSERT 指令永遠只會將資料插入到指定的資料表中。在某些情況下，如果設定了存取規則（第 40 章）的話，那有可能做到類似的效果。然而，在這個例子下是沒有辦法執行的，因為資料表 cities 中並沒有一個欄位稱作 state，所以這個指令將會被拒絕執行，如果沒有其他規則被設定的話。

所有限制條件的檢查，還有非空值的限制，都會自動從父資料表繼承下來，除非特別使用 NO INHERIT 子句來設定拋棄繼承。而其他型態的限制條件（唯一性、主鍵、外部鍵）都不會自動繼承。

一個資料表也可以繼承超過一個資料表，也就是說，它會擁有這些資料表全部的欄位，然後再加上自己所宣告的欄位。如果父資料表有相同名稱的欄位的話，或是父資料表和子資料表有同名的欄位，那麼這些欄位會被合併，它們會被合併為一個欄位。合併的時候，他們的資料型別必須要一致，否則會產生錯誤。被繼承的限制條件和無空值的限制也會用類似的方式合併。舉個例子來說，如果要合併的欄位中，任何一個欄位有 not-null 的設定的話，那麼合併後的欄位就會被設定為 not-null。如果有同名的限制條件要被合併，但他們的內容不相同的話，那麼合併也會失敗。

資料表的繼承一般來說是在子資料表建立時進行的，也就是在 CREATE TABLE 中使用 INHERITES 子句。然而，資料表也可以在 ALTER TABLE 中使用 INHERIT 子句來新增新的父資料表。要進行這個動作，新的子資料表必須已經包含所有父資料表的欄位—相同的欄位名稱及資料型別。還有在 ALTER TABLE 時加入 INHERIT 子句來移除某個欄位的繼承。動態地新增或移除繼承欄位通常是在應用分割表格（table partitioning）時特別好用（請參閱 5.10 節）。

還有一個方便的方式去建立一個相容於之後繼承的資料表，就是在 CREATE TABLE 中使用 LIKE 子句。這個方式會建立一個新的資料表，其欄位和另一個資料表完全相同。如果有任何 CHECK 子句的限制條件的話，就應該在 LIKE 子句中加入 INCLUDING CONSTRAINTS 選項，這樣就會和父資料表完全相容了。

父資料表無法在子資料表仍然存在時被移除。子資料表的欄位和限制條件也不能被移除，如果它們是由其他資料表繼承而來的話。如果你想要移除某個資料表，包含其相關的物件的話，一個簡單的方式就是在移除時加上 CASCADE 選項（請參閱 5.13 節）。

ALTER TABLE 將會讓欄位型態和限制條件的改變，衍生至繼承它的資料表之中。一樣地，移除某個欄位，如果它有被其他資料表繼承的話，那麼就必須要加上 CASCADE 選項才行。ALTER TABLE 會遵循和 CREATE TABLE 一樣的規則，決定重覆的欄位要合併還是拒絕。

指令的繼承權限是依父資料表的權限。舉個例子，當你存取資料表 cities 時，在 cities 上給予 UPDATE 的權限，同時也隱含了賦予 capitals 更新資料的權限。這考量到這些資料也會出現在父資料表，但如果你沒有特別給予 capitals 權限的話，你還是無法直接存取 capitals。類似的情況也會發生在資料列的安全原則（5.7 節），在繼承查詢時，同樣是參考父資料表的安全原則。而子資料表額外的安全原則，只在直接查詢該資料表時有效，同時任何父資料表的安全原則會失效。

外部資料表（5.11 節）也可以是繼承的一部份，父資料表或子資料表，就如同一般的資料表一樣。只是，如果整個繼承結構中，有任何外部資料不支援的操作的話，那麼整個繼承結構就都不支援。

5.9.1. 警告

注意，並非所有的 SQL 指令都可以在繼承結構中執行。一般常用的資料查詢，資料更新，或結構調整（像是 SELECT、UPDATE、DELETE，還有多數 ALTER TABLE 的功能，但不包括 INSERT 或 ALTER TABLE ...... RENAME），基本上預設都是包含子資料表，也支援使用 ONLY 指示字來排除子資料表。如果是資料庫維護或調教的指令，如 REINDEX、VACUUM，一般就只支援特定且實體的資料表，就不會在繼承結構中衍生其他的動作。這些個別指令相關的行為，請參閱 SQL Commands 內的說明。

繼承功能比較嚴格的限制是索引（包含唯一性索引），還有外部鍵的限制條件，都只能用在單一資料表，而不會衍生至他們的子資料表中。對外部鍵來說，無論引用資料表或是參考資料表的情況都一樣。下面是一些例子說明：

• 如果我們宣告 cities.name 具備唯一性或是主鍵，這不會限制到 capitals 中有重覆的項目。而這些重覆的資料列就會出現在 cities 的查詢結果中。事實上，預設的 capitals 就沒有唯一性的限制，所以就可能有多個資料列記載相同的名稱。你可以在 capitals 中也加入唯一性索引，但這也無法避免 capitals 和 cities 中有重覆的項目。

• 同樣地，如果我們指定 cities.name 以外部鍵的方式，參考另一個資料表，而這個外部鍵也不會衍生到 capitals 中。這種情況你就必須在 capitals 中也以 REFERENCES 設定同樣外部鍵的引用。

• 如果有另一個資料表的欄位設定了 REFERENCES cities(name) 就會允許其他的資料表包含城市名稱，但就沒有首都名稱。在這個情況下，沒有好的解決辦法。

這些缺點可能會在後續的版本中被修正，但在此時此刻，當你需要使用繼承功能讓你的應用設計更好用時，你就必須要同時考慮這些限制。

除了透過 GRANT 指令設定 SQL 標準的權限系統之外，資料表也可以有資料列層級的安全原則，控制每個使用者在資料查詢或變更時，所能接觸到的資料列。這個功能就稱作資料列安全原則（Row-Level Security）。預設上，資料表並不會有這些安全原則，所以只要使用者能存取該資料表，就表示他能存取所有資料列的內容。

當資料列安全原則在資料表裡被啓動後（使用 ALTER TABLE ... ENABLE ROW LEVEL SECURITY），所有資料表的操作，就必須符合資料列安全原則的設定。（當然，資料表的擁有者並不受限於資料列安全原則。）如果資料表中未設定任何原則，那麼預設就是拒絕存取，意思就是任何資料列都不能被看見或修改。但如果是整個資料表的操作行為，像是 TRUNCATE 或 REFERENCES，就不會受到影響。

資料列安全原則可以被設定在命令，使用者角色，或兩者兼具。安全原則也可以使用 ALL 的修飾字，或具體指出是 SELECT、INSERT、UPDATE、或 DELETE。多重角色可以共用一個安全原則，一般使用者或承繼的角色都會被同步影響到。

要設定一個安全原則來指出哪些資料列可見或可修改，是以一個回傳值為布林值的表示式來決定的。這個表示式會計算每一個資料列的結果，在使用者進行任何操作之前。（這個規則唯一的例外是 leakproof 函數，用來確保沒有洩漏資訊；查詢最佳化元件會選押在確任資料列安全原則前就先執行它。）在這個表示式沒有回傳 true 的資料列，都是不能被存取的。獨立的表示式可用於提供資料列專屬的控制，判斷其是否可供讀取或修改。安全原則表示式是查詢的一部份，和使用者執行查詢時一起執行，不過，安全原則表示式是可以存取到該使用者看不到的資料。

超級使用者因為擁有 BYPASSRLS 的屬性，所以永遠可以通過安全原則檢查而存取資料表。資料表的擁有者一般來說也是可以通過檢查，但可以使用 ALTER TABLE ... FORCE ROW LEVEL SECURITY 來強制適用安全原則。

開啓或關閉資料列安全原則的權限，只屬於資料表擁有者。

使用 CREATE POLICY 指令來建立安全原則；使用 ALTER POLICY 指令來修改；使用 DROP POLICY 指令來移除原則。要開啓或關閉安全原則的功能，請使用 ALTER TABLE 指令。

每一個安全原則都有一個名稱，而一個資料表可以定義多個安全原則。安全原則是資料表專屬的，而每一個安全原則在所屬資料表內必須有一個唯一的名稱。不同的資料表下的安全原則可以取相同的名稱。

當多個安全原則使用者某個查詢上時，可能會使用 OR 串接（開放安全原則 permissive policies，這是預設的狀態），也可能以 AND 串接（嚴格安全原則 restrictive policies）。這類似角色授權的情況。有關於開放安全原則與嚴格安全原則的細節，稍後再進行說明。

先進行一個簡單的範例，我們建立一個安全原則在資料表 account 上，它只允許 managers 的使用者可以存取資料列，並且只能存取他自己帳號的資料列：

CREATE TABLE accounts (manager text, company text, contact_email text);

ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

CREATE POLICY account_managers ON accounts TO managers
    USING (manager = current_user);

如果沒有指定角色或使用者時，就會以 PUBLIC 替代，也就是所有使用者都適用。要允許所有使用者存取他們自己的資料列的話，就可以簡化指令為：

CREATE POLICY user_policy ON users
    USING (user_name = current_user);

想要定義一個安全原則是有別於可見性權限的話，請使用 WITH CHECK 字句。例如希望讓所有人都可以看到所有資料列，但只能修改自己的資料的話：

CREATE POLICY user_policy ON users
    USING (true)
    WITH CHECK (user_name = current_user);

資料列安全原則也可以透過 ALTER TABLE 指令關閉。不過關閉資料列安全原則，並不會移除任何已定義的原則，只是單純被忽略而已。然後資料表的所有資料列，就只依標準 SQL 的權限系統，決定查詢及修改的權力。

下面是一個較複雜的例子，展示這個功能如何被應用於產品等級的環境裡。資料表 passwd 模擬 Unix 的密碼檔：

-- Simple passwd-file based example
CREATE TABLE passwd (
  user_name             text UNIQUE NOT NULL,
  pwhash                text,
  uid                   int  PRIMARY KEY,
  gid                   int  NOT NULL,
  real_name             text NOT NULL,
  home_phone            text,
  extra_info            text,
  home_dir              text NOT NULL,
  shell                 text NOT NULL
);

CREATE ROLE admin;  -- Administrator
CREATE ROLE bob;    -- Normal user
CREATE ROLE alice;  -- Normal user

-- Populate the table
INSERT INTO passwd VALUES
  ('admin','xxx',0,0,'Admin','111-222-3333',null,'/root','/bin/dash');
INSERT INTO passwd VALUES
  ('bob','xxx',1,1,'Bob','123-456-7890',null,'/home/bob','/bin/zsh');
INSERT INTO passwd VALUES
  ('alice','xxx',2,1,'Alice','098-765-4321',null,'/home/alice','/bin/zsh');

-- Be sure to enable row level security on the table
ALTER TABLE passwd ENABLE ROW LEVEL SECURITY;

-- Create policies
-- Administrator can see all rows and add any rows
CREATE POLICY admin_all ON passwd TO admin USING (true) WITH CHECK (true);
-- Normal users can view all rows
CREATE POLICY all_view ON passwd FOR SELECT USING (true);
-- Normal users can update their own records, but
-- limit which shells a normal user is allowed to set
CREATE POLICY user_mod ON passwd FOR UPDATE
  USING (current_user = user_name)
  WITH CHECK (
    current_user = user_name AND
    shell IN ('/bin/bash','/bin/sh','/bin/dash','/bin/zsh','/bin/tcsh')
  );

-- Allow admin all normal rights
GRANT SELECT, INSERT, UPDATE, DELETE ON passwd TO admin;
-- Users only get select access on public columns
GRANT SELECT
  (user_name, uid, gid, real_name, home_phone, extra_info, home_dir, shell)
  ON passwd TO public;
-- Allow users to update certain columns
GRANT UPDATE
  (pwhash, real_name, home_phone, extra_info, shell)
  ON passwd TO public;

對於任何的安全設定，很重要的是，你必須實際測試來確認系統的行為和你預期的相同。使用上面的例子，下面的測試表現出權限設如預期地運作。

-- admin can view all rows and fields
postgres=> set role admin;
SET
postgres=> table passwd;
 user_name | pwhash | uid | gid | real_name |  home_phone  | extra_info | home_dir    |   shell
-----------+--------+-----+-----+-----------+--------------+------------+-------------+-----------
 admin     | xxx    |   0 |   0 | Admin     | 111-222-3333 |            | /root       | /bin/dash
 bob       | xxx    |   1 |   1 | Bob       | 123-456-7890 |            | /home/bob   | /bin/zsh
 alice     | xxx    |   2 |   1 | Alice     | 098-765-4321 |            | /home/alice | /bin/zsh
(3 rows)

-- Test what Alice is able to do
postgres=> set role alice;
SET
postgres=> table passwd;
ERROR:  permission denied for relation passwd
postgres=> select user_name,real_name,home_phone,extra_info,home_dir,shell from passwd;
 user_name | real_name |  home_phone  | extra_info | home_dir    |   shell
-----------+-----------+--------------+------------+-------------+-----------
 admin     | Admin     | 111-222-3333 |            | /root       | /bin/dash
 bob       | Bob       | 123-456-7890 |            | /home/bob   | /bin/zsh
 alice     | Alice     | 098-765-4321 |            | /home/alice | /bin/zsh
(3 rows)

postgres=> update passwd set user_name = 'joe';
ERROR:  permission denied for relation passwd
-- Alice is allowed to change her own real_name, but no others
postgres=> update passwd set real_name = 'Alice Doe';
UPDATE 1
postgres=> update passwd set real_name = 'John Doe' where user_name = 'admin';
UPDATE 0
postgres=> update passwd set shell = '/bin/xx';
ERROR:  new row violates WITH CHECK OPTION for "passwd"
postgres=> delete from passwd;
ERROR:  permission denied for relation passwd
postgres=> insert into passwd (user_name) values ('xxx');
ERROR:  permission denied for relation passwd
-- Alice can change her own password; RLS silently prevents updating other rows
postgres=> update passwd set pwhash = 'abc';
UPDATE 1

所有的安全原則，目前來說都是開放安全原則，意思是當有多個安全原則被引用時，它們會以 OR 運算串連其結果。開放安全原則用於只允許在計畫內的環境使用的話，它會比和嚴格安全原則（把安全原則用 AND 串連起來判斷）一起使用來得簡單。基於上面的列子，我們建立一個嚴格安全原則，它限制管理者只能透過 unix socket 連線才能存取 passwd 資料表：

CREATE POLICY admin_local_only ON passwd AS RESTRICTIVE TO admin
    USING (pg_catalog.inet_client_addr() IS NULL);

我們接下來就可以看到，管理者透過一般網路連線，是看不到任何資料的，因為嚴格安全原則：

=> SELECT current_user;
 current_user 
--------------
 admin
(1 row)

=> select inet_client_addr();
 inet_client_addr 
------------------
 127.0.0.1
(1 row)

=> SELECT current_user;
 current_user 
--------------
 admin
(1 row)

=> TABLE passwd;
 user_name | pwhash | uid | gid | real_name | home_phone | extra_info | home_dir | shell
-----------+--------+-----+-----+-----------+------------+------------+----------+-------
(0 rows)

=> UPDATE passwd set pwhash = NULL;
UPDATE 0

資料一致性的檢查，像是唯一性、主鍵、以及外部鍵參考，都會略過資料列安全原則，以維持資料的一致性。在發展資料庫結構時應該要特別小心，以資料列安全原則避免透過一致性檢查而產生隱藏通道洩露資訊。

在某些情況，很重要的是要確認安全原則是否被觸發。舉例來說，當進行資料備份流程時，如果安全原則造成某些資料被備份程式忽略了，那可能就會很糟糕。在這種情況下，你可以把 row_security 這個參數設為 off。這並不是避開安全原則，而是在觸發安全原則時，會出現錯誤訊息，使得我們可以發現進而修正原則。

在上面的例子裡，安全原則表示式只引用了目前資料列中的資料。這是最簡單也是最常見的形式，可以的話，最好以這樣的方式來設計安全原則。如果需要參考其他資料列或資料表來做決定的話，那麼可以使用子查詢或函數的方式達成，也就是包含一個 SELECT 的查詢語句在表示式中。要注意到的是，這種方法可能會造成資料庫內交易競爭（race condition）的狀態，不注意的話也可能產生資訊的洩漏。像這樣的例子，試試下面的資料表設計：

-- definition of privilege groups
CREATE TABLE groups (group_id int PRIMARY KEY,
                     group_name text NOT NULL);

INSERT INTO groups VALUES
  (1, 'low'),
  (2, 'medium'),
  (5, 'high');

GRANT ALL ON groups TO alice;  -- alice is the administrator
GRANT SELECT ON groups TO public;

-- definition of users' privilege levels
CREATE TABLE users (user_name text PRIMARY KEY,
                    group_id int NOT NULL REFERENCES groups);

INSERT INTO users VALUES
  ('alice', 5),
  ('bob', 2),
  ('mallory', 2);

GRANT ALL ON users TO alice;
GRANT SELECT ON users TO public;

-- table holding the information to be protected
CREATE TABLE information (info text,
                          group_id int NOT NULL REFERENCES groups);

INSERT INTO information VALUES
  ('barely secret', 1),
  ('slightly secret', 2),
  ('very secret', 5);

ALTER TABLE information ENABLE ROW LEVEL SECURITY;

-- a row should be visible to/updatable by users whose security group_id is
-- greater than or equal to the row's group_id
CREATE POLICY fp_s ON information FOR SELECT
  USING (group_id <= (SELECT group_id FROM users WHERE user_name = current_user));
CREATE POLICY fp_u ON information FOR UPDATE
  USING (group_id <= (SELECT group_id FROM users WHERE user_name = current_user));

-- we rely only on RLS to protect the information table
GRANT ALL ON information TO public;

現在假設 alice 想要變更＂slightly secret＂的資訊，但決定不讓 mallory 看到新的內容，所以她這麼做：

BEGIN;
UPDATE users SET group_id = 1 WHERE user_name = 'mallory';
UPDATE information SET info = 'secret from mallory' WHERE group_id = 2;
COMMIT;

看起來很安全，因為沒有窗口讓 mallory 可以看到＂secret from mallory＂，然而，這裡就存在了交易競爭的情況。如果 mallory 也在同時做了：

SELECT * FROM information WHERE group_id = 2 FOR UPDATE;

因為她的交易是屬於 READ COMMITTED 模式，所以她有可能會看到＂secret from mallory＂。這會剛好發生在，她在 alice 的交易完成前一刻。mallory 的指令會暫時擋下 alice 的提交完成，而因為 FOR UPDATE，她會取得更新後的資訊。所以她並沒有從隱含的使用者執行 SELECT 取得資訊，因為子查詢沒有 FOR UPDATE，使得其他使用者可以從快照裡取得資訊。因為安全原則是以舊的 mallory 權限允許她看見該筆資料。

這個問題有好幾個面向的解決方式。一個簡單的方式就是使用 SELECT ... FOR SHARE 在安全原則的子查詢裡。但這樣就必須要讓使用者擁有 UPDATE 的權限，可能不太合適。（但也可以用另一個安全原則來做更多的限制，又或是把子查詢封裝進另一個安全的函數裡）同時，大量的引用查詢也可能造成效能的問題，特別是更新資料的時候。另一個解決辦法，如果參考的資料表並不是很常更新的話，那麼可以在資料表更新時強制鎖定該資料表，確保沒有其他交易能在同時進行查詢，也就不會洩漏任何資訊。或是等待其他所有交易都完成後，才提交更新變更新的安全方案。

更多詳細，請參閱 CREATE POLICY 和 ALTER TABLE。

PostgreSQL 支援基本的分割資料表。本節描述了為什麼以及如何在資料庫設計中實現分割資料表。

5.11.1. 概念

分割資料表指的是將一個大型資料表以邏輯規則實體拆分為較小的資料庫。分割資料表可以帶來以下好處：

• 在某些情況下，尤其是當資料表中大多數被頻繁存取的資料位於單個分割區或少量的分割區之中時，查詢效能可以顯著地提高。分割區替代了索引的前幾個欄位，從而縮減了索引的大小，並使索引中頻繁使用的部分更有可能都放入記憶體之中。

• 當查詢或更新存取單個分割區的很大一部分時，可以透過對該分割區進行循序掃描而不是使用索引和遍及整個資料表的隨機讀取來提高效能。

• 如果計劃程序將這種需求計劃在分割區的設計中，則可以透過增加或刪除分區來完成批次加入和刪除。使用 ALTER TABLE DETACH PARTITION 或使用 DROP TABLE 刪除單個分割區比批次操作要快得多。這些命令還完全避免了由批次 DELETE 所增加的 VACUUM 成本。

• 很少使用的資料可以遷移到慢一些，但更便宜的儲存媒體上。

通常只有在資料表很大的情況下，這些好處才是值得的。資料表可以從分割區中受益的確切評估點取決於應用程式，儘管經驗法則是資料表的大小超過資料庫伺服器的記憶體大小的時候。

PostgreSQL 內建支援以下形式的分割方式：

Range Partitioning

此資料庫表的分割區以一個欄位為鍵或一組欄位定義的「range」來分配，分配給不同分割區的範圍之間沒有重疊。例如，可以按日期範圍或特定業務對象的標識範圍進行分割。

List Partitioning

透過明確列出哪些鍵值出現應該在哪個分割區中來對資料表進行分割。

Hash Partitioning

透過為每個分割區指定除數和餘數來對資料表進行分割。每個分割區將保留其分割鍵的雜湊值除以指定的除數所產生指定的餘數的資料列。

如果您的應用程式需要使用上面未列出的其他分割區形式，則可以使用替代方法，例如繼承和 UNION ALL 檢視表。此類方法提供了靈活性，但沒有內建宣告分割區的效能優勢。

5.11.2. 宣告分割資料表

你可以在 PostgreSQL 上宣告一個資料表實際上被劃分為多個分割區。被劃分的資料表稱為分割資料表。此宣告包括如上所述的分割區方法，以及要用作分割區主鍵的欄位或表示式的列表。

分割資料表本身是一個「虛擬」資料表，沒有自己的儲存空間。而是儲存屬於分割區，分割區是與分割資料表相關聯的基本資料表。每個分割區都儲存由其分割區範圍定義的資料子集合。插入分割區資料表中的所有的資料都將根據分割主鍵欄位的值被重新導向到相應的其中一個分割區之中。如果某筆資料的分割主鍵不再滿足其原始分割區的分割區範圍，所以 UPDATE 該筆資料將可能導致其遷移至其他分割區。

分割區本身也可以定義為分割資料表，從而形成子分割區。儘管所有分割區都必須與其分割區的父親具有相同的欄位，但是分割區可以擁有自己的索引、限制條件和預設值，與其他分割區的索引、限制條件和預設值不同。有關建立分割區表和分割區的更多詳細說明，請參閱 CREATE TABLE。

不可能將一張常規的資料表轉換成一個分割過的資料表，反之亦然。但是，是有可能新增一個既有的常規或是分割過的資料表，作為一個分割資料表的分割區；或是從一個分割過的資料表刪除一個分割區，將它轉換成獨立的資料表。這樣可以簡化和加速維護的程序。請參閱 ALTER TABLE 學習更多關於 ATTACH PARTITION 和 DETACH PARTITION 次命令.

分割區也可以是外部資料表，儘管這些資料表有一些一般資料表沒有的限制。更多的資訊請參閱 CREATE FOREIGN TABLE.

5.11.2.1. Example

Suppose we are constructing a database for a large ice cream company. The company measures peak temperatures every day as well as ice cream sales in each region. Conceptually, we want a table like:

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
);

We know that most queries will access just the last week's, month's or quarter's data, since the main use of this table will be to prepare online reports for management. To reduce the amount of old data that needs to be stored, we decide to only keep the most recent 3 years worth of data. At the beginning of each month we will remove the oldest month's data. In this situation we can use partitioning to help us meet all of our different requirements for the measurements table.

To use declarative partitioning in this case, use the following steps:

1. Create measurement table as a partitioned table by specifying the PARTITION BY clause, which includes the partitioning method (RANGE in this case) and the list of column(s) to use as the partition key.

   Copy

   CREATE TABLE measurement (
       city_id         int not null,
       logdate         date not null,
       peaktemp        int,
       unitsales       int
   ) PARTITION BY RANGE (logdate);

   You may decide to use multiple columns in the partition key for range partitioning, if desired. Of course, this will often result in a larger number of partitions, each of which is individually smaller. On the other hand, using fewer columns may lead to a coarser-grained partitioning criteria with smaller number of partitions. A query accessing the partitioned table will have to scan fewer partitions if the conditions involve some or all of these columns. For example, consider a table range partitioned using columns lastname and firstname (in that order) as the partition key.

2. Create partitions. Each partition's definition must specify the bounds that correspond to the partitioning method and partition key of the parent. Note that specifying bounds such that the new partition's values will overlap with those in one or more existing partitions will cause an error. Inserting data into the parent table that does not map to one of the existing partitions will cause an error; an appropriate partition must be added manually.

   Partitions thus created are in every way normal PostgreSQL tables (or, possibly, foreign tables). It is possible to specify a tablespace and storage parameters for each partition separately.

   It is not necessary to create table constraints describing partition boundary condition for partitions. Instead, partition constraints are generated implicitly from the partition bound specification whenever there is need to refer to them.

   Copy

   CREATE TABLE measurement_y2006m02 PARTITION OF measurement
       FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');
   
   CREATE TABLE measurement_y2006m03 PARTITION OF measurement
       FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');
   
   ...
   CREATE TABLE measurement_y2007m11 PARTITION OF measurement
       FOR VALUES FROM ('2007-11-01') TO ('2007-12-01');
   
   CREATE TABLE measurement_y2007m12 PARTITION OF measurement
       FOR VALUES FROM ('2007-12-01') TO ('2008-01-01')
       TABLESPACE fasttablespace;
   
   CREATE TABLE measurement_y2008m01 PARTITION OF measurement
       FOR VALUES FROM ('2008-01-01') TO ('2008-02-01')
       WITH (parallel_workers = 4)
       TABLESPACE fasttablespace;

   To implement sub-partitioning, specify the PARTITION BY clause in the commands used to create individual partitions, for example:

   Copy

   CREATE TABLE measurement_y2006m02 PARTITION OF measurement
       FOR VALUES FROM ('2006-02-01') TO ('2006-03-01')
       PARTITION BY RANGE (peaktemp);

   After creating partitions of measurement_y2006m02, any data inserted into measurement that is mapped to measurement_y2006m02 (or data that is directly inserted into measurement_y2006m02, provided it satisfies its partition constraint) will be further redirected to one of its partitions based on the peaktemp column. The partition key specified may overlap with the parent's partition key, although care should be taken when specifying the bounds of a sub-partition such that the set of data it accepts constitutes a subset of what the partition's own bounds allows; the system does not try to check whether that's really the case.

3. Create an index on the key column(s), as well as any other indexes you might want, on the partitioned table. (The key index is not strictly necessary, but in most scenarios it is helpful.) This automatically creates one index on each partition, and any partitions you create or attach later will also contain the index.

   Copy

   CREATE INDEX ON measurement (logdate);

4. Ensure that the enable_partition_pruning configuration parameter is not disabled in postgresql.conf. If it is, queries will not be optimized as desired.

In the above example we would be creating a new partition each month, so it might be wise to write a script that generates the required DDL automatically.

5.11.2.2. Partition Maintenance

Normally the set of partitions established when initially defining the table are not intended to remain static. It is common to want to remove old partitions of data and periodically add new partitions for new data. One of the most important advantages of partitioning is precisely that it allows this otherwise painful task to be executed nearly instantaneously by manipulating the partition structure, rather than physically moving large amounts of data around.

The simplest option for removing old data is to drop the partition that is no longer necessary:

DROP TABLE measurement_y2006m02;

This can very quickly delete millions of records because it doesn't have to individually delete every record. Note however that the above command requires taking an ACCESS EXCLUSIVE lock on the parent table.

Another option that is often preferable is to remove the partition from the partitioned table but retain access to it as a table in its own right:

ALTER TABLE measurement DETACH PARTITION measurement_y2006m02;

This allows further operations to be performed on the data before it is dropped. For example, this is often a useful time to back up the data using COPY, pg_dump, or similar tools. It might also be a useful time to aggregate data into smaller formats, perform other data manipulations, or run reports.

Similarly we can add a new partition to handle new data. We can create an empty partition in the partitioned table just as the original partitions were created above:

CREATE TABLE measurement_y2008m02 PARTITION OF measurement
    FOR VALUES FROM ('2008-02-01') TO ('2008-03-01')
    TABLESPACE fasttablespace;

As an alternative, it is sometimes more convenient to create the new table outside the partition structure, and make it a proper partition later. This allows the data to be loaded, checked, and transformed prior to it appearing in the partitioned table:

CREATE TABLE measurement_y2008m02
  (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS)
  TABLESPACE fasttablespace;

ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02
   CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' );

\copy measurement_y2008m02 from 'measurement_y2008m02'
-- possibly some other data preparation work

ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
    FOR VALUES FROM ('2008-02-01') TO ('2008-03-01' );

Before running the ATTACH PARTITION command, it is recommended to create a CHECK constraint on the table to be attached matching the desired partition constraint. That way, the system will be able to skip the scan to validate the implicit partition constraint. Without the CHECK constraint, the table will be scanned to validate the partition constraint while holding an ACCESS EXCLUSIVE lock on that partition and a SHARE UPDATE EXCLUSIVE lock on the parent table. It may be desired to drop the redundant CHECK constraint after ATTACH PARTITION is finished.

As explained above, it is possible to create indexes on partitioned tables and they are applied automatically to the entire hierarchy. This is very convenient, as not only the existing partitions will become indexed, but also any partitions that are created in the future will. One limitation is that it's not possible to use the CONCURRENTLY qualifier when creating such a partitioned index. To overcome long lock times, it is possible to use CREATE INDEX ON ONLY the partitioned table; such an index is marked invalid, and the partitions do not get the index applied automatically. The indexes on partitions can be created separately using CONCURRENTLY, and later attached to the index on the parent using ALTER INDEX .. ATTACH PARTITION. Once indexes for all partitions are attached to the parent index, the parent index is marked valid automatically. Example:

CREATE INDEX measurement_usls_idx ON ONLY measurement (unitsales);

CREATE INDEX measurement_usls_200602_idx
    ON measurement_y2006m02 (unitsales);
ALTER INDEX measurement_usls_idx
    ATTACH PARTITION measurement_usls_200602_idx;
...

This technique can be used with UNIQUE and PRIMARY KEY constraints too; the indexes are created implicitly when the constraint is created. Example:

ALTER TABLE ONLY measurement ADD UNIQUE (city_id, logdate);

ALTER TABLE measurement_y2006m02 ADD UNIQUE (city_id, logdate);
ALTER INDEX measurement_city_id_logdate_key
    ATTACH PARTITION measurement_y2006m02_city_id_logdate_key;
...

5.11.2.3. Limitations

以下是分割區資料表的限制：

• 無法建立跨所有分割區的限制條件。只能單獨對每個分割區設定。

• 分割區資料表上的唯一性限制條件必須包含所有分割主鍵欄位。存在此限制是因為 PostgreSQL 只能在每個分割區中個別實施唯一性。

• 必要時，必須在單個分割區（而不是分割資料表）上定義 BEFORE ROW 觸發器。

• 不允許在同一分割區中混合臨時和永久關連。因此，如果分割資料表是永久性的，則分割區也必須是永久性的，或者都臨時的。使用臨時關連時，分割資料表的所有成員必須來自同一個資料庫連線。

Individual partitions are linked to their partitioned table using inheritance behind-the-scenes. However, it is not possible to use all of the generic features of inheritance with declaratively partitioned tables or their partitions, as discussed below. Notably, a partition cannot have any parents other than the partitioned table it is a partition of, nor can a table inherit from both a partitioned table and a regular table. That means partitioned tables and their partitions never share an inheritance hierarchy with regular tables.

由於由分割資料表及其分割區組成的分割區層次結構仍然是一個繼承式的架構，所以 tableoid 和所有一般的繼承規則都適用，如同第 5.10 節所述，但有一些例外：

• Partitions cannot have columns that are not present in the parent. It is not possible to specify columns when creating partitions with CREATE TABLE, nor is it possible to add columns to partitions after-the-fact using ALTER TABLE. Tables may be added as a partition with ALTER TABLE ... ATTACH PARTITION only if their columns exactly match the parent.

• Both CHECK and NOT NULL constraints of a partitioned table are always inherited by all its partitions. CHECK constraints that are marked NO INHERIT are not allowed to be created on partitioned tables. You cannot drop a NOT NULL constraint on a partition's column if the same constraint is present in the parent table.

• Using ONLY to add or drop a constraint on only the partitioned table is supported as long as there are no partitions. Once partitions exist, using ONLY will result in an error. Instead, constraints on the partitions themselves can be added and (if they are not present in the parent table) dropped.

• As a partitioned table does not have any data itself, attempts to use TRUNCATE ONLY on a partitioned table will always return an error.

5.11.3. Implementation Using Inheritance

While the built-in declarative partitioning is suitable for most common use cases, there are some circumstances where a more flexible approach may be useful. Partitioning can be implemented using table inheritance, which allows for several features not supported by declarative partitioning, such as:

• For declarative partitioning, partitions must have exactly the same set of columns as the partitioned table, whereas with table inheritance, child tables may have extra columns not present in the parent.

• Table inheritance allows for multiple inheritance.

• Declarative partitioning only supports range, list and hash partitioning, whereas table inheritance allows data to be divided in a manner of the user's choosing. (Note, however, that if constraint exclusion is unable to prune child tables effectively, query performance might be poor.)

• Some operations require a stronger lock when using declarative partitioning than when using table inheritance. For example, adding or removing a partition to or from a partitioned table requires taking an ACCESS EXCLUSIVE lock on the parent table, whereas a SHARE UPDATE EXCLUSIVE lock is enough in the case of regular inheritance.

5.11.3.1. Example

We use the same measurement table we used above. To implement partitioning using inheritance, use the following steps:

1. Create the “master” table, from which all of the “child” tables will inherit. This table will contain no data. Do not define any check constraints on this table, unless you intend them to be applied equally to all child tables. There is no point in defining any indexes or unique constraints on it, either. For our example, the master table is the measurement table as originally defined.

2. Create several “child” tables that each inherit from the master table. Normally, these tables will not add any columns to the set inherited from the master. Just as with declarative partitioning, these tables are in every way normal PostgreSQL tables (or foreign tables).

   Copy

   CREATE TABLE measurement_y2006m02 () INHERITS (measurement);
   CREATE TABLE measurement_y2006m03 () INHERITS (measurement);
   ...
   CREATE TABLE measurement_y2007m11 () INHERITS (measurement);
   CREATE TABLE measurement_y2007m12 () INHERITS (measurement);
   CREATE TABLE measurement_y2008m01 () INHERITS (measurement);

3. Add non-overlapping table constraints to the child tables to define the allowed key values in each.

   Typical examples would be:

   Copy

   CHECK ( x = 1 )
   CHECK ( county IN ( 'Oxfordshire', 'Buckinghamshire', 'Warwickshire' ))
   CHECK ( outletID >= 100 AND outletID < 200 )

   Ensure that the constraints guarantee that there is no overlap between the key values permitted in different child tables. A common mistake is to set up range constraints like:

   Copy

   CHECK ( outletID BETWEEN 100 AND 200 )
   CHECK ( outletID BETWEEN 200 AND 300 )

   This is wrong since it is not clear which child table the key value 200 belongs in.

   It would be better to instead create child tables as follows:

   Copy

   CREATE TABLE measurement_y2006m02 (
       CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
   ) INHERITS (measurement);
   
   CREATE TABLE measurement_y2006m03 (
       CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' )
   ) INHERITS (measurement);
   
   ...
   CREATE TABLE measurement_y2007m11 (
       CHECK ( logdate >= DATE '2007-11-01' AND logdate < DATE '2007-12-01' )
   ) INHERITS (measurement);
   
   CREATE TABLE measurement_y2007m12 (
       CHECK ( logdate >= DATE '2007-12-01' AND logdate < DATE '2008-01-01' )
   ) INHERITS (measurement);
   
   CREATE TABLE measurement_y2008m01 (
       CHECK ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )
   ) INHERITS (measurement);

4. For each child table, create an index on the key column(s), as well as any other indexes you might want.

   Copy

   CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate);
   CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate);
   CREATE INDEX measurement_y2007m11_logdate ON measurement_y2007m11 (logdate);
   CREATE INDEX measurement_y2007m12_logdate ON measurement_y2007m12 (logdate);
   CREATE INDEX measurement_y2008m01_logdate ON measurement_y2008m01 (logdate);

5. We want our application to be able to say INSERT INTO measurement ... and have the data be redirected into the appropriate child table. We can arrange that by attaching a suitable trigger function to the master table. If data will be added only to the latest child, we can use a very simple trigger function:

   Copy

   CREATE OR REPLACE FUNCTION measurement_insert_trigger()
   RETURNS TRIGGER AS $$
   BEGIN
       INSERT INTO measurement_y2008m01 VALUES (NEW.*);
       RETURN NULL;
   END;
   $$
   LANGUAGE plpgsql;

   After creating the function, we create a trigger which calls the trigger function:

   Copy

   CREATE TRIGGER insert_measurement_trigger
       BEFORE INSERT ON measurement
       FOR EACH ROW EXECUTE FUNCTION measurement_insert_trigger();

   We must redefine the trigger function each month so that it always points to the current child table. The trigger definition does not need to be updated, however.

   We might want to insert data and have the server automatically locate the child table into which the row should be added. We could do this with a more complex trigger function, for example:

   Copy

   CREATE OR REPLACE FUNCTION measurement_insert_trigger()
   RETURNS TRIGGER AS $$
   BEGIN
       IF ( NEW.logdate >= DATE '2006-02-01' AND
            NEW.logdate < DATE '2006-03-01' ) THEN
           INSERT INTO measurement_y2006m02 VALUES (NEW.*);
       ELSIF ( NEW.logdate >= DATE '2006-03-01' AND
               NEW.logdate < DATE '2006-04-01' ) THEN
           INSERT INTO measurement_y2006m03 VALUES (NEW.*);
       ...
       ELSIF ( NEW.logdate >= DATE '2008-01-01' AND
               NEW.logdate < DATE '2008-02-01' ) THEN
           INSERT INTO measurement_y2008m01 VALUES (NEW.*);
       ELSE
           RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';
       END IF;
       RETURN NULL;
   END;
   $$
   LANGUAGE plpgsql;

   The trigger definition is the same as before. Note that each IF test must exactly match the CHECK constraint for its child table.

   While this function is more complex than the single-month case, it doesn't need to be updated as often, since branches can be added in advance of being needed.

   Note

   In practice, it might be best to check the newest child first, if most inserts go into that child. For simplicity, we have shown the trigger's tests in the same order as in other parts of this example.

   A different approach to redirecting inserts into the appropriate child table is to set up rules, instead of a trigger, on the master table. For example:

   Copy

   CREATE RULE measurement_insert_y2006m02 AS
   ON INSERT TO measurement WHERE
       ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
   DO INSTEAD
       INSERT INTO measurement_y2006m02 VALUES (NEW.*);
   ...
   CREATE RULE measurement_insert_y2008m01 AS
   ON INSERT TO measurement WHERE
       ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )
   DO INSTEAD
       INSERT INTO measurement_y2008m01 VALUES (NEW.*);

   A rule has significantly more overhead than a trigger, but the overhead is paid once per query rather than once per row, so this method might be advantageous for bulk-insert situations. In most cases, however, the trigger method will offer better performance.

   Be aware that COPY ignores rules. If you want to use COPY to insert data, you'll need to copy into the correct child table rather than directly into the master. COPY does fire triggers, so you can use it normally if you use the trigger approach.

   Another disadvantage of the rule approach is that there is no simple way to force an error if the set of rules doesn't cover the insertion date; the data will silently go into the master table instead.

6. Ensure that the constraint_exclusion configuration parameter is not disabled in postgresql.conf; otherwise child tables may be accessed unnecessarily.

As we can see, a complex table hierarchy could require a substantial amount of DDL. In the above example we would be creating a new child table each month, so it might be wise to write a script that generates the required DDL automatically.

5.11.3.2. Maintenance For Inheritance Partitioning

To remove old data quickly, simply drop the child table that is no longer necessary:

DROP TABLE measurement_y2006m02;

To remove the child table from the inheritance hierarchy table but retain access to it as a table in its own right:

ALTER TABLE measurement_y2006m02 NO INHERIT measurement;

To add a new child table to handle new data, create an empty child table just as the original children were created above:

CREATE TABLE measurement_y2008m02 (
    CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' )
) INHERITS (measurement);

Alternatively, one may want to create and populate the new child table before adding it to the table hierarchy. This could allow data to be loaded, checked, and transformed before being made visible to queries on the parent table.

CREATE TABLE measurement_y2008m02
  (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02
   CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' );
\copy measurement_y2008m02 from 'measurement_y2008m02'
-- possibly some other data preparation work
ALTER TABLE measurement_y2008m02 INHERIT measurement;

5.11.3.3. Caveats

The following caveats apply to partitioning implemented using inheritance:

• There is no automatic way to verify that all of the CHECK constraints are mutually exclusive. It is safer to create code that generates child tables and creates and/or modifies associated objects than to write each by hand.

• Indexes and foreign key constraints apply to single tables and not to their inheritance children, hence they have some caveats to be aware of.

• The schemes shown here assume that the values of a row's key column(s) never change, or at least do not change enough to require it to move to another partition. An UPDATE that attempts to do that will fail because of the CHECK constraints. If you need to handle such cases, you can put suitable update triggers on the child tables, but it makes management of the structure much more complicated.

• If you are using manual VACUUM or ANALYZE commands, don't forget that you need to run them on each child table individually. A command like:

  Copy

  ANALYZE measurement;

  will only process the master table.

• INSERT statements with ON CONFLICT clauses are unlikely to work as expected, as the ON CONFLICT action is only taken in case of unique violations on the specified target relation, not its child relations.

• Triggers or rules will be needed to route rows to the desired child table, unless the application is explicitly aware of the partitioning scheme. Triggers may be complicated to write, and will be much slower than the tuple routing performed internally by declarative partitioning.

5.11.4. Partition Pruning

Partition pruning (分割區修剪)是一種查詢最佳化技術，可提高分割資料表的效能。 舉個例子：

SET enable_partition_pruning = on;                 -- the default
SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';

如果不進行分割區修剪，則上面的查詢將掃描 measurement 資料表的每個分割區。啟用分割區修剪後，計劃程序將檢查每個分割區的定義並證明不需要掃描該分割區，因為該分割區不會包含滿足查詢 WHERE 子句的資料。當計劃程序可以證明這一點時，它將從查詢計劃中排除（修剪）分割區。

透過使用 EXPLAIN 指令和 enable_partition_pruning 組態參數，可以顯示已修剪分割區的計劃與未修剪分割區的計劃之間差異。對於這種類型的資料表設定，典型未最佳化的計劃是：

SET enable_partition_pruning = off;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Aggregate  (cost=188.76..188.77 rows=1 width=8)
   ->  Append  (cost=0.00..181.05 rows=3085 width=0)
         ->  Seq Scan on measurement_y2006m02  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2006m03  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
...
         ->  Seq Scan on measurement_y2007m11  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2007m12  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2008m01  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)

有一部份的分割區可能使用索引掃描而不是全資料表的循序掃描，但是這裡的要點是根本不需要掃描較舊的分區來回應此查詢。啟用 partition pruning 之後，我們將獲得更為簡單的查詢計劃，該計劃能夠提供相同的回應：

SET enable_partition_pruning = on;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Aggregate  (cost=37.75..37.76 rows=1 width=8)
   ->  Seq Scan on measurement_y2008m01  (cost=0.00..33.12 rows=617 width=0)
         Filter: (logdate >= '2008-01-01'::date)

請注意，partition pruning 僅由分割主鍵隱含定義的內容而來，而不會參考索引。因此，不需要在相關欄位上定義索引。是否需要為該分割區建立索引取決於您是否希望掃描分割區的查詢會掃描大部分分割區還是僅掃描一小部分。在後者情況下，索引將有所幫助，但對於前者則無濟於事。

Partition pruning can be performed not only during the planning of a given query, but also during its execution. This is useful as it can allow more partitions to be pruned when clauses contain expressions whose values are not known at query planning time, for example, parameters defined in a PREPARE statement, using a value obtained from a subquery, or using a parameterized value on the inner side of a nested loop join. Partition pruning during execution can be performed at any of the following times:

• During initialization of the query plan. Partition pruning can be performed here for parameter values which are known during the initialization phase of execution. Partitions which are pruned during this stage will not show up in the query's EXPLAIN or EXPLAIN ANALYZE. It is possible to determine the number of partitions which were removed during this phase by observing the “Subplans Removed” property in the EXPLAIN output.

• During actual execution of the query plan. Partition pruning may also be performed here to remove partitions using values which are only known during actual query execution. This includes values from subqueries and values from execution-time parameters such as those from parameterized nested loop joins. Since the value of these parameters may change many times during the execution of the query, partition pruning is performed whenever one of the execution parameters being used by partition pruning changes. Determining if partitions were pruned during this phase requires careful inspection of the loops property in the EXPLAIN ANALYZE output. Subplans corresponding to different partitions may have different values for it depending on how many times each of them was pruned during execution. Some may be shown as (never executed) if they were pruned every time.

可以使用 enable_partition_pruning 設定來停用 partition pruning。

5.11.5. Partitioning and Constraint Exclusion

Constraint exclusion is a query optimization technique similar to partition pruning. While it is primarily used for partitioning implemented using the legacy inheritance method, it can be used for other purposes, including with declarative partitioning.

Constraint exclusion works in a very similar way to partition pruning, except that it uses each table's CHECK constraints — which gives it its name — whereas partition pruning uses the table's partition bounds, which exist only in the case of declarative partitioning. Another difference is that constraint exclusion is only applied at plan time; there is no attempt to remove partitions at execution time.

The fact that constraint exclusion uses CHECK constraints, which makes it slow compared to partition pruning, can sometimes be used as an advantage: because constraints can be defined even on declaratively-partitioned tables, in addition to their internal partition bounds, constraint exclusion may be able to elide additional partitions from the query plan.

The default (and recommended) setting of constraint_exclusion is neither on nor off, but an intermediate setting called partition, which causes the technique to be applied only to queries that are likely to be working on inheritance partitioned tables. The on setting causes the planner to examine CHECK constraints in all queries, even simple ones that are unlikely to benefit.

The following caveats apply to constraint exclusion:

• Constraint exclusion is only applied during query planning, unlike partition pruning, which can also be applied during query execution.

• Constraint exclusion only works when the query's WHERE clause contains constants (or externally supplied parameters). For example, a comparison against a non-immutable function such as CURRENT_TIMESTAMP cannot be optimized, since the planner cannot know which child table the function's value might fall into at run time.

• Keep the partitioning constraints simple, else the planner may not be able to prove that child tables might not need to be visited. Use simple equality conditions for list partitioning, or simple range tests for range partitioning, as illustrated in the preceding examples. A good rule of thumb is that partitioning constraints should contain only comparisons of the partitioning column(s) to constants using B-tree-indexable operators, because only B-tree-indexable column(s) are allowed in the partition key.

• All constraints on all children of the parent table are examined during constraint exclusion, so large numbers of children are likely to increase query planning time considerably. So the legacy inheritance based partitioning will work well with up to perhaps a hundred child tables; don't try to use many thousands of children.

5.11.6. Declarative Partitioning Best Practices

The choice of how to partition a table should be made carefully as the performance of query planning and execution can be negatively affected by poor design.

One of the most critical design decisions will be the column or columns by which you partition your data. Often the best choice will be to partition by the column or set of columns which most commonly appear in WHERE clauses of queries being executed on the partitioned table. WHERE clause items that match and are compatible with the partition key can be used to prune unneeded partitions. However, you may be forced into making other decisions by requirements for the PRIMARY KEY or a UNIQUE constraint. Removal of unwanted data is also a factor to consider when planning your partitioning strategy. An entire partition can be detached fairly quickly, so it may be beneficial to design the partition strategy in such a way that all data to be removed at once is located in a single partition.

Choosing the target number of partitions that the table should be divided into is also a critical decision to make. Not having enough partitions may mean that indexes remain too large and that data locality remains poor which could result in low cache hit ratios. However, dividing the table into too many partitions can also cause issues. Too many partitions can mean longer query planning times and higher memory consumption during both query planning and execution. When choosing how to partition your table, it's also important to consider what changes may occur in the future. For example, if you choose to have one partition per customer and you currently have a small number of large customers, consider the implications if in several years you instead find yourself with a large number of small customers. In this case, it may be better to choose to partition by HASH and choose a reasonable number of partitions rather than trying to partition by LIST and hoping that the number of customers does not increase beyond what it is practical to partition the data by.

Sub-partitioning can be useful to further divide partitions that are expected to become larger than other partitions, although excessive sub-partitioning can easily lead to large numbers of partitions and can cause the same problems mentioned in the preceding paragraph.

It is also important to consider the overhead of partitioning during query planning and execution. The query planner is generally able to handle partition hierarchies with up to a few thousand partitions fairly well, provided that typical queries allow the query planner to prune all but a small number of partitions. Planning times become longer and memory consumption becomes higher when more partitions remain after the planner performs partition pruning. This is particularly true for the UPDATE and DELETE commands. Another reason to be concerned about having a large number of partitions is that the server's memory consumption may grow significantly over a period of time, especially if many sessions touch large numbers of partitions. That's because each partition requires its metadata to be loaded into the local memory of each session that touches it.

With data warehouse type workloads, it can make sense to use a larger number of partitions than with an OLTP type workload. Generally, in data warehouses, query planning time is less of a concern as the majority of processing time is spent during query execution. With either of these two types of workload, it is important to make the right decisions early, as re-partitioning large quantities of data can be painfully slow. Simulations of the intended workload are often beneficial for optimizing the partitioning strategy. Never assume that more partitions are better than fewer partitions and vice-versa.\

前一章討論了如何建立資料表和其他結構來保存資料。現在是把資料表填滿的時候了。本章介紹如何新增、更新和刪除資料表的資料。 下一章將會完整說明如何從資料庫中取回你遺落在裡面的資料。

如前一節所述，SELECT 指令中的資料示表表示式透過各種可能地組合資料表、view、消除資料列、分組等來建構中介的虛擬資料表。這個資料表最終會被傳遞給資料列表的處理。資料列表確認中介資料表的哪些欄位是實際上要輸出的。

7.3.1. 資料列表項目

最簡單的選擇列表是*，它表示資料表表示式產生的所有欄位。否則，資料列表是逗號分隔的參數表示式列表（如中所定義的）。例如，它可能是欄位名稱的列表：

欄位名稱 a、b 和 c 是 FROM 子句中資料表的欄位的實際名稱，或者是由中所賦予它們的別名。資料列表中可用的命名空間與 WHERE 子句中的命名空間相同，除非是使用分組查詢，在這種情況下，它與 HAVING 子句中的相同。

如果多個資料表具有相同名稱的欄位，則還必須加上資料表的名稱，如下所示：

處理多個資料表時，查詢特定資料表的所有欄位也是可以的：

有關 table_name.* 表示法的更多信息，請參閱第 8.16.5 節。

如果在資料列表中使用任意值表示式，則概念上是它將新的虛擬欄位加到回傳的資料表中。參數表示式對每個結果資料列計算一次，將該資料列的值替換為任何欄位引用。但是資料列表中的表示式不必引用 FROM 子句的資料表表示式中的任何欄位；例如，它們可以是常數算術表示式。

7.3.2. 欄位命名標籤

資料列表中的項目可以被分配用於後續處理的名稱，例如在 ORDER BY 子句中使用或由用戶端應用程序顯示。 例如：

如果沒有使用 AS 指定輸出欄位的名稱，系統將分配一個預設的欄位名稱。對於簡單欄位的引用，就是引用欄位的名稱。對於函數呼叫，就是函數的名稱。對於複雜的表示式，系統將會產成一個通用的名稱。

AS 關鍵字是選用的，但前提是新的欄位名稱不為任何PostgreSQL 關鍵字（請參閱）。為避免與關鍵字意外撞名，你可以對欄位名稱使用雙引號。例如，VALUE 是一個關鍵字，所以就不能這樣使用：

但這樣就可以了：

為了防止未來可能增加的關鍵字，建議你習慣使用 AS 或總是在欄位名稱使用雙引號。

注意

這裡輸出欄位的命名與 FROM 子句中的命名不同（參閱第 7.2.1.2 節）。可以重新命名相同的欄位兩次，但在資料列表中分配的名稱是將要回傳的名稱。

7.3.3. DISTINCT

在處理了資料列表之後，結果資料表可以選擇性地消除重複的資料列。 DISTINCT 關鍵字在 SELECT 之後直接寫入以指定這個動作：

（如果不是 DISTINCT，而是關鍵字 ALL，可用於指定保留所有資料列的預設行為。）

顯然，如果至少有一個欄位值不同，則兩個資料列就會被認為是不同的。 在這個比較中，空值（null）被認為是相等的。

或者，使用表示式可以指定資料列如何被認為是不同的：

這裡表示式是一個任意的運算表示式，對所有資料列進行求值運算。所有表示式相等的一組資料列被認為是重複的，並且只有該組的第一個資料列會被保留在輸出中。請注意，集合中的「第一行」是不可預知的，除非查詢按足夠的欄位進行排序，以保證進到 DISTINCT 過濾器的資料列是唯一排序。（在 ORDER BY 排序後才進行 DISTINCT ON 處理。）

DISTINCT ON 子句不是SQL標準的一部分，有時被認為是不好的樣式，因為其結果有潛在的不確定性。透過在 FROM 中智慧地使用 GROUP BY 和子查詢，可以避免這種結構，但這卻往往是最方便的選擇。

將已經在資料庫中的資料做修改被稱為更新。您可以單獨更新某個資料列，或資料表中的所有資料列，或是部份資料列。每個欄位可以單獨更新，而不影響其他欄位。

要更新現有的資料列，請使用 指令。這需要三種資訊：

1. 要更新的資料表和欄位的名稱

2. 資料欄位新的內容

3. 哪些資料列要更新

回想一下，SQL 通常不提供資料列的唯一識別資訊。因此，直接指定要更新哪一行通常是不行的，而是指定該資料列必須符合哪些條件才能更新。只有你在資料表中有一個主鍵（決定於是否你有宣告過）之後，才能通過選擇與主鍵相匹配的條件來可靠地解決單個資料列的問題。圖形化的資料庫管理工具依賴這個方式才能允許你單獨更新指定的資料列。

例如，這個指令會將價格為 5 的所有產品更新為 10：

這結果可能是零個，一個或多個資料列被更新。嘗試更新卻沒有匹配到任何資料列，並不是一種錯誤。

我們來詳細看看這個命令。首先是關鍵字 UPDATE，然後是資料表的名稱。像往常一樣，資料表的名稱可以使用加上 schema 的完整路徑名稱，否則就會在搜尋路徑中尋找。接下來的關鍵字是 SET，後面接著欄位名稱，等號和新的欄位內容。新的欄位內容可以是任何的運算表示式，而不僅僅是一個常數。例如，如果要將所有產品的價格提高10％，則可以使用：

如你所見，欄位的表示式可以引用資料列中現有的內容。我們還遺漏了 WHERE 子句。如果省略的話，則意味著資料表中的所有資料列都會被更新。如果存在的話，則只有更新符合 WHERE 條件的那些資料列。請注意，SET 子句中的等號是一個賦值運算，而 WHERE 子句中的等號是比較運算，但這不會造成任何誤解。當然，WHERE 條件不一定是等號運算。 還有許多其他的運算子可以使用（詳見第 9 章）。但是表示式需要能產生為布林運算的結果。

您可以在使用 UPDATE 指令時，以 SET 子句中列出多個欄位賦值來更新多個欄位內容。例如：

一個 資料表表示式 計算出一個資料表。資料表表示式包含了一個可以選擇在後方跟隨WHERE、GROUP BY和HAVING子句的FROM子句。普遍的資料表表示式簡單地在磁碟上引用一個資料表，, 即聲稱的基底資料表（base table）, 但更複雜的表示式可被用於以多種形式修改或組合基底資料表。

在資料表表示式中選擇性的WHERE、GROUP BY和HAVING子句指定一個逐次變換執行在FROM子句衍生的資料表上的管道。所有的這些轉換都會產生一個虛擬資料表，該資料表提供了被傳遞到選擇串列的資料列，以計算查詢的輸出資料列。

7.2.1. FROM子句

The 從逗號分隔資料表參照串列中給出的一個或多個其他的資料表衍生一個資料表。

一個資料表參照能是一個表格名稱（也許綱要限定的），或一個衍生出的資料表，例如子查詢，JOIN建構或這些的複雜組合。如果多個資料表參照被列在FROM子句中， 這些資料表參照則表將被交叉聯接（cross-joined，即形成其資料列的笛卡爾積；請參見下文。）FROM串列的結果是一個中間的虛擬表，該表可以受到WHERE、GROUP BY和HAVING子句的轉換，並且最終是整個資料表表示式的結果。

當一個資料表參照命名一個表格繼承層次結構的父級資料表，資料表參照不只是產生該表格的列，還會產生其所有後代表格的列，除非關鍵字ONLY在表格名稱之前。然而，該參照僅產生出現在已命名資料表中的欄位—子資料表中添加的任何欄位都將被忽略。

可以在表格名稱之後寫入*來明確指定包含後代表格，而不是在表格名稱之前寫入ONLY。因為搜索後代表格現在始終是默認行為，沒有真正的理由再使用此語法。但是，支持它是為了與舊版本的兼容性。

7.2.1.1. 聯接的資料表

聯接的資料表（joined table）是一個根據特定聯接型別的規則從兩個（真實的或被衍生的）其他資料表衍生的資料表。可以使用 Inner、outer、及cross-join 。聯接資料表的一般語法是

所有型別的聯接可以鏈結或嵌套在一起： T1 and T2 中的一個或兩個都可以被聯接資料表。可以在JOIN子句周圍使用括號來控制聯接順序。在沒有括號的情況下，JOIN子句從左到右嵌套。

聯接型別

Cross join

對於從 T1 and T2 的列的每種可能的組合(即笛卡爾積), 聯接的資料表將包含一個由 T1 所有欄其次是 T2 所有欄組成的列。如果資料表分別有 N 列及 M 列， 聯接表將具有 N * M 列。

FROM T1CROSS JOIN T2 相當於 FROM T1 INNER JOIN T2 ON TRUE（見下文。）它也等同於 FROM T1, T2。

Qualified joins

單詞 INNER 及 OUTER在所有形式中都是可選的。INNER 是默認值； LEFT、RIGHT及 FULL 表示外部聯接。

在 ON or USING子句中指定 join condition ，或由單詞NATURAL隱式指定。聯接條件決定兩個來源資料表中的哪些列被視為“匹配”，如下面詳細的說明。

限定聯接（qualified joins）的可能型別為：

INNER JOIN

對於T1的每一列 R1 ，聯接表有一列在T2中的每一列中滿足R1的聯接條件。

LEFT OUTER JOIN

首先，執行內部聯接。然後，對於T1中每一列與T2中任何列不滿足聯接條件，聯接列在T2的欄中添加空值。因此，對於T1中的每一列聯接表始終至少具有一列。

RIGHT OUTER JOIN

首先，執行內部聯接。 然後，對於T2中每一列與T1中任何列不滿足聯接條件，聯接列在T1的欄中添加空值。這是左聯接的反面：對於T2中的每一列結果表將始終有一列。

FULL OUTER JOIN

首先，執行內部聯接。然後，對於T1中每一列與T2中任何列不滿足聯接條件，聯接列在T2的欄中添加空值。另外，對於T2中每一列與T1中任何列不滿足聯接條件，聯接列在T1的欄中添加空值。

ON子句是最通用種類的聯接條件：它採用與WHERE子句中使用的種類相同的Boolean值表示式。如果 ON表示式評估為真值，來自 T1 和 T2 的一對資料列匹配。

USING 子句是一種簡寫形式，可讓您在特定的情況充分利用，即在聯接兩端使用相同的名稱聯接欄位。它使用逗號分隔的共享欄位名稱串列並形成一個包括每個條件相等性比較的聯接條件。例如，將 T1 和 T2 與 USING (a, b) 進行聯接會產生聯接條件ON T1.a =T2.a ANDT1.b =T2.b。

此外，JOIN USING的輸出抑制多餘的欄：無需打印兩個匹配的欄，因為它們必須具有相等的值。儘管JOIN ON會產生 T1 的所有欄其次是 T2 的所有欄，JOIN USING為每個列出的欄配對（按照列出的順序）產生一個輸出欄，其次是 T1 的所有剩餘欄，其次是 T2 的所有剩餘欄。

最後，NATURAL是USING的簡寫形式：它形成一個由出現在兩個輸入資料表中的所有欄位名稱組成的USING串列。 與USING一樣，這些欄在輸出表中僅出現一次。如果沒有共用的欄位名稱，NATURAL JOIN 的行為類似於JOIN ... ON TRUE，產生外積聯接（cross-product join。

綜合以上所述，假設我們有資料表t1:

和資料表t2:

然後對於各種聯接我們得到以下結果：

以ON指定的聯接條件還可以包含與聯接不直接相關的條件。對於某些查詢這可以證明是有用的但需要小心地深思熟慮。例如：

請注意，將限制放置在WHERE子句中會產生不同的結果：

這是因為限制放在 ON子句會在聯接之前被處理，而限制放在 WHERE子句會在聯接之後被處理。這與內部聯接無關緊要，但對於外部聯接則很重要。

7.2.1.2. 資料表和欄位別名

可以為資料表和復雜資料表參照給定一個臨時名稱來用在其餘查詢中參照衍生的資料表。這稱為 資料表別名（table alias） 。

要創建資料表別名，請編寫

或者是

關鍵字AS是選擇性的。 alias 可以是任何標識符。

資料表別名的典型應用是將短標識符分配給長資料表名稱，以保持連接子句的可讀性。例如：

以當前查詢而言，別名成為表參照的新名稱 —不允許在查詢其他位置中使用原始名稱引用該表。因此，這是無效的：

資料表別名主要是為了表示法的方便，但是在將資料表聯接到自身時必須使用它們，例如：

括號被用於解決歧義。在以下範例中，第一條語句將別名b分配給my_table的第二個實例，但是第二條語句將別名分配給聯接結果：

資料表別名的另一種形式為資料表欄位以及資料表本身賦予臨時名稱：

如果指定的欄位別名少於實際表中包含的欄位，則不會重命名剩餘的欄位。此語法對於自聯接或子查詢特別有用。

當別名被應用到JOIN子句的輸出時，別名將原始名稱隱藏在JOIN中。例如：

是有效的SQL，但是：

是無效的；資料表別名a在別名c之外並不可見。

7.2.1.3. 子查詢

這個例子相當於FROM table1 AS alias_name。當子查詢涉及分組或彙總時會出現更有趣的無法簡化為普通聯接的情況。

子查詢也可以是VALUES串列：

7.2.1.4. 資料表函數

資料表函數是產生一組資料列的函數，這些列由基本資料型別（標量（scalar）型別）或複合數資料型別（資料表列）組成。在查詢的 FROM 子句中，它們像資料表、檢視表或子查詢一樣使用。資料表函數返回的欄位以資料表欄位、檢視表或子查詢相同的方式可以包含在SELECT、JOIN或WHERE子句中。

資料表函數也可以使用ROWS FROM語法進行組合，以並行欄位返回結果；在這種情況下結果列的數量是最大的函數結果，較小的結果將填充空值來匹配。

如果WITH ORDINALITY子句被指定，一個額外的bigint型別欄位将被添加到函數結果欄位。這個欄位從1開始為函數結果集合的列作編號（這是SQL標準語法UNNEST ... WITH ORDINALITY的概括。）在默認情況下，序數欄位欄位被稱為ordinality，但可以使用AS子句分配不同的欄位名稱給它。

如果沒有指定 table_alias，該函數名稱被用作資料表名稱； 在ROWS FROM建構的情況中使用第一個函數的名稱。

如果沒有提供欄位別名，則對於返回一個基礎資料型別的函數，該欄位名稱也與函數名稱相同。對於返回一個複合資料型別的函數，該結果欄位取得該型別個別屬性的名稱。

舉一些範例：

在一些情況中他對定義能根據它們的調用方式返回不同欄位集合的資料表函數很有用。為了要支持這情況，資料表函數可以被宣告為返回偽型別 record。在查詢中使用此種函數時，在查詢本身中必須指定預期的資料列結構，以便讓系統知道如何解析和規劃查詢。這種語法看起來像是：

沒有使用ROWS FROM()語法時，column_definition 串列替換原本能被附加到FROM項目的欄位別名串列；在欄位定義中的名稱充當欄位別名。當使用ROWS FROM()語法時，column_definition 串列能被分別附加到每個成員函數；或者如果只有一個成員函數且沒有WITH ORDINALITY子句，能編寫_column_definition_ 串列來代替ROWS FROM()之後的欄位別名串列。

考慮以下範例:

7.2.1.5. LATERAL子查詢

出現在FROM中的子查詢的前面可以有關鍵字LATERAL。這允許它們參照前面FROM項目提供的欄位。（沒有LATERAL的話，每一個子查詢被個別評估所以不能交叉參照任何其他FROM項目。）

出現在FROM中的資料表函數的前面也能有關鍵字LATERAL，但對於函數來說該關鍵字是選擇性的；在任何情況下該函數的參數能包含前面FROM項目提供的欄位參照。

LATERAL項目能出現在FROM串列的頂層，或在JOIN樹之中。在後面的情況下在JOIN右邊的LATERAL也能引用在JOIN左邊的任何項目。

當FROM項目包含LATERAL交叉參照，評估過程如下： 對於該FROM項目每一個提供交叉參照後欄位的列，或是多個FROM項目之提供欄位的列集合，將使用該欄位的列或列集合值來評估LATERAL項目。結果資料列照常與運算出它們的資料列聯接。對於欄位來源表的每一列或列集合重複此操作。

LATERAL的一個簡單範例是：

這不是特別有用，因為它與完全常規的結果完全相同

LATERAL主要有用的時機是在運算資料列聯接而需要交叉參照後欄位的時候。典型的應用是提供一個參數值給會返回集合的函數。舉例來說，假如vertices(polygon)返回多邊形的頂點集合，我們可以經由以下方式識別存儲在表中多邊形的近似頂點：

這個查詢也可以寫成

或者以其他幾種等效公式表示。（如前所述，關鍵字LATERAL在此範例中是不必要的，但為了清楚起見而使用它。）

即使LATERAL子查詢沒有產生資料列，通常特別便利將LEFT JOIN添加到LATERAL子查詢，使得來源資料列將出現在結果中。舉例來說，如果get_product_names()返回製造商生產的產品名稱，但是我們表中的某些製造商目前未生產任何產品，我們可以像這樣找出：

7.2.2. WHERE子句

在完成FROM子句的處理之後，針對搜尋條件檢查衍生虛擬表的每一列。如果條件的結果為true，則資料列保留在輸出表中，否則（即結果為false或null） 被丟棄。搜尋條件通常參照在FROM子句中生成的表中的至少一欄；這不是必須的，但反之WHERE 子句是相當毫無用處的。

以下是WHERE子句的一些範例：

fdt是在 FROM子劇中衍生的資料表。不符合WHERE子句搜尋條件的列從FDT排除。請注意標量（scalar）子查詢作為值表示式的使用。就像任何其他查詢一樣，子查詢可以採用複雜的資料表表示式。還要注意在子查詢中fdt是如何被參照的。僅當c1也是子查詢衍生輸入表中的欄位名稱時，限定（qualifying）c1為fdt.c1是必要的。但即使不需要，限定欄位名稱會增加清晰度。此範例顯示了外部查詢的欄位命名作用域如何延伸到其內部查詢中。

7.2.3. GROUP BY及 HAVING子句

在經過WHERE篩選器後，衍生的輸入表可能會遭受到使用GROUP BY 子句進行分組，而使用HAVING子句進行群組資料列的排除。

在第二個查詢中，我們不能寫成 SELECT * FROM test1 GROUP BY x，因為對於可能與每個群組相關聯的欄位y來說沒有單一值。可以在選擇串列中參照被分組的列，因為它們在每個群組中具有單一值。

通常來說，如果將資料表被分組，則除了彙總表示式之外不能參照沒有在GROUP BY中條列出的欄位。彙總表示式的範例是：

這是另一個範例，它計算每個產品的總銷售額（而不是所有產品的總銷售）：

在這個範例，欄位product_id、p.name、及p.price必須在GROUP BY子句中是由於它們在查詢選擇串列中被參照（但詳見下文。）欄位s.units沒有需要在GROUP BY串列是由於它只能使用在彙總表示式（sum(...)），其代表一個產品的銷售。對於每個產品，查詢返回關於該產品所有銷售的摘要資料列。

如果產品資料被設置為product_id是主鍵（primary key），然後在上方的範例中它足以經由被product_id 分組，是由於名稱與價格將是在功能上依賴於產品ID，所以對與每個產品ID群組要返回哪些名稱和價格值都沒有模棱兩可。

在嚴格的SQL中， GROUP BY只能經由來源資料表的欄位進行分組但PostgreSQL擴展允許GROUP BY經由選擇串列中的欄位進行分組。允許經由值表示式來取代簡單的欄位名稱進行分組。

如果資料表已經被GROUP BY分組，但只有對某些群組感興趣，能使用HAVING子句，類似WHERE子句，從結果來排除群組。語法如下：

在HAVING子句中的表示式能引用已分組表示式及未分組表示式兩者（其必然涉及彙總函數。）

舉例：

再來一個更真實的範例：

在上方的範例中，WHERE子句正在經由一個未被分組的欄位選擇資料列（在過去四周內，該表示式僅適用於銷售額），儘管 HAVING子句限制輸出為總銷售額超過5000的群組。 請注意，彙總表示式在查詢的所有部分中不一定需要相同。

如果查詢包含彙總函數調用但沒有 GROUP BY子句，分組仍然會發生：結果是單個群組資料列（或者可能沒有資料列，如果經由HAVING排除該單一資料列。）即使沒有任何彙總函數調用或 GROUP BY子句，如果包含HAVING子句則同樣會發生。

7.2.4. GROUPING SETS、CUBE及 ROLLUP

更多比上方描述較複雜的分組操作可以使用 分組集合（grouping sets） 的概念。經由FROM及WHERE子句選擇的資料被每一個特定的分組集合分別地分組，對於每一個群組運算的彙總就如同簡單的GROUP BY子句，而後返回其結果。舉例來說：

每一個GROUPING SETS的子串列可以指定零個或多個欄位或表示式並且以它直接在GROUP BY子句中相同的方式來解釋。 一個空的分組集合意味著所有資料列被彙總到單一的群組（即使沒有輸入資料列被呈現也會輸出），如同上方所述對於沒有GROUP BY子句的彙總函數之情況。

為了指定兩個分組集合的常見型別提供了一個簡寫表示法。該形式的子句為

代表了給定的表達式串列和該串列的所有前綴，包括空串列；因此它相當於

這通常用於分析階層式資料：例如，部門，分部和公司的總薪資。

另一形式的子句為

表示給定的串列和所有可能的子集合（即power set。）因此

相當於

CUBE或ROLLUP 子句各自的元素也許是各自的表示式，或元素在括號中的子串列。在後一種情況下，為了生成各自的分組集合的意圖，該子串列被視為單個單元。例如：

相當於

以及

相當於

CUBE或ROLLUP 建構能被直接用在GROUP BY子句中，或被嵌套在GROUPING SETS子句內。如果GROUPING SETS子句被嵌套在另一個內，效果與內部子句內的所有元素被直接寫入外部子句中時相同。

如果多個的分組項目被指定在單一GROUP BY子句，分組集合的最終串列會是各自項目的外積。例如：

相當於

7.2.5. 窗函數處理

當使用多個窗函數，擁有在語法上等效於PARTITION BY及ORDER BY子句的所有窗函數在窗口定義中是被保證在資料上的單次傳遞中被評估。因此它們將看到相同的排序次序，即使ORDER BY沒有唯一決定次序。然而不保證具有不同於PARTITION BY或ORDER BY規範的函數之評估。（在這種情況下窗函數評估的傳遞之間通常需要排序步驟，並且不保證該排序會維持它的ORDER BY視為等效的資料列之次序。）

目前，窗函數總是必須要預先排序的資料，因此會依照一個或其他窗函數的PARTITION BY/ORDER BY子句整理查詢輸出。然而，不建議依賴這一點。使用顯式頂層ORDER BY子句如果要確保結果以特定方式排序。