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 就以名稱指定。在本例子，只有增加一點點內容而已。使用比較複雜的函數時，會有許多參數設定了預設值，以名稱或混合的方式來設定參數，可以節省許多撰寫的程式碼，也可以減少出錯的可能性。

注意

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

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

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

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

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

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

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

SQL 語法包含一連串的命令，命令是由一系列的指示記號所組合而成，以分號結尾。最後如果是串流輸入，也會結束一個命令。指示的合法性是由特別的命令語法所定義的。

指示記號可能是關鍵字、識別項、引號識別項、文字、或一個特別的字元符號。指示一般來說是以空白分隔（空白符號、定位符號、換行符號），但如果不會混淆的話，也不一定需要。（一般只出現在特殊字元用來調整了其他指示的型別）

舉個例子，下面就是一個合法（符合語法）的 SQL 輸入：

SELECT * FROM MY_TABLE;
UPDATE MY_TABLE SET A = 5;
INSERT INTO MY_TABLE VALUES (3, 'hi there');

這個序列包含了 3 個命令，每行一個（然而這不是一定的，同一行可以超過一個命令，而一個命令也可以分解為多行使用）。

順帶一提的是，註解也是 SQL 輸入的一部份，但不屬於任何指示記號，他們等同於空白字元。

SQL 語法並不是很嚴格要求什麼樣的指示記號來識別命令，或是哪些是運算子或參數。通常最前面的指示記號是命令的名稱，以上面的例子來說，我們通常會說是一個「SELECT」、一個「UPDATE」、以及一個「INSERT」命令。但對於 UPDATE 命令而言，有一個 SET 指示記號出現在某個地方是必要的；同樣地，INSERT 也需要有 VALUES 來搭配。精確的語法規則都在第 6 部份中的章節進行說明。

4.1.1. 識別項（Identifier）和關鍵字 （Keyword）

在上面的例子中的 SELECT、UPDATE、或是 VALUES，都是屬於關鍵字的範圍。所謂關鍵字，意即在 SQL 語言中，其具有固定的意義。像指示記號 MY_TABLE 則是屬於識別項。它識別表格的名稱，欄位名稱，或是其他的資料庫物件，端看命令如何看待該識別項。然而，有時候它們會被簡稱為「名稱」。關鍵字和識別項的文法結構是相同的，意即不看整個命令的話，是無法辨別到底是識別項還是關鍵字的。完整的關鍵字列表，收錄在附件 C 當中。

SQL 識別項與關鍵字必須以英文字母開頭（a - z，也可以是附加符號和非拉丁字母，中文沒問題）或是底線（_）。剩餘的字元可以是字母、底線、數字（0 - 9）、或錢字號（$）。注意錢字號，在標準 SQL 語法中是不允許使用的，所以可能會降低一些應用程式的可攜性。標準 SQL 也沒有定義包含數字或是以底線起迄的關鍵字，所以識別項這樣的形式定義是安全的，不會和標準未來的修訂相衝突。

資料庫系統不能使用長度超過 NAMEDATALEN -1 的識別項；太長的名稱仍然可以在命令中被輸入，但會被截斷。預設上，NAMEDATALEN 的設定是 64，所以最長的識別項名稱長度是 63 位元組。如果這個限制會造成困擾的話，你也可以調整 NAMEDATALEN 的編譯值，它的設定在 src/include/pg_config_manual.h 檔案中。

關鍵字和無引號識別項都是不分大小寫的，所以：

UPDATE MY_TABLE SET A = 5;

等同於：

uPDaTE my_TabLE SeT a = 5;

有一種寫法很常使用，就是把關鍵字用大寫表示，而識別項名稱使用小寫，例如：

UPDATE my_table SET a = 5;

第二種要介紹的識別項是，受限制的識別項，或是引號識別項。它的形式就是以雙引號括住的任何字串。受限制的識別項，就一定是識別項，不會是關鍵字。所以，「"select"」就會被識別為名稱為「select」的表格或欄位，而無引號的 select 就會被視為是關鍵字，也可能會產生解譯錯誤，如果剛好用在可能是表格或欄位名稱的位置上的話。使用引號識別項的例子如下：

UPDATE "my_table" SET "a" = 5;

引號識別項可以包含任何字元，除了字元碼為 0 的字元以外。（要包含雙引號字元的話，請使用連續兩個雙引號。）這可以用來建立原來不能使用的表格或欄位名稱，甚至是包含空白或＂&＂。但長度的限制仍然要遵守。

還有一種變形的引號識別項，允許包含跳脫的形式來表現萬國碼（unicode）。這種變形會以「U&」開頭（U大小寫皆可）緊接在前面的雙引號的前面，不能有任何空白在它們之間，例如：U&"foo"。（注意，這可能會和運算子的 & 產生混淆，但可以在運算子的 & 前後都加上空白來避免這個問題。）在雙引號內，萬國碼字元以跳脫的形式表現，也就是以倒斜線再接 4 位數的 16 進位碼，或倒斜線接一個加號再串一組 6 位數的 16 進位碼。例如，識別項 "data" 可以寫成這樣：

U&"d\0061t\+000061"

下面是稍微不簡明的例子是，俄文的＂slon＂（大象），以希伯萊文字母表現：

U&"\0441\043B\043E\043D"

如果希望以不同的跳脫字元來代替倒斜線的話，那麼可以雙引號結束後使用 UESCAPE 子句來指定，舉例來說：

U&"d!0061t!+000061" UESCAPE '!'

跳脫字元可以是任何的單一字元，除了 16 進位數字的字元、單引號、雙引號、或空白以外。注意指定的跳脫字元是以單引號括住，而不是雙引號。

內容要使用到跳脫字元的話，就重覆輸入 2 次。

萬國碼的跳脫語法，只能使用 UTF8 的編碼。如果有用到其他的編碼的話，只有在 ASCII 範圍（最大為 \007F）可以使用。4 位數及 6 位數的形式，可以組合配對用來指定 UTF-16 中，大於 U+FFFF 的字元，雖然 6 位數的形式單獨就可以解決這個問題（組合配對並不會直接被儲存起來，他們會被編碼成 UTF-8 再儲存。）

把識別項用引號括起來也可以用來保持它的大小寫狀態，沒有括起來的話，都會被轉成小寫字母。舉例來說，對 PostgreSQL 而言，FOO、foo、"foo"，三者都是一樣的，但 "Foo" 和 "FOO" 就彼此及前面三者都視為不同。（在 PostgreSQL 中，把未引號括起的名稱轉成小寫，並不是 SQL 的標準。SQL 標準反而是都轉成大寫。所以在 SQL 標準中，foo 應該是等同於 "FOO" 而不同於 "foo"。如果你要增加語法的可攜性的話，建議最好都使用引號括起特別的名稱，或者都不要使用引號。）

4.1.2. 常數

PostgreSQL 中有三種隱含型別的常數：字串、位元字串、和數值。常數也可以強制型別，有助於更精確的表達，也可以讓系統處理更有效率。接下來就開始進行相關的說明。

4.1.2.1. 字串常數

在 SQL 中，所謂的字串常數，指的是用單引號括住的任意字元串列，例如：'This is a string'。如果在字串常數內需要有單引號的話就使用連續兩個單引號，例如：'Dianne''s horse'。注意這不是雙引號，是兩個單引號。

兩個字串常數如果只用空白及至少一個換行符號所分隔的話，那個它們會被連在一起，和寫成一個字串是一樣的。舉例來說：

SELECT 'foo'
'bar';

等同於：

SELECT 'foobar';

但如果是這樣：

SELECT 'foo'      'bar';

語法上就不正確了。（這是來自於 SQL 奇怪的常規，PostgreSQL 單純只是遵循。）

4.1.2.2. C 語言樣式的跳脫字串常數

PostgreSQL 也支援跳脫字串常數，這些是 SQL 標準的延伸。跳脫字串常數使用的是字母 E （大小寫皆可），緊接著單引號所組成，例如：E'foo'。（如果字串有超過一行的話，也只要在第一個單引號前有 E 就可以了。）在跳脫字串當中，使用倒斜線開頭，就可以使用 C 語言式的倒斜線跳脫字串，通常是一個倒斜線再接一個字元，對應到一個特殊位元組的值，如 Table 4.1 所示。

Table 4.1. 倒斜線跳腳字串（Backslash Escape Sequence）

任何其他接在倒斜線後面的字元都僅以原樣呈現。而如果要包含一個倒斜線的話，就使用連續兩個倒斜線輸入。同樣地，要包含一個單引號的話，可以使用跳脫字串 \' 輸入，也可以用一般連續兩個單引號的方式輸入。

你需要確保你所使用的 8 進位或 16 進位創建的位元組序列，都是屬於資料庫中合法的字元集。當資料庫編輯是 UTF-8 時，就應該使用萬國碼跳脫寫法，或其他萬國碼的輸入方式，如前 4.1.2.3 中所述。（所謂其他的方式可能是自行組合每一個位元組，但這樣會是相當麻煩的事。）

萬國碼跳脫語法只有在 UTF8 的編碼下才完整支援。當有其他的字元編碼被使用時，就只能使用 ASCII 的範圍（最大值為 \u007F）中的值。4 位數及 6 位數的型式可以用來配對指定 UTF-16 超過 U+FFFF 的字元，即使 6 位數的型式就足以解決這個問題。（當使用配對語法，且字元編碼為 UTF8 時，他們會先被合併成單一字元，然後再編碼成 UTF-8。）

注意

如果設定檔參數 standard_conforming_string 設定為 off，PostgreSQL 不論在一般字串還是跳脫字串常數，都會把倒斜線識別為跳脫符號。然而，在 PostgreSQL 9.1 之前，這個參數的預設值為 on，表示只在跳脫字串常數裡，才把倒斜線視為跳脫符號。這樣的模式是更與標準相容的，但可能會破壞默認舊有設定的應用程式，也就是總是把倒斜線視為跳脫符號。在這樣的背景之下，你可以把這個參數設為 off，但更好的是，修改程式不再使用倒斜線跳脫符號。如果你需要使用倒斜線跳脫符號來表示一個特殊字元，請使用 E 開頭的字串常數。

有關 standard_conforming_string，順帶一提的是，還有 escape_string_warning 和 backslash_quote 兩個參數，也提供調整倒斜線在字串常數中的使用。

字元代碼 0 的字元不能使用在字串常數當中。

4.1.2.3. String Constants with Unicode Escapes

PostgreSQL 也支援其他跳脫字串的語法，可以用來直接輸入任意的萬國碼字元。萬國碼跳脫字串常數是以 U& （U& 或 u& 皆可）開頭，然後緊接著單引號括住的字串，記得中間不能有任何空白，例如：U&'foo'。（注意這可能會混淆到 & 的使用，最好在其他使用 & 作為運算子的指令中，在 & 前後 加上空白字元，以避免這個問題。）在括住的內容裡，萬國碼字元可以使用跳脫字元來指定，也就是使用倒斜線再接一組 4 位數的 16 進位值，或者以倒斜線加上加號再接一組 6 位數的 16 進位值。舉個例子，字串 'data' 也可以寫成：

U&'d\0061t\+000061'

下面是稍微不簡明的例子是，俄文的＂slon＂（大象），以希伯萊文字母表現：

U&'\0441\043B\043E\043D'

如果希望以不同的跳脫字元來代替倒斜線的話，那麼可以雙引號結束後使用 UESCAPE 子句來指定，舉例來說：

U&'d!0061t!+000061' UESCAPE '!'

跳脫字元可以是任何的單一字元，除了 16 進位數字的字元、單引號、雙引號、或空白以外。

萬國碼跳脫語法只有在 UTF8 的編碼下才完整支援。當有其他的字元編碼被使用時，就只能使用 ASCII 的範圍（最大值為 \u007F）中的值。4 位數及 6 位數的型式可以用來配對指定 UTF-16 超過 U+FFFF 的字元，即使 6 位數的型式就足以解決這個問題。（當使用配對語法，且字元編碼為 UTF8 時，他們會先被合併成單一字元，然後再編碼成 UTF-8。）

然而，萬國碼的跳脫字串語法，只有在參數 standard_conforming_strings 設定為 on 時有效。這是因為這個語法可能會造成 SQL 指令在編譯時的困擾，造成 SQL 隱碼攻擊（SQL injection） 或其他安全性的問題。如果這個參數設定為 off，那麼這個語法就會被禁止，並且產生錯誤訊息。

內容要使用到跳脫字元的話，就重覆輸入 2 次。

4.1.2.4. 錢字引號字串常數

標準的語法用於字串常數的設定很方便的，但如果字串裡有很多單引號或倒斜線，可讀性就很低了，因為它們都必須再連續多一個符號輸入。像這樣的例子，要改善可讀性的話，PostgreSQL 提供了另一個方式，稱作「錢字引號」（dollar quoting），來描述字串常數。錢字引號字串常數包含一個錢字號（$），可省略或多個字元所組成的「標籤」，另一個錢字號，組成字川的任何序列文字，再一個錢字號，與起始的錢字引號同樣的標籤，再一個錢字號。舉例來說，這裡有兩個不同使用錢字引號的方式，但都是「Dianne's horse」

$$Dianne's horse$$
$SomeTag$Dianne's horse$SomeTag$

注意在錢字引號字串中，單引號的使用就不需要跳脫處理了。實際上，在錢字引號字串中，沒有字元需要跳脫處理：字串內容就原樣輸出。倒斜錢並不特別，就算是錢字號也是，除非它們是引號標籤配對的一部份。

巢狀錢字字串常數是可以的，只要在不同層選擇不同的標籤就好。最常見的用途就是撰寫函數定義。舉例如下：

$function$
BEGIN
    RETURN ($1 ~ $q$[\t\r\n\v\\]$q$);
END;
$function$

這裡，「$q$[\t\r\n\v\]$q$」以錢字引號字串輸出就是「[\t\r\n\v\]」，作為 PostgreSQL 的函數內容。但這個字串並不會和外層的 $function$ 配對。對外層的字串而言，它只是被包裏的一部份字元而已。

以錢字符作為標籤（如果有的話）的引號字串和無引號的識別項，遵循相同的規則，除了它無法包含錢字符號以外。標籤是區分大小寫的，所以 $tag$String content$tag$ 是正確的，而 $TAG$String content$tag$ 是不合法的。

錢字引號字串緊接著關鍵字或識別項的話，就必須以空白分隔；否則錢字號的終止符可能會被當作前面識別項的一部份。

錢字引號並不是標準 SQL 的用法，但當撰寫一些複雜字串的時候，會比標準語法更為便利。當字串常數內嵌於另一個常數時，也是很好用的情境，像自訂函數時就時常用到。使用單引號的語法時，前面例子中的每一個倒斜線，需要使用 4 個倒斜線才能表示（原來字串常數時需要雙倒斜線，然後在執行階段時也需要雙倒斜線，一共就是 4 倍）。

4.1.2.5. 位元字串常數（Bit-string Constants）

位元字串常數看起來就像是一般的字串常數，只是將 B（大小寫皆可）放在引號的前面（不能有空白），例如：B'1001'。而在位元字串當中，只能有 0 或 1 的存在。

另一方面，位元字串常數也可以表示一個 16 進位的值，使用的先導字為 X（大小寫皆可），例如：X'1FF'。這個撰寫方式與使用前段方式，以 4 位數 2 進位表示每一個 16 進位位數，是相同的結果。

這兩種位元字串常數的表達方式，都可以在字串中換行，如同一般的字串常數。錢字引號表示方式不能使用在位元字串常數上。

4.1.2.6. 數值常數（Numeric Constants）

數值常數可以以下列語法輸入：

digits
digits.[digits][e[+-]digits]
[digits].digits[e[+-]digits]
[digits]e[+-]digits

這裡的 digits 指的是 0 到 9 的多位數十進位數字。如果有小數點的話，在小數點之前或之後要有數字。在指數標記 e 之前，也必須要有數字。字串中間不能再有其他字元或空白出現。注意，最前面正負號並不是數值常數的一部份，它是屬於運算子的概念。

下面是一些合法數值常數的例子：

42 3.5 4. .001 5e2 1.925e-3

數值常數如果沒有小數點或指數標記的話，預設就會被假定為整數，32 位元以內的為整數型別（interger），否則就會以 64 位元的大整數型別（bigint）來處理。其次就會宣告為數值型別（numeric）。只要包含小數點或指數標記的數值，都會預設使用數值型別。

預設數值常數的資料型別只是整個型別解析演算法的開端而已。在多數的情況下，各種常數會自動被轉換為最貼近內容的適當型別。不過，如果需要的話，你可以強制指定一個資料型別給該常數。舉例來說，你可以強制以實數型別（real 或 float4）來處理該數值：

REAL '1.23'  -- string style
1.23::REAL   -- PostgreSQL (historical) style

實際上，在型別轉換上還有一些特殊的情況，留待後續探討。

4.1.2.7. 其他型別常數

任意型別的常數，可以使用下列的語法來表示：

type 'string'
'string'::type
CAST ( 'string' AS type )

字串常數的內容會由型別轉換的程序 type 來處理，其結果就會得到該常數的專屬型別。明定型別轉換可以被省略，如果不會混淆的話（舉例來說，要輸入給特定的表格欄位的話，因為已有型別宣告，就不會混淆），那麼就會自動給定型別。

字串常數可以使用一般 SQL 標準寫法，或是錢字引號寫法。

還可以使用函數式的語法來撰寫：

typename ( 'string' )

但並非所有的型別都可以使用這個方式，請參閱 4.2.9 節取得詳細說明。

「::」、CAST()、及函數式語法，也可以用來指定任何表示式在執行中的型別轉換，如同 4.2.9 節中所描述的。要避免語法上的混淆，「type 'string'」這個語法，只能用在指定簡單的文字常數，另一個限制是，不能用於陣列型別。陣列常數的型別指定，請使用 :: 或 CAST() 的語法。

4.1.3. 運算子（Operators）

一個運算子最長可以是 NAMEDATALEN - 1（預設為 63 個字元），除了以下的字元之外：

• • * / <> = ~ ! @ # % ^ & | ` ?

還有一些運算子的限制：

• 「--」和「/*」都不能出現在運算子裡，因為它們表示註解的開始。

• 多字元的運算子不能以 + 或 - 結尾，除非名稱裡也包含了下列字元：

  ~ ! @ # % ^ & | ` ?

舉個例子，@- 可以是合法的運算子，但 *- 就不合法。這個限制是讓 PostgreSQL 解譯 SQL 語法時，可以不需要在不同的標記間使用空白分隔。

當使用非 SQL 標準的運算子時，你通常需要在相隣的運算子間使用空白以免混淆。舉例來說，如果你已經定義了一個左側單元運算子 @，你就不能使用 X*@Y，必須寫成 X* @Y，以確保 PostgreSQL 可以識別為兩個運算子，而不是一個。

4.1.4. 特殊字元

有一些字元並不是字母型態，而具有特殊意義，但並非運算子。詳細的說明請參閱相對應的語法說明。本節僅簡要描述這些特殊字元的使用情境。

• 錢字號（$）其後接著數字的話，用來表示函數宣告或預備指令的參數編號。其他的用法還有識別項的一部份，或是錢字引號常數。

• 小括號（( )）一般用來強調表示式並且優先運算。還有某些情況用於表示某些 SQL 指令的部份的必要性。

• 中括號（[ ]）用於組成陣列的各個元素。詳情請參閱 8.15 節有關於陣列的內容。

• 逗號（,）用於一般語法上的結構需要，來分隔列表中的單元。

• 分號（;）表示 SQL 指令的結束。它不能出現在指令中的其他位置，除非是在字串常數當中，或是引號識別項。

• 冒號（:）用在取得陣列的小項。（參閱 8.15 節）在某些 SQL 分支（篏入式 SQL 之類的）中，冒號用來前置變數名稱。

• 米字號（*）用來表示表格中所有的欄位，或複合性的內容。它也可以用於函數宣告時，不限制固定數量的參數。

• 頓號（.）用在數值常數之中，也用於區分結構、表格、及欄位名稱。

4.1.5. 註解（Comments）

註解是以連續兩個破折號開頭，一直到行結尾的字串。例如：

-- This is a standard SQL comment

另外，C 語言的註解語法也可以使用：

/* multiline comment
 * with nesting: /* nested block comment */
 */

這樣的註解，以「/*」開頭，一直持續到對應的「*/」出現才結束。這樣區塊式的註解可以巢狀使用，所以你可以一次註解掉一堆包含註解的指令。這點是 SQL 的標準，和 C 語言的使用不太一樣的地方。

註解會在進一步的語法分析前被消去，也可以方便地以空白字元替代。

4.1.6. 運算優先權（Operator Precedence）

Table 4.2 列出在 PostgreSQL 中，運算子的運算優先權及運算次序。大多數的運算子都是相同的運算優先權，並且是左側運算。這些優先權與次序是撰寫在解譯器的程式當中的。

你有時候需要加上括號，當遇到二元運算子與一元運算子一起出現時。舉個例子：

SELECT 5 ! - 6;

會被解譯為：

SELECT 5 ! (- 6);

因為解譯器並不知道實際的情況，所以它可能會搞錯。「!」是一個後置運算子，並非中置運算子。在這個例子中，要以想要的方式進行運算的話，你必須要改寫為：

SELECT (5 !) - 6;

這是為了延展性而需要付出的代價。

Table 4.2. Operator Precedence (highest to lowest)

注意，使用與內建運算子同名的自訂運算子，運算優先權的規則也會以原規則適用，如同上面的樣子。舉例來說，如果你定義了一個「+」的運算子，用於自訂的資料型態，那麼它就會和內建的「+」擁有相同的運算優先權，而與你的運算內容無關。

當某個結構操作的運算子用於 OPERATOR 語法之中時，如下所示：

SELECT 3 OPERATOR(pg_catalog.+) 4;

OPERATOR 建構式被用來為任何運算子，取得如 Table 4.2 中所示的預設運算優先權。不論在 OPERATOR() 中指定什麼運算子，都會回傳 true 的結果。

注意

PostgreSQL 在 9.5 之前的運算優先權有一些不同。比較特別的是，比較運算子「<= >= <>」是和一般其他運算子是相同等級的；「IS」先前的優先權較高；而「NOT BETWEEN」和相關的建構式行為不一致，使得在某些情況下，「NOT」和「BETWEEN」的優先權不同。這些規則的改變是為了與 SQL 標準有更好的相容性，減少因為等價轉換的不一致處理所造成的困擾。大多數的情況，這些改變並不需要使用習慣的改變，也不會產生沒有運算子的錯誤，而且都可以透過增加括號來解決。然而，有一些極端的情況可能會在沒有錯誤的情況改變其運算行為。如果你很關心這些變化，很擔心這些無聲的錯誤，你可以打開參數 operator_precedence_warning 來測試你的程式，然後檢查是否有警告被記錄下來。

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

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

• 常數或文字內容

• 欄位的引用

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

• 子參數表示式

• 欄位選擇表示式

• 運算子宣告

• 函數呼叫

• 彙總表示式

• 窗函數呼叫

• 型別轉換

• 校對轉換（collation expression）

• 賦值子查詢（scalar subquery）

• 陣列建構式

• 列建構式

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

除了這個列表之外，還有一些建構式也會應用到表示式，但並沒有特別定義語法規則。一般來說，他們會包含函數或運算子的操作，在第 9 章中會有適當的說明。其中有一個例子便是 IS NULL 字句。

我們已經在 4.1.2 節中討論過常數了，所以接下來就從常數以下的項目繼續說明。

4.2.1. 欄位引用

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

correlation.columnname

「correlation」（所屬名稱）是其所屬表格的名稱（也可能需要包含結構名），或是表格的別名（在 FROM 子句中所定義的）。所屬名稱和分隔用的句點是可以省略的，如果欄位名稱在目前查詢中的所有表格中是唯一的話。（參閱第 7 章）

4.2.2. 函數參數引用

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

$number

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

CREATE FUNCTION dept(text) RETURNS dept
    AS $$ SELECT * FROM dept WHERE name = $1 $$
    LANGUAGE SQL;

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

4.2.3. 子參數表示式（Subscripts）

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

expression[subscript]

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

expression[lower_subscript:upper_subscript]

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

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

mytable.arraycolumn[4]
mytable.two_d_column[17][34]
$1[10:42]
(arrayfunction(a,b))[42]

在最後一個例子中，括號是必須的。關於陣列，在 8.15 節有更多說明。

4.2.4. 欄位選擇

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

expression.fieldname

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

mytable.mycolumn
$1.somecolumn
(rowfunction(a,b)).col3

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

(compositecol).somefield
(mytable.compositecol).somefield

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

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

(compositecol).*

這個記號在不同的地方有不同的用法，請參閱 8.16.5 節的說明。

4.2.5. 運算子宣告（Operator Invocations）

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

運算子記號的語法規則依 4.1.3 節的說明，或是關鍵字 AND、OR、和 NOT，又或是如下形式的限定運算子名稱：

OPERATOR(schema.operatorname)

哪些特定的運算子的使用與運算方式，端看系統與使用者如何定義。在第 9 章中會說明內建的運算子詳情。

4.2.6. 函數呼叫

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

function_name ([expression [, expression ... ]] )

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

sqrt(2)

內建函數在第 9 章說明，其他的函數可由使用者自訂。

參數可以是選擇性的附加名稱，請參閱 4.3 節的內容。

注意

函數如果只有一個參數，而又是複合型別的話，就稱作使用了欄位選擇語法；反過來說，欄位選擇語法也可以寫成函數的形式。這是因為 col(table) 和 table.col 是可以互換的。這並非標準 SQL，但 PostgreSQL 支援了，因為這使得函數的使用可以模擬「計算欄位」（computed fields）。更多資訊請參閱 8.16.5 節。

4.2.7. 彙總表示式

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

aggregate_name (expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]

aggregate_name (ALL expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]

aggregate_name (DISTINCT expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]

aggregate_name ( * ) [ FILTER ( WHERE filter_clause ) ]

aggregate_name ( [ expression [ , ... ] ] ) WITHIN GROUP ( order_by_clause ) [ FILTER ( WHERE filter_clause ) ]

這裡的 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 節，除非該表示式無法輸出成欄位名稱或數字。舉例如下：

SELECT array_agg(a ORDER BY b DESC) FROM table;

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

SELECT string_agg(a, ',' ORDER BY a) FROM table;

但不能這樣寫：

SELECT string_agg(a ORDER BY a, ',') FROM table;  -- incorrect

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

如果 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 只接受前者。）

有次序彙總查詢如下：

SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY income) FROM households;

 percentile_cont
-----------------
           50489

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

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

SELECT
    count(*) AS unfiltered,
    count(*) FILTER (WHERE i < 5) AS filtered
FROM generate_series(1,10) AS s(i);

 unfiltered | filtered
------------+----------
         10 |        4
(1 row)

預先內建的彙總函數將在 9.20 節中介紹，其他彙總函數可以由使用者自行設計。

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

當彙總表示式使用在子查詢（參閱 4.2.11 節及 9.22 節）中時，彙總計算就會一般性地處理子查詢中的資料。但如果該彙總計算的參數用到了外層的變數時，就會產生例外情況：彙整計算是屬於最接近的外層查詢，並且只處理該層的查詢資料。這個彙總表示式對整體而言，只是一個子查詢的引用，它會被視為一個常數的結果，限制它只會出現在 HAVING 子句的運算層次而已。

4.2.8. 窗函數呼叫

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

function_name ([expression [, expression ... ]]) [ FILTER ( WHERE filter_clause ) ] OVER window_name
function_name ([expression [, expression ... ]]) [ FILTER ( WHERE filter_clause ) ] OVER ( indow_definition )
function_name ( * ) [ FILTER ( WHERE filter_clause ) ] OVER window_name
function_name ( * ) [ FILTER ( WHERE filter_clause ) ] OVER ( indow_definition )

定義「窗」，請使用下列語法：

[ existing_window_name ][ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]

選擇性的 frame_clause 語法如下：

{ RANGE | ROWS } frame_start
{ RANGE | ROWS } BETWEEN frame_start AND frame_end

frame_start 及 frame_end 的語法如下：

UNBOUNDED PRECEDING
value PRECEDING 
CURRENT ROW
value FOLLOWING 
UNBOUNDED FOLLOWING

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

window_name 是一個定義在 WINDOW 子句中的命名。另一方面，一個完整的窗也可以是被括號括起來，使用和 WINDOW 子句相同語法的定義。詳見 SELECT 語法頁面。值得探討的是，OVER wname 並不完全等同於 OVER (wname ...)；後者隱含著複製及修改窗的定義，而如果包含 frame 子句的話，就會被拒絕執行。

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

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

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

在 RANGE 模式裡，如果 frame_start 設定為 CURRENT ROW 的話，表示窗框始於目前列同序的那一列（使用 ORDER BY 時，排序相同的那一列），同理，frame_end 設定為 CURRENT ROW 時，表示窗框止於排序相同的列。而在 ROWS 模式時，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 子句。

內建的窗函數會在 9.57 節中說明，使用者也可以自行設計窗函數。任何內建或自訂的一般函數或統計函數，都可以當作窗函數來使用。（有序集合和假定集合的彙總數，目前不能當作窗函數來使用。）

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

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

更多窗函數的說明請參閱 3.5 節、9.21 節、及 7.2.5 節。

4.2.9. 型別轉換

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

CAST ( expression AS type )
expression::type

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

當強制轉換應用於已知型別的值表示式時，它表示執行時型別轉換。只有定義了合適的型別轉換操作，操作才能成功。請注意，這與使用帶常數的強制轉換略有不同，如 4.1.2.7 節所示。應用於未經修飾的字串文字的強制轉換表示將型別初始分配給文字常數，因此對於任何型別（如果字串文字的內容都是資料型別的可接受輸入語法）都會成功。

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

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

typename ( expression )

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

注意

函數式語法實際上只是一個函數呼叫。當兩個標準轉換語法之一用於執行轉換時，它將在內部呼叫已註冊的函數來執行轉換。按照慣例，這些轉換函數與它們的輸出類型具有相同的名稱，因此「函數式語法」只不過是直接呼叫底層的轉換函數。顯然，這不是一個可移植式應用程序應該依賴的東西。有關更多詳情，請參閱 CREATE CAST。

4.2.10. 排序表示式

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

expr COLLATE collation

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

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

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

SELECT a, b, c FROM tbl WHERE ... ORDER BY a COLLATE "C";

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

SELECT * FROM tbl WHERE a > 'foo' COLLATE "C";

請注意，在後者的情況下，COLLATE 子句附加到我們希望影響的運算子的輸入參數。 無論運算子或函數呼叫 COLLATE 子句的哪個參數被附加到哪個參數都沒有關係，因為運算子或函數套用的排序規則是透過考慮所有參數衍生的，並且顯式 COLLATE 子句將覆蓋所有其他排序規則參數。（然而，將不匹配的 COLLATE 子句連接到多個參數是錯誤的，更多細節請參閱第 23.2 節）。因此，這會産生與前面的例子相同的結果：

SELECT * FROM tbl WHERE a COLLATE "C" > 'foo';

但是這會有錯：

SELECT * FROM tbl WHERE (a > 'foo') COLLATE "C";

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

4.2.11. Scalar 子查詢

Scalar 子查詢指的是括號中的普通 SELECT 查詢，但它只回傳一個資料列的一個欄位。（有關撰寫查詢的訊息，請參閱第 7 章。）執行 SELECT 查詢並在周圍的值表示式中使用單個回傳的值。使用回傳多於一個資料列或多於一個欄位的查詢作為 scalar 子查詢是錯誤的。（但是，如果在特定執行過程中子查詢不回傳任何資料列，則不會出現錯誤；Scalar 結果將視為空）。子查詢可以引用周圍查詢中的變數，該變數在任何一次運算期間都將用作常數的子查詢。有關子查詢的其他表示式，另請參閱第 9.22 節。

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

SELECT name, (SELECT max(pop) FROM cities WHERE cities.state = states.name)
    FROM states;

4.2.12. 陣列建構函數

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

SELECT ARRAY[1,2,3+4];
  array
---------
 {1,2,7}
(1 row)

預設情況下，陣列元素型別是成員表示式的通用型別，使用與 UNION 或 CASE 結構相同的規則來決定（參閱 10.5 節）。您也可以透過明確將陣列建構函數轉換為所需的型別來覆蓋它，例如：

SELECT ARRAY[1,2,22.7]::integer[];
  array
----------
 {1,2,23}
(1 row)

這與分別將每個表示式轉換為陣列元素型別的效果相同。有關型別轉換的更多訊息，請參閱第 4.2.9 節。

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

SELECT ARRAY[ARRAY[1,2], ARRAY[3,4]];
     array
---------------
 {{1,2},{3,4}}
(1 row)

SELECT ARRAY[[1,2],[3,4]];
     array
---------------
 {{1,2},{3,4}}
(1 row)

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

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

CREATE TABLE arr(f1 int[], f2 int[]);

INSERT INTO arr VALUES (ARRAY[[1,2],[3,4]], ARRAY[[5,6],[7,8]]);

SELECT ARRAY[f1, f2, '{{9,10},{11,12}}'::int[]] FROM arr;
                     array
------------------------------------------------
 {{{1,2},{3,4}},{{5,6},{7,8}},{{9,10},{11,12}}}
(1 row)

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

SELECT ARRAY[]::integer[];
 array
-------
 {}
(1 row)

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

SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%');
                                 array
-----------------------------------------------------------------------
 {2011,1954,1948,1952,1951,1244,1950,2005,1949,1953,2006,31,2412,2413}
(1 row)

SELECT ARRAY(SELECT ARRAY[i, i*2] FROM generate_series(1,5) AS a(i));
              array
----------------------------------
 {{1,2},{2,4},{3,6},{4,8},{5,10}}
(1 row)

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

用 ARRAY 建構的陣列索引值的下標始終以 1 開頭。有關陣列的更多訊息，請參閱第 8.15 節。

4.2.13. 資料列建構者

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

SELECT ROW(1,2.5,'this is a test');

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

資料列建構函數可以包含語法 rowvalue.，它將被延展為資料列內容的元素列表，就像在 SELECT 回傳列表的使用 . 語法時一樣（請參閱第 8.16.5 節）。例如，如果資料列具有欄位 f1 和 f2，則這些欄位是相同的：

SELECT ROW(t.*, 42) FROM t;
SELECT ROW(t.f1, t.f2, 42) FROM t;

注意

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

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

CREATE TABLE mytable(f1 int, f2 float, f3 text);

CREATE FUNCTION getf1(mytable) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL;

-- No cast needed since only one getf1() exists
SELECT getf1(ROW(1,2.5,'this is a test'));
 getf1
-------
     1
(1 row)

CREATE TYPE myrowtype AS (f1 int, f2 text, f3 numeric);

CREATE FUNCTION getf1(myrowtype) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL;

-- Now we need a cast to indicate which function to call:
SELECT getf1(ROW(1,2.5,'this is a test'));
ERROR:  function getf1(record) is not unique

SELECT getf1(ROW(1,2.5,'this is a test')::mytable);
 getf1
-------
     1
(1 row)

SELECT getf1(CAST(ROW(11,'this is a test',2.5) AS myrowtype));
 getf1
-------
    11
(1 row)

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

SELECT ROW(1,2.5,'this is a test') = ROW(1, 3, 'not the same');

SELECT ROW(table.*) IS NULL FROM table;  -- detect all-null rows

更多細節請參閱第 9.23 節。資料列建構函數也可以與子查詢結合使用，如第 9.22 節所述。

4.2.14. 表示式運算規則

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

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

SELECT true OR somefunc();

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

SELECT somefunc() OR true;

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

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

如果必須強制執行某部份的運算指令，則可以使用 CASE 結構（請參閱第 9.17 節）。例如，這是試圖避免在 WHERE 子句中除以零不可信任的方式：

SELECT ... WHERE x 
>
 0 AND y/x 
>
 1.5;

但這樣是安全的：

SELECT ... WHERE CASE WHEN x 
>
 0 THEN y/x 
>
 1.5 ELSE false END;

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

然而，CASE 對於這些問題並不是萬能的。上述技術的一個局限是它不能阻止對常數子表示式的預先評估。如第 37.6 節所述，標記為 IMMUTABLE 的函數和運算子可以在查詢計劃時進行運算，而不是在執行時進行運算。因此，例如：

SELECT CASE WHEN x 
>
 0 THEN x ELSE 1/0 END FROM tab;

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

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

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

SELECT CASE WHEN min(employees) > 0
            THEN avg(expenses / employees)
       END
    FROM departments;

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

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

使用 GENERATED ALWAYS AS 語法來產生自動欄位，舉例來說：

務必要加上 STORED 關鍵字以寫入自動欄位. 更多細節請參閱

使用 INSERT 或 UPDATE 指令時，不能直接指定內容至自動欄位，但可以使用 DEFAULT 關鍵字來設定預設值。

針對「包含預設值的欄位」及 「自動欄位」進行比較:

當資料列第一次被寫入時，如果該欄位沒有提供任何值，將採用預設值寫入; 而自動欄位則是在資料列被更新時，根據其他欄位來產生對應的值，該值無法被覆寫。

• 「包含預設值的欄位」通常不會參考到表格的其他欄位; 而「自動欄位」通常都會參考到其他欄位。

• 「含預設值的欄位」在設定預設值時可以使用易變函數 (volatile function)，舉例來說: random() 或者是取得當前時間的函式; 而「自動欄位」則不允許使用。

「自動欄位」和「包含自動欄位的表格」有一些限制：

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

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

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

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

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

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

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

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

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

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

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 指令數量的限制，而不是處理資料的限制。只有真正有改變資料庫內容的指令才會有指令識別碼。

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

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

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

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

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

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

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

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

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

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

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

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

小技巧

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

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

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

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

你可以：

• 加入欄位

• 移除欄位

• 加入限制條件

• 移除限制條件

• 改變預設值

• 改變欄位資料型別

• 變更欄位名稱

• 變更表格名稱

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

5.5.1. 加入欄位

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

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

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

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

小技巧

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

5.5.2. 移除欄位

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

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

5.5.3. 加入限制條件

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

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

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

5.5.4. 移除限制條件

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

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

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

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

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

5.5.5. 變更欄位預設值

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

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

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

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

5.5.6. 變更欄位資料型別

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

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

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

5.5.7. 變更欄位名稱

要變更某個欄位的名稱：

5.5.8. 變更表格名稱

要變更表格的名稱：

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

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

5.4.1. 檢查

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

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

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

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

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

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

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

或等同於：

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

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

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

5.4.2. 限制無空值

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

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

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

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

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

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

小技巧

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

5.4.3. 限制唯一性

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

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

則是表格限制的寫法。

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

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

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

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

5.4.4. 主鍵（Primary Keys）

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

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

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

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

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

5.4.5. 外部鍵（Foreign Keys）

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• 同時也刪去訂單

• 其他？

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

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

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

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

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

5.4.6. 除外宣告（Exclusion Constraints）

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

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

當一個資料庫物件被建立時，它會先指定存取權限給擁有者，而擁有者一般來說就是執行建立指令的使用者。對大多數的資料庫物件來說，其預設的狀態就是只有擁有者（或超級使用者）可以對該物件進行所有操作。要讓給其他使用者來操作的話，就必須進行授權的動作。

有很多不同種類的權限：SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、TRIGGER、CREATE、CONNECT、TEMPORARY、EXECUTE、USAGE。這些權限對於不同物件的效果，會因為是哪一種物件而有所差別（表格、函式...等等）。要瞭解完整在 PostgreSQL 中所支援的各種物件權限，請參考 GRANT 語法頁面。這裡的內容主要說明如何使用。

修改和移除一個資料庫物件，是只有擁有者才具備的權力。

要把一個物件被指派給一個新的擁有者的話，使用該物件的 ALTER 指令，例如：ALTER TABLE。超級使用者也可以做指派的動作；原來的擁有者如果它仍是該物件的管理群組一員的話，當然也可以；再來就管理群組新的成員。

要進行授權行為的話，請使用 GRANT 指令。舉例來說，如果 joe 是一個使用者，而 accounts 是一個表格，要讓他可以獲得更新表格資料的權力：

GRANT UPDATE ON accounts TO joe;

使用 ALL 的權限，就代表授權所有可設定的權限。

有一個特別的使用者是 PUBLIC，代表的是系統內的所有使用者。當資料庫內有很多使用者時，可以制定「群組（group）」來簡化管理。這部份詳細的說明請參閱第 21 章。

要移除權限，請使用 REVOKE 指令：

REVOKE ALL ON accounts FROM PUBLIC;

物件擁有者的特殊權限（例如DROP、GRANT、REVOKE...等）都是和擁有者一起設定，而無法單獨授權。不過，擁有者可以選擇移除自己的權限，例如建立一個唯讀的表格，讓自己和其他人一樣。

回到前面所說的，只有物件的擁有者（或超級使用者）可以變更該物件的權限。然而，也可以使用「with grant option」讓另一個使用者可以代授權給其他使用者。不過如果這個「grant option」被移除時，所有被代授權的使用者都會同時喪失該權限。更詳細的說明請參閱 GRANT 及 REVOKE 說明頁面。

PostgreSQL 實作了 SQL/MED 的部份標準，讓你可以存取不在 PostgreSQL 管理下的資料，重點是，你仍然只需要使用 SQL 語法。這樣的資料我們稱作為外部資料。（注意這部份的使用不要和外部鍵搞混了，外部鍵是資料庫內部的一種條件限制。）

外部資料的存取是透過「Foreign data wrapper」（外部資料封裝技術）。外部資料封裝技術是一組函式庫，用於和外部的資料源溝通，它封裝了資料連線和存取資料的細節。有一些外部資料封裝的套件收錄在 contrib 模組之中，參閱附件 F。其他種類的外部封裝套件則由第三方產品提供。如果沒有適合你的資料源的套件的話，你也可以自己寫一個，參閱第 56 章。

要存取外部資料，你需要建立外部服務物件，用它來連結特定的外部資料源，也可以對套件進行一些設定。然後你還需要建立幾個外部資料表，用於定義外部資料的資料結構。外部資料表的使用就如一般的表格一樣，只不過它沒有實際儲存任何資料罷了。當外部資料表被查詢時，PostgreSQL 會透過外部資料封裝套件，從外部資料源取得資料，或者傳送資料到外部，進行更新資料。

存取外部資料可能需要對外部資料源進行認證。這可以利用使用者映對（user mapping）的方法，讓每個 PostgreSQL 使用者在使用部資料表時，可以傳送自己的認證資訊。

進一步的資訊，請參閱 CREATE FOREIGN DATA WRAPPER、CREATE SERVER、CREATE USER MAPPING、CREATE FOREIGN TABLE、IMPORT FOREIGN SCHEMA 等內容。

Schema 在台灣並沒有習慣的中文說法，所以仍使用原文，而不翻譯。

PostgreSQL 資料庫叢集（cluster）可以包含一個或多個資料庫。使用者和群組則是共用於叢集的層次，但沒有任何資料面是在資料庫之間能共用的。任何用戶端連到資料庫服務，都只能存取單一資料庫，你必須在連線時指定一個資料庫。

注意

在叢集內的使用者並不需要對每個資料庫都有使用權。使用者共用指的是它們不能有同名的情況，例如在同一個叢集內，不能有兩個使用者名稱都叫 joe。但系統可以只允許 joe 使用某些叢集內的資料庫。

一個資料庫可以包含一個或多個 schema，它會包含一些資料表。Schema 也可以包含一些資料庫物件，像是資料型別、函數、和運算子。同樣的物件名稱在不同的 schema 中是不會衝突的。舉例來說，schema1 和 myschema 都可以擁有一個叫作 mytable 的資料表。和資料庫不同， schema 並不是完全隔離的：使用者可以直接取用他們連接的資料庫中的任何 schema，只要他們擁有足夠的權限。

使用 schema 有幾個好處：

• 允許多個使用者存取相同資料庫，而不會互相干擾。

• 將資料庫物件建立邏輯上的管理層，它們會更有彈性。

• 第三方的應用結構可以放在不同的 schema 中，避免有撞名的情況產生。

Schema 和作業系統裡的資料夾是類似的，只是它不能使用巢狀結構。

5.9.1. 建立 Schema

要建立 schema，請使用 CREATE SCHEMA 指令。給予一個自訂的名稱。例如：

CREATE SCHEMA myschema;

要在 schema 中建立或存取某個物件，請使用句點（.）將兩者名稱串連起來：

schema.table

這個形式在任何可以使用資料表的地方都是可以的，包含資料表結構更新指令，以及在接下來章節會討論到的資料處理指令。（我們只提到資料表的部份，但相同的概念用於其他資料庫物件都是一樣的，像是資料型別和函數。）

實際上，更一般化的語法是：

database.schema.table

也可以這樣使用，但目前這只是為了符合 SQL 標準而已。如果你填上了資料庫的名稱，也必須填上你所連線的資料庫而已。

所以，要在新的 schema 中建立一個資料表，請使用：

CREATE TABLE myschema.mytable (
 ...
);

要移除一個 schema，它必須要是空的，也就是所有所屬物件都已經被移除了，請使用：

DROP SCHEMA myschema;

但你也可以同步移除 schema 及其所屬物件，請使用：

DROP SCHEMA myschema CASCADE;

這個部份的機制請參閱 5.13 節，會深入介紹移除時的問題。

通常你會想要建立一個 schema 給某個使用者使用（這是一種藉由命名空間規畫來限制使用者權限的方法）。可以使用下列語法：

CREATE SCHEMA schema_name AUTHORIZATION user_name;

你甚至可以省略 schema 名稱，省略的話，schema 名稱會與使用者名稱相同。請參閱後續的 5.8.6 節來瞭解如何使用。

Schema 名稱以「pg_」開頭的，是系統的保留名稱，使用者不能使用這樣的名稱建立 schema。

5.9.2. 公開的 Schema

在前面我們所建立的資料表都沒有指定 schema 名稱。預設使用的 schema 是「public」，每一個資料庫都會有這個 schema。所以，下面兩種寫法是一樣的：

CREATE TABLE products ( ... );

以及：

CREATE TABLE public.products ( ... );

5.9.3. Schema 搜尋路徑

完整的名稱寫法是冗長而不容易使用的，通常最好不要把一些特別的 schema 名稱寫到應用程式裡。而資料表時常是以簡要的寫法引用，也就是只寫資料表本身的名稱。資料庫系統依據搜尋路徑的規則找到該資料表。在搜尋路徑上所遇到的第一個資料表就會被使用。如果整個搜尋路徑走完都沒有符合的資料表，那麼才會回報錯誤，即使該資料表名稱有出現在資料庫裡的其他 schema 中。

第一個會被搜尋的 schema，就是目前的 schema。除此之外也用於新的資料表建立，當 CREATE TABLE 未指定 schema 名稱的話，也會依搜尋路徑的 schema 建立。

要顯示目前的搜尋路徑，請使用下面的指令：

SHOW search_path;

預設的情況是：

 search_path
--------------
 "$user", public

第一個項目指的就是和目前使用者同名的 schema 會被使用，而如果沒有同名的，它就會被忽略。第二個項目則是先前介紹過的公開 schema。第一個被找到的 schema，就會是新建物件時預設的位置，這就是為什麼預設都會被建立在公開的 schema。當某個物件在使用（資料表結構調整、資料更新、或查詢指令）時沒有註明 schema 的話，那也會使用搜尋路徑來找到符合的物件。不過，預設上只會搜尋公開的 schema。

要設定新的搜尋路徑，請使用：

SET search_path TO myschema,public;

（我們在這邊暫時忽略掉 $user，因為還沒有立即性的需要。）然後我們就可以試著存取資料表而不用加上 schema：

DROP TABLE mytable;

因為 myschema 在搜尋路徑裡是第一個項目，所以新的物件就會被建立在該處。

我們也可以這樣寫：

SET search_path TO myschema;

這樣的話，不指定的話就不再能夠再使用公開的 schema 了。「public」schema 並沒有比較特別，除了它一開始就會存在之外，它也可以被移除。

請參閱 9.25 節，將會介紹其他設定 schema 搜尋路徑的方式。

搜尋路徑也用於資料型別、函數、及運算子的搜尋，就如同在資料表上的行為一樣。資料型別和函數名稱完整的寫法也和資料表相同。如果你需要特別指出運算子的完整路徑的話，它比較特別，你必須這樣寫：

OPERATOR(schema.operator)

這是為了避免語法上的混淆。如下所示：

SELECT 3 OPERATOR(pg_catalog.+) 4;

實務上我們都還是依賴路徑搜尋來使用運算子，這樣可以避免使用冗長且低可讀性的程式碼。

5.9.4. Schemas 與權限

預設的情況，使用者無法存取任何不屬於他們的 schema 中的物件。要允許存取的話，該 schema 的擁有者必須要授予 USAGE 權限給其他使用者。要允許其他使用者使用某個 schema 中的物件，通常需要額外給予適當的權限。

使用者想要在其他使用者的 schema 中建立新物件的話，就必須要授予 CREATE 的權限。注意，預設上，所有的使用者在 public schema 中，都具備 CREATE 和 USAGE 權限。這使得所有的使用者在連線到某個資料庫之後，就可以在 public schema 上新增物件。如果你不希望這樣，你可以移除這些權限：

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

前面的「public」指的是 schema，是一個物件識別器；而後面的「PUBLIC」指的是所有使用者，是一個關鍵字。所以使用不同的大小寫，可以再複習 4.1.1 節的內容。

5.9.5. 系統資訊 Schema

除了 public 以及使用者自行建立的 schema 之外，每一個資料庫還有一個稱作 pg_catalog 的 schema，它包含了系統資訊的資料表和內建的資料型別、函數、及運算子。 pg_catlog 永遠都都是搜尋路徑裡的有效項目。它沒有明確地顯示在搜尋路徑裡，但卻是隱含優先搜尋，在那些明定的搜尋項目之前。這是為了確保內建的物件的名稱都能被找到。然而，你可以把 pg_catlog 放在搜尋路徑的最後面，如果你希望自訂的同名物件能優先被使用的話。

系統用的資料表都以「pg_」開頭，為的就是確保不會有衝突的情況出現，以免將來新的系統資料表和你現在所定義的資料表同名。（以預設的搜尋路徑來說，一個簡單的資料表使用，會直接被同名的系統資料表取代。）系統資料表會一直遵循這個命名規則，就不會產生衝突，只要使用者不使用「 pg_」開頭的命名方式就好了。

5.9.6. 使用樣版

Schema 可以在許多方面協助你組織你的資料。有一些巧妙的樣版值得推薦，也很方便以預設的方式支援：

• 如果你沒有建立任何 schema 的話，那麼所有使用者就是隱含著都使用 public schema。這種情況指的是都沒有設定任何 schema，而主要推薦給在一個資料庫中，只有一個使用者的情況。這樣的樣版設定也適合之後轉換到無 schema 設計的資料庫環境。

• 你可以為每一個使用者建立一個同名的 schema。回想一下先前介紹的預設搜尋路徑，第一個項目就是 $user，表示該使用者的名稱。所以，每一個使用者有一個專屬的 schema，預設上，他們就只存取他們所擁有的 schema。 如果你使用這個情境樣版，你也許會需要移除 public schema 的權限，甚至直接移除它，讓使用者真正被隔離在他們自己的 schema 中。

• 要安裝共享的應用程式（每個人共享資料表，有一些第三方提供的延伸套件，或其他的東西。），把他們放到不同的 schema 裡，然後記得要設定好適當的存取權限。使用者可以使用完整的名稱來存取這些共享的應用程式，或把他們加入到搜尋路徑中，由使用者自己來決定。

5.9.7. 可攜性

在標準 SQL 中，在同一個 schema 中的物件，分別被不同使用者擁有，是不被允許的。然而，有一些實作系統甚至不允許使用者建立和自己不同名的 schema。事實上，schema 和使用者的概念，對於只支援基本 schema 的資料庫系統本身而言，幾乎是相同的。所以，許多使用者會認為完整名稱指的是 user_name.table_name。這也就是為什麼 PostgreSQL 建議你這樣為每一個使用者建立他們同名的 schema。

再者，在標準 SQL 裡，也沒有所謂 public schema 的概念。極致相容標準的話，你就不應該使用，或移除 public schema。

當然，也有些 SQL 資料庫並沒有實作 schema，或提供其他跨資料庫存取的命名方式。如果你需要和這些系統共同運作，要提高可攜性的方式就是不要使用任何 schema。

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. Declarative Partitioning

PostgreSQL offers a way to specify how to divide a table into pieces called partitions. The table that is divided is referred to as a partitioned table. The specification consists of the partitioning method and a list of columns or expressions to be used as the partition key.

All rows inserted into a partitioned table will be routed to one of the partitions based on the value of the partition key. Each partition has a subset of the data defined by its partition bounds. The currently supported partitioning methods are range, list, and hash.

• 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.

• 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 as adding or dropping constraints on only the partitioned table, when partitions exist, is not supported. 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 directly, attempts to use TRUNCATE ONLY on a partitioned table will always return an error.

• 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.

• You cannot drop the NOT NULL constraint on a partition's column if the constraint is present in the parent 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:

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.

   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.

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

   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.

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:

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:

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:

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:

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:

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

5.11.2.3. Limitations

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

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

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

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

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

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).
3. Add non-overlapping table constraints to the child tables to define the allowed key values in each.

   Typical examples would be:

   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:

   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:
4. For each child table, create an index on the key column(s), as well as any other indexes you might want.
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:

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

   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:

   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:

   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.

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:

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

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

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.

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.

• 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:

  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 (分割區修剪)是一種查詢最佳化技術，可提高分割資料表的效能。 舉個例子：

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

Some or all of the partitions might use index scans instead of full-table sequential scans, but the point here is that there is no need to scan the older partitions at all to answer this query. When we enable partition pruning, we get a significantly cheaper plan that will deliver the same answer:

Note that partition pruning is driven only by the constraints defined implicitly by the partition keys, not by the presence of indexes. Therefore it isn't necessary to define indexes on the key columns. Whether an index needs to be created for a given partition depends on whether you expect that queries that scan the partition will generally scan a large part of the partition or just a small part. An index will be helpful in the latter case but not the former.

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.

Note

Execution-time partition pruning currently only occurs for the Append and MergeAppend node types. It is not yet implemented for the ModifyTable node type, but that is likely to be changed in a future release of PostgreSQL.

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 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.

到目前為止，我們已經解釋瞭如何將資料新增到資料表以及如何更新資料了。剩下的就是討論如何刪除不再需要的資料。 正如新增資料時只能新增整個資料列一樣，你只能從資料表中以資料列為單位刪除資料。在前面的章節中，我們解釋了SQL沒有提供直接處理某個資料列的方法。因此，只能透過指定要刪除的行必須符合的條件來刪除指定的資料列。如果資料列中有主鍵，則可以指定確切的資料列。但是，你也可以刪除全部符合條件的資料列，更可以一次刪除資料表中的所有資料列。

您使用 DELETE 指令刪除資料列；該語法與 UPDATE 指令十分類似。例如，要從產品表中刪除價格為 10 的所有資料列，請使用：

DELETE FROM products WHERE price = 10;

如果你只是寫：

DELETE FROM products;

那麼資料表中的所有資料列都將被刪除！ 請程式設計師一定要小心使用。

表格是關連式資料庫結構裡的主要物件，因為它負責存放資料，但並不是資料庫中唯一的物件。還有許多其他種的物件存在，讓使用上更方便或管理更有效率。這些其他的物件並不在本章中討論，但我們先在這裡列出讓你知道：

• 視觀

• 函數與運算子

• 資料型別和領域

• 觸發事件和規則覆寫

關於這些物件的詳細說明安排在第 V 部份。

前一章討論了如何建立資料表和其他結構來保存資料。現在是把資料表填滿的時候了。本章介紹如何新增、更新和刪除資料表的資料。 下一章將會完整說明如何從資料庫中取回你遺落在裡面的資料。

當你建立了一個複雜的資料庫結構，包含了許多資料表，也設計了許多外部索引鍵、檢視表、觸發事件、函數.....等等。也就是說，其實你建立了一堆物件之間的關連性。舉例來說，資料表的外部索引鍵就與另一個資料表有著參考的關連性。

要維護整個資料庫結構的完整性，PostgreSQL 得確保你不能在有關連性的情況下，隨意刪去物件。舉例來說，企圖刪去在 5.3.5 節中，我們所使用過的產品資料表，而訂單資料表與其有相依的關連性，那就會產生如下的錯誤訊息：

DROP TABLE products;

ERROR:  cannot drop table products because other objects depend on it
DETAIL:  constraint orders_product_no_fkey on table orders depends on table products
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

這個錯誤訊息包含了很有用的指引：如果你不想要一個個處理其相依關連性，那可以一次刪去他們：

DROP TABLE products CASCADE;

如此所有相依的物件就會被刪除了，所有相互依存的物件都會，是遞迴式的處理流程。在這個例子中，它不會移除訂單資料表，只會移除外部索引鍵的限制條件，因為沒有其他物件與該外部索引鍵相依。（如果你要確認 DROP ... CASCADE 會處理哪些物件，你可以用 DETAIL 取代 CASCADE，就會輸出其相依的物件。）

幾乎所有 PostgreSQL 的 DROP 指令都支援 CASCADE 的用法。當然，有些自然的關連性是和物件型別有關。你也可以使用 RESTRICT 來取代 CASCADE 的位置，以強制以預設的行為來處理，也就是絕對不會刪去其他相關的物件。

注意

根據 SQL 標準，不論是 RESTRICT 或 CASCADE，都必須要在 DROP 指令中明確表示，但沒有任何一套資料庫系統真的這樣設計。不過，都會內定預設行為是 RESTRICT 或 CASCADE，每個資料庫不同。

如果 DROP 指令列出了多個物件，CASCADE 只有在這些物件之外還有相依性時才會需要。舉個例子，當執行「DROP TABLE tab1, tab2」時，即使 tab1 與 tab2 之間有外部索引鍵的相依關係，而沒有指定 CASCADE，這個操作也會完成。

對於使用者自訂的函數來說，PostgreSQL 會引用函數的外顯屬性來判斷其相依性，例如函數的參數或輸出型態，但函數內部執行的相依關係就無法追蹤了。舉個列子：

CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow',
                             'green', 'blue', 'purple');

CREATE TABLE my_colors (color rainbow, note text);

CREATE FUNCTION get_color_note (rainbow) RETURNS text AS
  'SELECT note FROM my_colors WHERE color = $1'
  LANGUAGE SQL;

（參閱 37.4 節，瞭解 SQL 語言的函數。）PostgreSQL 會知道 get_color_note 函數相依於 rainbow 資料型別：也就是刪去該資料型別時，也會強制要刪去該函數，因為它的參數將不再合法。但 PostgreSQL 就無法發現 get_color_note 和 my_colors 之間的關連性，當該資料表被移除時，此函數並不會跟著被移除。這種情況有好有壞，函數基本上還是合法的，即使內含的資料表不存在的話，頂多就是執行會出錯就是了，只要再建立該名稱的資料表就可以讓這個函數重新正常運作。

資料表在建立的時候，並不包含任何資料。以各種方式使用資料庫之前，要做的第一件事就是新增資料。概念上，資料是一次新增一列。當然你也可以新增多列，但就沒有辦法新增少於一列。 即使只知道某些欄位的值，也必須建立一個完整的資料列。

要建立新的資料列，請使用 INSERT 指令。該命令需要資料表的名稱和各欄位的資料內容。例如，來看看第 5 章中的產品資料表：

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

新增資料列的指令可能如下所示：

INSERT INTO products VALUES (1, 'Cheese', 9.99);

資料內容按資料表表中欄位的順序列出，以逗號分隔。通常，資料內容會是文字（常數），但運算表示式也是允許的。

上面的語法有缺點，就是你需要知道資料表中欄位的順序。為了避免這種情況，您可以明確地列出欄位。例如，以下兩個命令與上面的命令具有相同的效果：

INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', 9.99);
INSERT INTO products (name, price, product_no) VALUES ('Cheese', 9.99, 1);

許多用戶認為總是列出欄位名稱是一個很好的習慣。

如果你並沒有所有欄位的內容，則可以省略其中一些欄位。在這種情況下，那些欄位將會以預設值代入。如下所示：

INSERT INTO products (product_no, name) VALUES (1, 'Cheese');
INSERT INTO products VALUES (1, 'Cheese');

第二種形式是屬於 PostgreSQL 延伸寫法。 從左邊開始的欄位填入所給定的內容，其餘的欄位則使用預設值。

為了清楚起見，你也可以明確地指定個別欄位或整個資料列都使用預設值：

INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', DEFAULT);
INSERT INTO products DEFAULT VALUES;

您可以在一個命令中新增多個資料列：

INSERT INTO products (product_no, name, price) VALUES
    (1, 'Cheese', 9.99),
    (2, 'Bread', 1.99),
    (3, 'Milk', 2.99);

也可以以查詢的結果新增（可能沒有資料，一個資料列或多個資料列）：

INSERT INTO products (product_no, name, price)
  SELECT product_no, name, price FROM new_products
    WHERE release_date = 'today';

這包含完整 SQL 查詢機制（第 7 章）用於計算需要新增的資料列。

小技巧

同時要新增大量資料時，請考慮使用 COPY 指令。它不像 INSERT 指令那麼靈活，但是效率更高。有關提高批次新增資料效率的更多資訊，請參閱第 14.4 節。

將已經在資料庫中的資料做修改被稱為更新。您可以單獨更新某個資料列，或資料表中的所有資料列，或是部份資料列。每個欄位可以單獨更新，而不影響其他欄位。

要更新現有的資料列，請使用 UPDATE 指令。這需要三種資訊：

1. 要更新的資料表和欄位的名稱

2. 資料欄位新的內容

3. 哪些資料列要更新

回想一下第 5 章，SQL 通常不提供資料列的唯一識別資訊。因此，直接指定要更新哪一行通常是不行的，而是指定該資料列必須符合哪些條件才能更新。只有你在資料表中有一個主鍵（決定於是否你有宣告過）之後，才能通過選擇與主鍵相匹配的條件來可靠地解決單個資料列的問題。圖形化的資料庫管理工具依賴這個方式才能允許你單獨更新指定的資料列。

例如，這個指令會將價格為 5 的所有產品更新為 10：

UPDATE products SET price = 10 WHERE price = 5;

這結果可能是零個，一個或多個資料列被更新。嘗試更新卻沒有匹配到任何資料列，並不是一種錯誤。

我們來詳細看看這個命令。首先是關鍵字 UPDATE，然後是資料表的名稱。像往常一樣，資料表的名稱可以使用加上 schema 的完整路徑名稱，否則就會在搜尋路徑中尋找。接下來的關鍵字是 SET，後面接著欄位名稱，等號和新的欄位內容。新的欄位內容可以是任何的運算表示式，而不僅僅是一個常數。例如，如果要將所有產品的價格提高10％，則可以使用：

UPDATE products SET price = price * 1.10;

如你所見，欄位的表示式可以引用資料列中現有的內容。我們還遺漏了 WHERE 子句。如果省略的話，則意味著資料表中的所有資料列都會被更新。如果存在的話，則只有更新符合 WHERE 條件的那些資料列。請注意，SET 子句中的等號是一個賦值運算，而 WHERE 子句中的等號是比較運算，但這不會造成任何誤解。當然，WHERE 條件不一定是等號運算。 還有許多其他的運算子可以使用（詳見第 9 章）。但是表示式需要能產生為布林運算的結果。

您可以在使用 UPDATE 指令時，以 SET 子句中列出多個欄位賦值來更新多個欄位內容。例如：

UPDATE mytable SET a = 5, b = 3, c = 1 WHERE a > 0;

檢索過程或從資料庫檢索資料的命令稱之為查詢。在 SQL 中，SELECT 命令用於進行條件查詢。 SELECT 指令的一般語法是：

[WITH with_queries] SELECT select_list FROM table_expression [sort_specification]

以下各節介紹了資料列表（select list），資料表和排序規則的詳細資訊。由於 WITH 查詢是高級功能，因此最後再介紹。

一種簡單的查詢形式如下：

SELECT * FROM table1;

假設有一個名稱為 table1 的資料表，該指令會將取出 table1 中的所有資料表和所有用戶定義的欄位。（檢索的方法取決於用戶端的應用程序，例如，psql 程序將在屏幕上顯示一個 ASCII-art 表格，而用戶端的程式函式庫將提供從查詢結果中提取單一值的功能。選擇資料列表定義「*」表示由資料表表示式所產生的所有欄位。篩選列表可以是可用欄位的子集或使用欄位進行計算。例如，如果 table1 具有名稱為 a，b 和 c（也許是其他）的欄位，則可以進行以下查詢：

SELECT a, b + c FROM table1;

（假設 b 和 c 是數字型別）。更多細節詳見 7.3 節。

FROM table1是一種簡單的資料表表示式：它只讀取一個資料表。一般來說，資料表表示式可以是一般的資料表，交叉查詢和子查詢的複雜結構。但是，你也可以完全省略資料表表示式，並使用 SELECT 指令作為計算機：

SELECT 3 * 4;

使用資料列表中的表達式產生變動的結果，是更為常用的方式。例如，你可以這樣呼叫一個函數：

SELECT random();

前面的章節解釋了如何建立資料表，如何填入資料以及如何操作這些資料。現在我們是時候討論如何從資料庫中檢索資料了。

兩個查詢的結果可以使用集合操作聯、交集和差集來組合。其語法為：

query1 UNION [ALL] query2
query1 INTERSECT [ALL] query2
query1 EXCEPT [ALL]query2

query1 和 query2 是到目前為止討論過的任何查詢功能。集合操作也可以巢狀也可以連接，例如：

query1 UNION query2 UNION query3

會如下方式執行：

(query1 UNION query2) UNION query3

UNION 將 query2 的結果有效率地附加到 query1 的結果中（但不能保證這是實際回傳資料列的次序）。此外，除非使用了UNION ALL，否則它將以與 DISTINCT相同的方式從結果中消除重複的資料列。

INTERSECT 返回 query1 的結果和 query2 的結果中所有共同的資料列。除非使用 INTERSECT ALL，否則會刪除重複的資料列。

EXCEPT 回傳 query1 的結果中但不包含在 query2 的結果中的所有資料列。（這有時被稱為兩個查詢之間的差集。）同樣地，除非使用 EXCEPT ALL，否則重複資料列將被刪除。

為了計算兩個查詢的聯集、交集或差集，兩個查詢必須是「union compatible」，這意味著它們回傳相同數量的欄位，相應的欄位具有相容的資料型別，如 10.5 節所述。

有時在修改資料列的操作過程中取得資料是很方便的。INSERT、UPDATE 和 DELETE 指令都有一個選擇性的RETURNING 子句來支持這個功能。使用 RETURNING 可以避免執行額外的資料庫查詢來收集資料，特別是在難以可靠地識別修改的資料列時尤其有用。

RETURNING 子句允許的語法與 SELECT 指令的輸出列表相同（詳見第 7.3 節）。它可以包含命令目標資料表的欄位名稱，或者包含使用這些欄位的表示式。常用的簡寫形式是 RETURNING *，預設是資料表的所有欄位，且相同次序。

在 INSERT 中，可用於 RETURNING 的資料是新增的資料列。這在一般的資料新增中並不是很有用，因為它只會重複用戶端所提供的資料。但如果是計算過的預設值就會非常方便。 例如，當使用串列欄位（serial）提供唯一識別時，RETURNING 可以回傳分配給新資料列的 ID：

CREATE TABLE users (firstname text, lastname text, id serial primary key);

INSERT INTO users (firstname, lastname) VALUES ('Joe', 'Cool') RETURNING id;

對於 INSERT ... SELECT，RETURNING 子句也非常有用。

在 UPDATE 中，可用於 RETURNING 的資料是被修改的資料列新內容。例如：

UPDATE products SET price = price * 1.10
  WHERE price <= 99.99
  RETURNING name, price AS new_price;

在 DELETE 中，可用於 RETURNING 的資料是已刪除資料列的內容。例如：

DELETE FROM products
  WHERE obsoletion_date = 'today'
  RETURNING *;

如果目標資料表上有觸發函數的話（第 38 章），則可用於 RETURNING 的資料是由該觸發函數所修改的資料列。因此，由觸發函數計算檢查欄位是 RETURNING 的另一個常見用法。

如前一節所述，SELECT 指令中的資料示表表示式透過各種可能地組合資料表、view、消除資料列、分組等來建構中介的虛擬資料表。這個資料表最終會被傳遞給資料列表的處理。資料列表確認中介資料表的哪些欄位是實際上要輸出的。

7.3.1. 資料列表項目

最簡單的選擇列表是*，它表示資料表表示式產生的所有欄位。否則，資料列表是逗號分隔的參數表示式列表（如第 4.2 節中所定義的）。例如，它可能是欄位名稱的列表：

SELECT a, b, c FROM ...

欄位名稱 a、b 和 c 是 FROM 子句中資料表的欄位的實際名稱，或者是由第 7.2.1.2 節中所賦予它們的別名。資料列表中可用的命名空間與 WHERE 子句中的命名空間相同，除非是使用分組查詢，在這種情況下，它與 HAVING 子句中的相同。

如果多個資料表具有相同名稱的欄位，則還必須加上資料表的名稱，如下所示：

SELECT tbl1.a, tbl2.a, tbl1.b FROM ...

處理多個資料表時，查詢特定資料表的所有欄位也是可以的：

SELECT tbl1.*, tbl2.a FROM ...

有關 table_name.* 表示法的更多信息，請參閱第 8.16.5 節。

如果在資料列表中使用任意值表示式，則概念上是它將新的虛擬欄位加到回傳的資料表中。參數表示式對每個結果資料列計算一次，將該資料列的值替換為任何欄位引用。但是資料列表中的表示式不必引用 FROM 子句的資料表表示式中的任何欄位；例如，它們可以是常數算術表示式。

7.3.2. 欄位命名標籤

資料列表中的項目可以被分配用於後續處理的名稱，例如在 ORDER BY 子句中使用或由用戶端應用程序顯示。 例如：

SELECT a AS value, b + c AS sum FROM ...

如果沒有使用 AS 指定輸出欄位的名稱，系統將分配一個預設的欄位名稱。對於簡單欄位的引用，就是引用欄位的名稱。對於函數呼叫，就是函數的名稱。對於複雜的表示式，系統將會產成一個通用的名稱。

AS 關鍵字是選用的，但前提是新的欄位名稱不為任何PostgreSQL 關鍵字（請參閱附錄C）。為避免與關鍵字意外撞名，你可以對欄位名稱使用雙引號。例如，VALUE 是一個關鍵字，所以就不能這樣使用：

SELECT a value, b + c AS sum FROM ...

但這樣就可以了：

SELECT a "value", b + c AS sum FROM ...

為了防止未來可能增加的關鍵字，建議你習慣使用 AS 或總是在欄位名稱使用雙引號。

注意

這裡輸出欄位的命名與 FROM 子句中的命名不同（參閱第 7.2.1.2 節）。可以重新命名相同的欄位兩次，但在資料列表中分配的名稱是將要回傳的名稱。

7.3.3. DISTINCT

在處理了資料列表之後，結果資料表可以選擇性地消除重複的資料列。 DISTINCT 關鍵字在 SELECT 之後直接寫入以指定這個動作：

SELECT DISTINCT select_list ...

（如果不是 DISTINCT，而是關鍵字 ALL，可用於指定保留所有資料列的預設行為。）

顯然，如果至少有一個欄位值不同，則兩個資料列就會被認為是不同的。 在這個比較中，空值（null）被認為是相等的。

或者，使用表示式可以指定資料列如何被認為是不同的：

SELECT DISTINCT ON (expression [, expression ...]) select_list ...

這裡表示式是一個任意的運算表示式，對所有資料列進行求值運算。所有表示式相等的一組資料列被認為是重複的，並且只有該組的第一個資料列會被保留在輸出中。請注意，集合中的「第一行」是不可預知的，除非查詢按足夠的欄位進行排序，以保證進到 DISTINCT 過濾器的資料列是唯一排序。（在 ORDER BY 排序後才進行 DISTINCT ON 處理。）

DISTINCT ON 子句不是SQL標準的一部分，有時被認為是不好的樣式，因為其結果有潛在的不確定性。透過在 FROM 中智慧地使用 GROUP BY 和子查詢，可以避免這種結構，但這卻往往是最方便的選擇。

A table expression computes a table. The table expression contains a FROM clause that is optionally followed by WHERE, GROUP BY, and HAVING clauses. Trivial table expressions simply refer to a table on disk, a so-called base table, but more complex expressions can be used to modify or combine base tables in various ways.

The optional WHERE, GROUP BY, and HAVING clauses in the table expression specify a pipeline of successive transformations performed on the table derived in the FROM clause. All these transformations produce a virtual table that provides the rows that are passed to the select list to compute the output rows of the query.

7.2.1. The FROM Clause

The FROM Clause derives a table from one or more other tables given in a comma-separated table reference list.

FROM table_reference [, table_reference [, ...]]

A table reference can be a table name (possibly schema-qualified), or a derived table such as a subquery, a JOIN construct, or complex combinations of these. If more than one table reference is listed in the FROM clause, the tables are cross-joined (that is, the Cartesian product of their rows is formed; see below). The result of the FROM list is an intermediate virtual table that can then be subject to transformations by the WHERE, GROUP BY, and HAVING clauses and is finally the result of the overall table expression.

When a table reference names a table that is the parent of a table inheritance hierarchy, the table reference produces rows of not only that table but all of its descendant tables, unless the key word ONLY precedes the table name. However, the reference produces only the columns that appear in the named table — any columns added in subtables are ignored.

Instead of writing ONLY before the table name, you can write * after the table name to explicitly specify that descendant tables are included. There is no real reason to use this syntax any more, because searching descendant tables is now always the default behavior. However, it is supported for compatibility with older releases.

7.2.1.1. Joined Tables

A joined table is a table derived from two other (real or derived) tables according to the rules of the particular join type. Inner, outer, and cross-joins are available. The general syntax of a joined table is

T1 join_type T2 [ join_condition ]

Joins of all types can be chained together, or nested: either or both T1 and T2 can be joined tables. Parentheses can be used around JOIN clauses to control the join order. In the absence of parentheses, JOIN clauses nest left-to-right.

Join TypesCross join

T1 CROSS JOIN T2

For every possible combination of rows from T1 and T2 (i.e., a Cartesian product), the joined table will contain a row consisting of all columns in T1 followed by all columns in T2. If the tables have N and M rows respectively, the joined table will have N * M rows.

FROM T1 CROSS JOIN T2 is equivalent to FROM T1 INNER JOIN T2 ON TRUE (see below). It is also equivalent to FROM T1, T2.

Note

This latter equivalence does not hold exactly when more than two tables appear, because JOIN binds more tightly than comma. For example FROMT1 CROSS JOIN T2 INNER JOIN T3 ON condition is not the same as FROMT1, T2 INNER JOIN T3 ON condition because the condition can referenceT1 in the first case but not the second.Qualified joins

T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 ON boolean_expression
T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 USING ( join column list )
T1 NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2

The words INNER and OUTER are optional in all forms. INNER is the default; LEFT, RIGHT, and FULL imply an outer join.

The join condition is specified in the ON or USING clause, or implicitly by the word NATURAL. The join condition determines which rows from the two source tables are considered to “match”, as explained in detail below.

The possible types of qualified join are:INNER JOIN

For each row R1 of T1, the joined table has a row for each row in T2 that satisfies the join condition with R1.LEFT OUTER JOIN

First, an inner join is performed. Then, for each row in T1 that does not satisfy the join condition with any row in T2, a joined row is added with null values in columns of T2. Thus, the joined table always has at least one row for each row in T1.RIGHT OUTER JOIN

First, an inner join is performed. Then, for each row in T2 that does not satisfy the join condition with any row in T1, a joined row is added with null values in columns of T1. This is the converse of a left join: the result table will always have a row for each row in T2.FULL OUTER JOIN

First, an inner join is performed. Then, for each row in T1 that does not satisfy the join condition with any row in T2, a joined row is added with null values in columns of T2. Also, for each row of T2 that does not satisfy the join condition with any row in T1, a joined row with null values in the columns of T1 is added.

The ON clause is the most general kind of join condition: it takes a Boolean value expression of the same kind as is used in a WHERE clause. A pair of rows from T1 and _T2_match if the ON expression evaluates to true.

The USING clause is a shorthand that allows you to take advantage of the specific situation where both sides of the join use the same name for the joining column(s). It takes a comma-separated list of the shared column names and forms a join condition that includes an equality comparison for each one. For example, joining T1 and T2_with USING (a, b) produces the join condition ON T1.a = T2.a AND T1.b = T2_.b.

Furthermore, the output of JOIN USING suppresses redundant columns: there is no need to print both of the matched columns, since they must have equal values. While JOIN ON produces all columns from T1 followed by all columns from T2, JOIN USING produces one output column for each of the listed column pairs (in the listed order), followed by any remaining columns from T1, followed by any remaining columns from T2.

Finally, NATURAL is a shorthand form of USING: it forms a USING list consisting of all column names that appear in both input tables. As with USING, these columns appear only once in the output table. If there are no common column names, NATURAL JOIN behaves like JOIN ... ON TRUE, producing a cross-product join.

Note

USING is reasonably safe from column changes in the joined relations since only the listed columns are combined. NATURAL is considerably more risky since any schema changes to either relation that cause a new matching column name to be present will cause the join to combine that new column as well.

To put this together, assume we have tables t1:

 num | name
-----+------
   1 | a
   2 | b
   3 | c

and t2:

 num | value
-----+-------
   1 | xxx
   3 | yyy
   5 | zzz

then we get the following results for the various joins:

=> SELECT * FROM t1 CROSS JOIN t2;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   1 | a    |   3 | yyy
   1 | a    |   5 | zzz
   2 | b    |   1 | xxx
   2 | b    |   3 | yyy
   2 | b    |   5 | zzz
   3 | c    |   1 | xxx
   3 | c    |   3 | yyy
   3 | c    |   5 | zzz
(9 rows)

=> SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   3 | c    |   3 | yyy
(2 rows)

=> SELECT * FROM t1 INNER JOIN t2 USING (num);
 num | name | value
-----+------+-------
   1 | a    | xxx
   3 | c    | yyy
(2 rows)

=> SELECT * FROM t1 NATURAL INNER JOIN t2;
 num | name | value
-----+------+-------
   1 | a    | xxx
   3 | c    | yyy
(2 rows)

=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |   3 | yyy
(3 rows)

=> SELECT * FROM t1 LEFT JOIN t2 USING (num);
 num | name | value
-----+------+-------
   1 | a    | xxx
   2 | b    |
   3 | c    | yyy
(3 rows)

=> SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   3 | c    |   3 | yyy
     |      |   5 | zzz
(3 rows)

=> SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |   3 | yyy
     |      |   5 | zzz
(4 rows)

The join condition specified with ON can also contain conditions that do not relate directly to the join. This can prove useful for some queries but needs to be thought out carefully. For example:

=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |     |
(3 rows)

Notice that placing the restriction in the WHERE clause produces a different result:

=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num WHERE t2.value = 'xxx';
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
(1 row)

This is because a restriction placed in the ON clause is processed before the join, while a restriction placed in the WHERE clause is processed after the join. That does not matter with inner joins, but it matters a lot with outer joins.

7.2.1.2. Table and Column Aliases

A temporary name can be given to tables and complex table references to be used for references to the derived table in the rest of the query. This is called a table alias.

To create a table alias, write

FROM table_reference AS alias

or

FROM table_reference alias

The AS key word is optional noise. alias can be any identifier.

A typical application of table aliases is to assign short identifiers to long table names to keep the join clauses readable. For example:

SELECT * FROM some_very_long_table_name s JOIN another_fairly_long_name a ON s.id = a.num;

The alias becomes the new name of the table reference so far as the current query is concerned — it is not allowed to refer to the table by the original name elsewhere in the query. Thus, this is not valid:

SELECT * FROM my_table AS m WHERE my_table.a > 5;    -- wrong

Table aliases are mainly for notational convenience, but it is necessary to use them when joining a table to itself, e.g.:

SELECT * FROM people AS mother JOIN people AS child ON mother.id = child.mother_id;

Additionally, an alias is required if the table reference is a subquery (see Section 7.2.1.3).

Parentheses are used to resolve ambiguities. In the following example, the first statement assigns the alias b to the second instance of my_table, but the second statement assigns the alias to the result of the join:

SELECT * FROM my_table AS a CROSS JOIN my_table AS b ...
SELECT * FROM (my_table AS a CROSS JOIN my_table) AS b ...

Another form of table aliasing gives temporary names to the columns of the table, as well as the table itself:

FROM table_reference [AS] alias ( column1 [, column2 [, ...]] )

If fewer column aliases are specified than the actual table has columns, the remaining columns are not renamed. This syntax is especially useful for self-joins or subqueries.

When an alias is applied to the output of a JOIN clause, the alias hides the original name(s) within the JOIN. For example:

SELECT a.* FROM my_table AS a JOIN your_table AS b ON ...

is valid SQL, but:

SELECT a.* FROM (my_table AS a JOIN your_table AS b ON ...) AS c

is not valid; the table alias a is not visible outside the alias c.

7.2.1.3. Subqueries

Subqueries specifying a derived table must be enclosed in parentheses and must be assigned a table alias name (as in Section 7.2.1.2). For example:

FROM (SELECT * FROM table1) AS alias_name

This example is equivalent to FROM table1 AS alias_name. More interesting cases, which cannot be reduced to a plain join, arise when the subquery involves grouping or aggregation.

A subquery can also be a VALUES list:

FROM (VALUES ('anne', 'smith'), ('bob', 'jones'), ('joe', 'blow'))
     AS names(first, last)

Again, a table alias is required. Assigning alias names to the columns of the VALUES list is optional, but is good practice. For more information see Section 7.7.

7.2.1.4. Table Functions

Table functions are functions that produce a set of rows, made up of either base data types (scalar types) or composite data types (table rows). They are used like a table, view, or subquery in the FROM clause of a query. Columns returned by table functions can be included in SELECT, JOIN, or WHERE clauses in the same manner as columns of a table, view, or subquery.

Table functions may also be combined using the ROWS FROM syntax, with the results returned in parallel columns; the number of result rows in this case is that of the largest function result, with smaller results padded with null values to match.

function_call [WITH ORDINALITY] [[AS] table_alias [(column_alias [, ... ])]]
ROWS FROM( function_call [, ... ] ) [WITH ORDINALITY] [[AS] table_alias [(column_alias [, ... ])]]

If the WITH ORDINALITY clause is specified, an additional column of type bigint will be added to the function result columns. This column numbers the rows of the function result set, starting from 1. (This is a generalization of the SQL-standard syntax for UNNEST ... WITH ORDINALITY.) By default, the ordinal column is called ordinality, but a different column name can be assigned to it using an AS clause.

The special table function UNNEST may be called with any number of array parameters, and it returns a corresponding number of columns, as if UNNEST (Section 9.18) had been called on each parameter separately and combined using the ROWS FROM construct.

UNNEST( array_expression [, ... ] ) [WITH ORDINALITY] [[AS] table_alias [(column_alias [, ... ])]]

If no table_alias is specified, the function name is used as the table name; in the case of a ROWS FROM() construct, the first function's name is used.

If column aliases are not supplied, then for a function returning a base data type, the column name is also the same as the function name. For a function returning a composite type, the result columns get the names of the individual attributes of the type.

Some examples:

CREATE TABLE foo (fooid int, foosubid int, fooname text);

CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

SELECT * FROM getfoo(1) AS t1;

SELECT * FROM foo
    WHERE foosubid IN (
                        SELECT foosubid
                        FROM getfoo(foo.fooid) z
                        WHERE z.fooid = foo.fooid
                      );

CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);

SELECT * FROM vw_getfoo;

In some cases it is useful to define table functions that can return different column sets depending on how they are invoked. To support this, the table function can be declared as returning the pseudo-type record. When such a function is used in a query, the expected row structure must be specified in the query itself, so that the system can know how to parse and plan the query. This syntax looks like:

function_call [AS] alias (column_definition [, ... ])
function_call AS [alias] (column_definition [, ... ])
ROWS FROM( ... function_call AS (column_definition [, ... ]) [, ... ] )

When not using the ROWS FROM() syntax, the column_definition list replaces the column alias list that could otherwise be attached to the FROM item; the names in the column definitions serve as column aliases. When using the ROWS FROM() syntax, a column_definition list can be attached to each member function separately; or if there is only one member function and no WITH ORDINALITY clause, a column_definition list can be written in place of a column alias list following ROWS FROM().

Consider this example:

SELECT *
    FROM dblink('dbname=mydb', 'SELECT proname, prosrc FROM pg_proc')
      AS t1(proname name, prosrc text)
    WHERE proname LIKE 'bytea%';

The dblink function (part of the dblink module) executes a remote query. It is declared to return record since it might be used for any kind of query. The actual column set must be specified in the calling query so that the parser knows, for example, what * should expand to.

7.2.1.5. LATERAL Subqueries

Subqueries appearing in FROM can be preceded by the key word LATERAL. This allows them to reference columns provided by preceding FROM items. (Without LATERAL, each subquery is evaluated independently and so cannot cross-reference any other FROM item.)

Table functions appearing in FROM can also be preceded by the key word LATERAL, but for functions the key word is optional; the function's arguments can contain references to columns provided by preceding FROM items in any case.

A LATERAL item can appear at top level in the FROM list, or within a JOIN tree. In the latter case it can also refer to any items that are on the left-hand side of a JOIN that it is on the right-hand side of.

When a FROM item contains LATERAL cross-references, evaluation proceeds as follows: for each row of the FROM item providing the cross-referenced column(s), or set of rows of multiple FROM items providing the columns, the LATERAL item is evaluated using that row or row set's values of the columns. The resulting row(s) are joined as usual with the rows they were computed from. This is repeated for each row or set of rows from the column source table(s).

A trivial example of LATERAL is

SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar.id = foo.bar_id) ss;

This is not especially useful since it has exactly the same result as the more conventional

SELECT * FROM foo, bar WHERE bar.id = foo.bar_id;

LATERAL is primarily useful when the cross-referenced column is necessary for computing the row(s) to be joined. A common application is providing an argument value for a set-returning function. For example, supposing that vertices(polygon) returns the set of vertices of a polygon, we could identify close-together vertices of polygons stored in a table with:

SELECT p1.id, p2.id, v1, v2
FROM polygons p1, polygons p2,
     LATERAL vertices(p1.poly) v1,
     LATERAL vertices(p2.poly) v2
WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;

This query could also be written

SELECT p1.id, p2.id, v1, v2
FROM polygons p1 CROSS JOIN LATERAL vertices(p1.poly) v1,
     polygons p2 CROSS JOIN LATERAL vertices(p2.poly) v2
WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;

or in several other equivalent formulations. (As already mentioned, the LATERAL key word is unnecessary in this example, but we use it for clarity.)

It is often particularly handy to LEFT JOIN to a LATERAL subquery, so that source rows will appear in the result even if the LATERAL subquery produces no rows for them. For example, if get_product_names() returns the names of products made by a manufacturer, but some manufacturers in our table currently produce no products, we could find out which ones those are like this:

SELECT m.name
FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true
WHERE pname IS NULL;

7.2.2. The WHERE Clause

The syntax of the WHERE Clause is

WHERE search_condition

where search_condition is any value expression (see Section 4.2) that returns a value of type boolean.

After the processing of the FROM clause is done, each row of the derived virtual table is checked against the search condition. If the result of the condition is true, the row is kept in the output table, otherwise (i.e., if the result is false or null) it is discarded. The search condition typically references at least one column of the table generated in the FROMclause; this is not required, but otherwise the WHERE clause will be fairly useless.

Note

The join condition of an inner join can be written either in the WHEREclause or in the JOIN clause. For example, these table expressions are equivalent:

FROM a, b WHERE a.id = b.id AND b.val > 5

and:

FROM a INNER JOIN b ON (a.id = b.id) WHERE b.val > 5

or perhaps even:

FROM a NATURAL JOIN b WHERE b.val > 5

Which one of these you use is mainly a matter of style. The JOIN syntax in the FROM clause is probably not as portable to other SQL database management systems, even though it is in the SQL standard. For outer joins there is no choice: they must be done in the FROM clause. The ON or USING clause of an outer join is not equivalent to a WHERE condition, because it results in the addition of rows (for unmatched input rows) as well as the removal of rows in the final result.

Here are some examples of WHERE clauses:

SELECT ... FROM fdt WHERE c1 > 5

SELECT ... FROM fdt WHERE c1 IN (1, 2, 3)

SELECT ... FROM fdt WHERE c1 IN (SELECT c1 FROM t2)

SELECT ... FROM fdt WHERE c1 IN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10)

SELECT ... FROM fdt WHERE c1 BETWEEN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10) AND 100

SELECT ... FROM fdt WHERE EXISTS (SELECT c1 FROM t2 WHERE c2 > fdt.c1)

fdt is the table derived in the FROM clause. Rows that do not meet the search condition of the WHERE clause are eliminated from fdt. Notice the use of scalar subqueries as value expressions. Just like any other query, the subqueries can employ complex table expressions. Notice also how fdt is referenced in the subqueries. Qualifying c1 as fdt.c1 is only necessary if c1 is also the name of a column in the derived input table of the subquery. But qualifying the column name adds clarity even when it is not needed. This example shows how the column naming scope of an outer query extends into its inner queries.

7.2.3. The GROUP BY and HAVING Clauses

After passing the WHERE filter, the derived input table might be subject to grouping, using the GROUP BY clause, and elimination of group rows using the HAVING clause.

SELECT select_list
    FROM ...
    [WHERE ...]
    GROUP BY grouping_column_reference [, grouping_column_reference]...

The GROUP BY Clause is used to group together those rows in a table that have the same values in all the columns listed. The order in which the columns are listed does not matter. The effect is to combine each set of rows having common values into one group row that represents all rows in the group. This is done to eliminate redundancy in the output and/or compute aggregates that apply to these groups. For instance:

=> SELECT * FROM test1;
 x | y
---+---
 a | 3
 c | 2
 b | 5
 a | 1
(4 rows)

=> SELECT x FROM test1 GROUP BY x;
 x
---
 a
 b
 c
(3 rows)

In the second query, we could not have written SELECT * FROM test1 GROUP BY x, because there is no single value for the column y that could be associated with each group. The grouped-by columns can be referenced in the select list since they have a single value in each group.

In general, if a table is grouped, columns that are not listed in GROUP BY cannot be referenced except in aggregate expressions. An example with aggregate expressions is:

=> SELECT x, sum(y) FROM test1 GROUP BY x;
 x | sum
---+-----
 a |   4
 b |   5
 c |   2
(3 rows)

Here sum is an aggregate function that computes a single value over the entire group. More information about the available aggregate functions can be found in Section 9.20.

Tip

Grouping without aggregate expressions effectively calculates the set of distinct values in a column. This can also be achieved using the DISTINCTclause (see Section 7.3.3).

Here is another example: it calculates the total sales for each product (rather than the total sales of all products):

SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
    FROM products p LEFT JOIN sales s USING (product_id)
    GROUP BY product_id, p.name, p.price;

In this example, the columns product_id, p.name, and p.price must be in the GROUP BY clause since they are referenced in the query select list (but see below). The columns.units does not have to be in the GROUP BY list since it is only used in an aggregate expression (sum(...)), which represents the sales of a product. For each product, the query returns a summary row about all sales of the product.

If the products table is set up so that, say, product_id is the primary key, then it would be enough to group by product_id in the above example, since name and price would be functionally dependent on the product ID, and so there would be no ambiguity about which name and price value to return for each product ID group.

In strict SQL, GROUP BY can only group by columns of the source table but PostgreSQL extends this to also allow GROUP BY to group by columns in the select list. Grouping by value expressions instead of simple column names is also allowed.

If a table has been grouped using GROUP BY, but only certain groups are of interest, the HAVING clause can be used, much like a WHERE clause, to eliminate groups from the result. The syntax is:

SELECT select_list FROM ... [WHERE ...] GROUP BY ... HAVING boolean_expression

Expressions in the HAVING clause can refer both to grouped expressions and to ungrouped expressions (which necessarily involve an aggregate function).

Example:

=> SELECT x, sum(y) FROM test1 GROUP BY x HAVING sum(y) > 3;
 x | sum
---+-----
 a |   4
 b |   5
(2 rows)

=> SELECT x, sum(y) FROM test1 GROUP BY x HAVING x < 'c';
 x | sum
---+-----
 a |   4
 b |   5
(2 rows)

Again, a more realistic example:

SELECT product_id, p.name, (sum(s.units) * (p.price - p.cost)) AS profit
    FROM products p LEFT JOIN sales s USING (product_id)
    WHERE s.date > CURRENT_DATE - INTERVAL '4 weeks'
    GROUP BY product_id, p.name, p.price, p.cost
    HAVING sum(p.price * s.units) > 5000;

In the example above, the WHERE clause is selecting rows by a column that is not grouped (the expression is only true for sales during the last four weeks), while the HAVINGclause restricts the output to groups with total gross sales over 5000. Note that the aggregate expressions do not necessarily need to be the same in all parts of the query.

If a query contains aggregate function calls, but no GROUP BY clause, grouping still occurs: the result is a single group row (or perhaps no rows at all, if the single row is then eliminated by HAVING). The same is true if it contains a HAVING clause, even without any aggregate function calls or GROUP BY clause.

7.2.4. GROUPING SETS, CUBE, and ROLLUP

More complex grouping operations than those described above are possible using the concept of grouping sets. The data selected by the FROM and WHERE clauses is grouped separately by each specified grouping set, aggregates computed for each group just as for simple GROUP BY clauses, and then the results returned. For example:

=> SELECT * FROM items_sold;
 brand | size | sales
-------+------+-------
 Foo   | L    |  10
 Foo   | M    |  20
 Bar   | M    |  15
 Bar   | L    |  5
(4 rows)

=> SELECT brand, size, sum(sales) FROM items_sold GROUP BY GROUPING SETS ((brand), (size), ());
 brand | size | sum
-------+------+-----
 Foo   |      |  30
 Bar   |      |  20
       | L    |  15
       | M    |  35
       |      |  50
(5 rows)

Each sublist of GROUPING SETS may specify zero or more columns or expressions and is interpreted the same way as though it were directly in the GROUP BY clause. An empty grouping set means that all rows are aggregated down to a single group (which is output even if no input rows were present), as described above for the case of aggregate functions with no GROUP BY clause.

References to the grouping columns or expressions are replaced by null values in result rows for grouping sets in which those columns do not appear. To distinguish which grouping a particular output row resulted from, see Table 9.56.

A shorthand notation is provided for specifying two common types of grouping set. A clause of the form

ROLLUP ( e1, e2, e3, ... )

represents the given list of expressions and all prefixes of the list including the empty list; thus it is equivalent to

GROUPING SETS (
    ( e1, e2, e3, ... ),
    ...
    ( e1, e2 ),
    ( e1 ),
    ( )
)

This is commonly used for analysis over hierarchical data; e.g. total salary by department, division, and company-wide total.

A clause of the form

CUBE ( e1, e2, ... )

represents the given list and all of its possible subsets (i.e. the power set). Thus

CUBE ( a, b, c )

is equivalent to

GROUPING SETS (
    ( a, b, c ),
    ( a, b    ),
    ( a,    c ),
    ( a       ),
    (    b, c ),
    (    b    ),
    (       c ),
    (         )
)

The individual elements of a CUBE or ROLLUP clause may be either individual expressions, or sublists of elements in parentheses. In the latter case, the sublists are treated as single units for the purposes of generating the individual grouping sets. For example:

CUBE ( (a, b), (c, d) )

is equivalent to

GROUPING SETS (
    ( a, b, c, d ),
    ( a, b       ),
    (       c, d ),
    (            )
)

and

ROLLUP ( a, (b, c), d )

is equivalent to

GROUPING SETS (
    ( a, b, c, d ),
    ( a, b, c    ),
    ( a          ),
    (            )
)

The CUBE and ROLLUP constructs can be used either directly in the GROUP BY clause, or nested inside a GROUPING SETS clause. If one GROUPING SETS clause is nested inside another, the effect is the same as if all the elements of the inner clause had been written directly in the outer clause.

If multiple grouping items are specified in a single GROUP BY clause, then the final list of grouping sets is the cross product of the individual items. For example:

GROUP BY a, CUBE (b, c), GROUPING SETS ((d), (e))

is equivalent to

GROUP BY GROUPING SETS (
    (a, b, c, d), (a, b, c, e),
    (a, b, d),    (a, b, e),
    (a, c, d),    (a, c, e),
    (a, d),       (a, e)
)

Note

The construct (a, b) is normally recognized in expressions as a row constructor. Within the GROUP BY clause, this does not apply at the top levels of expressions, and (a, b) is parsed as a list of expressions as described above. If for some reason you need a row constructor in a grouping expression, use ROW(a, b).

7.2.5. Window Function Processing

If the query contains any window functions (see Section 3.5, Section 9.21 and Section 4.2.8), these functions are evaluated after any grouping, aggregation, and HAVING filtering is performed. That is, if the query uses any aggregates, GROUP BY, or HAVING, then the rows seen by the window functions are the group rows instead of the original table rows from FROM/WHERE.

When multiple window functions are used, all the window functions having syntactically equivalent PARTITION BY and ORDER BY clauses in their window definitions are guaranteed to be evaluated in a single pass over the data. Therefore they will see the same sort ordering, even if the ORDER BY does not uniquely determine an ordering. However, no guarantees are made about the evaluation of functions having different PARTITION BY or ORDER BY specifications. (In such cases a sort step is typically required between the passes of window function evaluations, and the sort is not guaranteed to preserve ordering of rows that its ORDER BY sees as equivalent.)

Currently, window functions always require presorted data, and so the query output will be ordered according to one or another of the window functions' PARTITION BY/ORDER BYclauses. It is not recommended to rely on this, however. Use an explicit top-level ORDER BY clause if you want to be sure the results are sorted in a particular way.

在查詢產生了一個輸出資料表（處理了資料列表之後）之後，可以對其資料列進行排序。如果未選擇排序，則資料列將以未指定的順序回傳。在這種情況下的實際順序將取決於資料掃描和交叉查詢類型以及磁碟上的順序，但不能依賴它。只有明確選擇了排序方式，才能保證特定的輸出排序。

以 ORDER BY 子句指定排序順序：

排序表示式可以在查詢的資料列表中有效的任何表示式。 一個例子是：

當指定多個表示式時，後面的表示式用於前面表示式都相同的資料進行排序。每個表示式可以跟隨一個選擇性的 ASC 或 DESC 關鍵字來設定排序方向為升冪或降冪。 ASC 排序是預設的選項。升冪首先放置較小的值，其中「較小」是根據「<」運算元定義的。 同樣，降冪也是由「>」運算元決定的。

NULLS FIRST 和 NULLS LAST 選項可用於確定在排序順序中是否出現空值出現在非空值之前或之後。預設情況下，空值排序大於任何非空值；也就是 NULLS FIRST 是 DESC 選項的預設值，否則就是 NULLS LAST。

請注意，排序選項是針對每個排序欄位獨立考慮的。例如 ORDER BY x, y DESC 是指 ORDER BY x ASC, y DESC，它與 ORDER BY x DESC, y DESC 不同。

排序表示式也可以是輸出欄位的欄位標籤或編號，如下所示：

兩者都按第一個輸出欄位排序。請注意，輸出欄位名稱必須獨立，也就是說，不能在表示式中使用 - 例如，這樣是不正確的：

這種限制是為了減少歧義。 即使 ORDER BY 項目是一個簡單的名字，可以匹配輸出欄位名稱或者資料表表示式中的一項，這仍然是會混淆的。在這種情況下請使用輸出欄位。如果您使用 AS 來重新命名輸出欄位以匹配其他資料表欄位的名稱，只會導致混淆。

可以將 ORDER BY 應用於 UNION、INTERSECT 或 EXCEPT 組合的結果，但在這種情況下，只允許按輸出欄位名稱或數字進行排序，而不能使用表示式進行排序。

LIMIT 和 OFFSET 允許你只回傳由查詢生成的一部分資料列：

SELECT select_list
    FROM table_expression
    [ ORDER BY ... ]
    [ LIMIT { number | ALL } ] [ OFFSET number]

如果給了一個限制的數量，那麼只有那個數目的資料列會回傳（如果查詢本身產生較少的資料列，則可能會少一些）。LIMIT ALL 與省略 LIMIT 子句相同，也如同 LIMIT 的參數為 NULL。

OFFSET 指的是在開始回傳資料列之前跳過那麼多少資料列。OFFSET 0 與忽略 OFFSET 子句相同，就像使用 NULL 參數的 OFFSET 一樣。

如果同時出現 OFFSET 和 LIMIT，則在開始計算回傳的LIMIT 資料列之前，先跳過 OFFSET 數量的資料列。

使用 LIMIT 時，運用 ORDER BY 子句將結果資料列限制為唯一順序非常重要。否則，你會得到一個不可預知的查詢資料列的子集。你可能會查詢第十到第二十個資料列，但是第十到第二十個資料列是按什麼順序排列的？次序是未知的，除非你指定 ORDER BY。

查詢最佳化在產生查詢計劃時會將 LIMIT 考慮在內，所以根據你給的 LIMIT 和 OFFSET，你很可能會得到不同的計劃（產生不同的資料列順序）。因此，使用不同的 LIMIT / OFFSET 值來選擇查詢結果的不同子集將導致不一致的結果，除非使用 ORDER BY 強制執行可預測的結果排序。這不是一個錯誤；這是一種事實上的結果，即 SQL 不保證以任何特定順序傳遞查詢的結果，除非使用 ORDER BY 來約束順序。

由 OFFSET 子句跳過的資料列仍然需要在伺服器內計算。因此一個大的 OFFSET 可能是低效率的。

VALUES 提供了一種產生「靜態資料表」的方法，可以在查詢中使用，而不必實際創建和寫入磁碟上的資料表。其語法是

VALUES ( expression [, ...] ) [, ...]

每個括號內的表示式列表在資料表中生成一個資料列。列表必須具有相同數量的元素（即資料表中的欄位數），並且每個列表中的對應條目必須具有兼容的資料型別。 分配給結果中每個欄位的實際資料型別，使用與 UNION 相同的規則來給定（請參閱第 10.5 節）。

如下範例所示：

VALUES (1, 'one'), (2, 'two'), (3, 'three');

將回傳一個兩個欄位三個資料列的資料表。這實際上相當於：

SELECT 1 AS column1, 'one' AS column2
UNION ALL
SELECT 2, 'two'
UNION ALL
SELECT 3, 'three';

預設情況下，PostgreSQL 會將名稱 column1、column2 等分配給 VALUES 資料表的欄位。欄位名稱並不是由 SQL 標準規定的，不同的資料庫系統會以不同的方式賦予，所以通常以資料表別名列表覆寫預設名稱會比較好，如下所示：

=> SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t (num,letter);
 num | letter
-----+--------
   1 | one
   2 | two
   3 | three
(3 rows)

在語法上，VALUES 接在表示式列表之後被視為等同於：

SELECT select_list FROM table_expression

並可以出現在任何一個 SELECT 可以使用的地方。例如，你可以將其用作為 UNION 的一部分，或者為其增加排序規則（ORDER BY、LIMIT 和 OFFSET）。在 INSERT 命令中，VALUES 最常來作為資料源，其次最常在子查詢。

關於更多訊息，請參閱 VALUES。

WITH provides a way to write auxiliary statements for use in a larger query. These statements, which are often referred to as Common Table Expressions or CTEs, can be thought of as defining temporary tables that exist just for one query. Each auxiliary statement in a WITH clause can be a SELECT, INSERT, UPDATE, or DELETE; and the WITH clause itself is attached to a primary statement that can also be a SELECT, INSERT, UPDATE, or DELETE.

7.8.1. SELECT in WITH

The basic value of SELECT in WITH is to break down complicated queries into simpler parts. An example is:

WITH regional_sales AS (
    SELECT region, SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
), top_regions AS (
    SELECT region
    FROM regional_sales
    WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

which displays per-product sales totals in only the top sales regions. The WITH clause defines two auxiliary statements named regional_sales and top_regions, where the output of regional_sales is used in top_regions and the output of top_regions is used in the primary SELECT query. This example could have been written without WITH, but we'd have needed two levels of nested sub-SELECTs. It's a bit easier to follow this way.

The optional RECURSIVE modifier changes WITH from a mere syntactic convenience into a feature that accomplishes things not otherwise possible in standard SQL. Using RECURSIVE, a WITH query can refer to its own output. A very simple example is this query to sum the integers from 1 through 100:

WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;

The general form of a recursive WITH query is always a non-recursive term, then UNION (or UNION ALL), then a recursive term, where only the recursive term can contain a reference to the query's own output. Such a query is executed as follows:

Recursive Query Evaluation

1. Evaluate the non-recursive term. For UNION (but not UNION ALL), discard duplicate rows. Include all remaining rows in the result of the recursive query, and also place them in a temporary working table.

2. So long as the working table is not empty, repeat these steps:

   1. Evaluate the recursive term, substituting the current contents of the working table for the recursive self-reference. For UNION (but not UNION ALL), discard duplicate rows and rows that duplicate any previous result row. Include all remaining rows in the result of the recursive query, and also place them in a temporary intermediate table.

   2. Replace the contents of the working table with the contents of the intermediate table, then empty the intermediate table.

Note

Strictly speaking, this process is iteration not recursion, but RECURSIVE is the terminology chosen by the SQL standards committee.

In the example above, the working table has just a single row in each step, and it takes on the values from 1 through 100 in successive steps. In the 100th step, there is no output because of the WHERE clause, and so the query terminates.

Recursive queries are typically used to deal with hierarchical or tree-structured data. A useful example is this query to find all the direct and indirect sub-parts of a product, given only a table that shows immediate inclusions:

WITH RECURSIVE included_parts(sub_part, part, quantity) AS (
    SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'
  UNION ALL
    SELECT p.sub_part, p.part, p.quantity
    FROM included_parts pr, parts p
    WHERE p.part = pr.sub_part
)
SELECT sub_part, SUM(quantity) as total_quantity
FROM included_parts
GROUP BY sub_part

When working with recursive queries it is important to be sure that the recursive part of the query will eventually return no tuples, or else the query will loop indefinitely. Sometimes, using UNION instead of UNION ALL can accomplish this by discarding rows that duplicate previous output rows. However, often a cycle does not involve output rows that are completely duplicate: it may be necessary to check just one or a few fields to see if the same point has been reached before. The standard method for handling such situations is to compute an array of the already-visited values. For example, consider the following query that searches a table graph using a link field:

WITH RECURSIVE search_graph(id, link, data, depth) AS (
    SELECT g.id, g.link, g.data, 1
    FROM graph g
  UNION ALL
    SELECT g.id, g.link, g.data, sg.depth + 1
    FROM graph g, search_graph sg
    WHERE g.id = sg.link
)
SELECT * FROM search_graph;

This query will loop if the link relationships contain cycles. Because we require a “depth” output, just changing UNION ALL to UNION would not eliminate the looping. Instead we need to recognize whether we have reached the same row again while following a particular path of links. We add two columns path and cycle to the loop-prone query:

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
    SELECT g.id, g.link, g.data, 1,
      ARRAY[g.id],
      false
    FROM graph g
  UNION ALL
    SELECT g.id, g.link, g.data, sg.depth + 1,
      path || g.id,
      g.id = ANY(path)
    FROM graph g, search_graph sg
    WHERE g.id = sg.link AND NOT cycle
)
SELECT * FROM search_graph;

Aside from preventing cycles, the array value is often useful in its own right as representing the “path” taken to reach any particular row.

In the general case where more than one field needs to be checked to recognize a cycle, use an array of rows. For example, if we needed to compare fields f1 and f2:

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
    SELECT g.id, g.link, g.data, 1,
      ARRAY[ROW(g.f1, g.f2)],
      false
    FROM graph g
  UNION ALL
    SELECT g.id, g.link, g.data, sg.depth + 1,
      path || ROW(g.f1, g.f2),
      ROW(g.f1, g.f2) = ANY(path)
    FROM graph g, search_graph sg
    WHERE g.id = sg.link AND NOT cycle
)
SELECT * FROM search_graph;

Tip

Omit the ROW() syntax in the common case where only one field needs to be checked to recognize a cycle. This allows a simple array rather than a composite-type array to be used, gaining efficiency.

Tip

The recursive query evaluation algorithm produces its output in breadth-first search order. You can display the results in depth-first search order by making the outer query ORDER BY a “path” column constructed in this way.

A helpful trick for testing queries when you are not certain if they might loop is to place a LIMIT in the parent query. For example, this query would loop forever without the LIMIT:

WITH RECURSIVE t(n) AS (
    SELECT 1
  UNION ALL
    SELECT n+1 FROM t
)
SELECT n FROM t LIMIT 100;

This works because PostgreSQL's implementation evaluates only as many rows of a WITH query as are actually fetched by the parent query. Using this trick in production is not recommended, because other systems might work differently. Also, it usually won't work if you make the outer query sort the recursive query's results or join them to some other table, because in such cases the outer query will usually try to fetch all of the WITH query's output anyway.

A useful property of WITH queries is that they are normally evaluated only once per execution of the parent query, even if they are referred to more than once by the parent query or sibling WITH queries. Thus, expensive calculations that are needed in multiple places can be placed within a WITH query to avoid redundant work. Another possible application is to prevent unwanted multiple evaluations of functions with side-effects. However, the other side of this coin is that the optimizer is not able to push restrictions from the parent query down into a multiply-referenced WITH query, since that might affect all uses of the WITH query's output when it should affect only one. The multiply-referenced WITH query will be evaluated as written, without suppression of rows that the parent query might discard afterwards. (But, as mentioned above, evaluation might stop early if the reference(s) to the query demand only a limited number of rows.)

但是，如果 WITH 查詢是非遞迴且不會在執行中變動的（即它是一個不包含 volatile 函數的 SELECT），則可以將其合併到父查詢之中，從而可以對兩個查詢等級進行聯合語法最佳化。預設情況下，如果父查詢僅引用一次 WITH 語句，而不是多次引用 WITH 一次查詢，則會觸發這個機制。您可以透過指定 MATERIALIZED 強制執行 WITH 查詢的單獨計算，或者透過指定 NOT MATERIALIZED 強制執行將其合併到父查詢中來覆蓋該查詢計畫。後面一種選擇可能會冒著重複計算 WITH 查詢的風險，但如果 WITH 查詢的每次使用只需要 WITH 查詢全部輸出的一小部分，那麼它仍然可以節省成本。

A simple example of these rules is

WITH w AS (
    SELECT * FROM big_table
)
SELECT * FROM w WHERE key = 123;

This WITH query will be folded, producing the same execution plan as

SELECT * FROM big_table WHERE key = 123;

In particular, if there's an index on key, it will probably be used to fetch just the rows having key = 123. On the other hand, in

WITH w AS (
    SELECT * FROM big_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
WHERE w2.key = 123;

the WITH query will be materialized, producing a temporary copy of big_table that is then joined with itself — without benefit of any index. This query will be executed much more efficiently if written as

WITH w AS NOT MATERIALIZED (
    SELECT * FROM big_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
WHERE w2.key = 123;

so that the parent query's restrictions can be applied directly to scans of big_table.

An example where NOT MATERIALIZED could be undesirable is

WITH w AS (
    SELECT key, very_expensive_function(val) as f FROM some_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.f = w2.f;

Here, materialization of the WITH query ensures that very_expensive_function is evaluated only once per table row, not twice.

The examples above only show WITH being used with SELECT, but it can be attached in the same way to INSERT, UPDATE, or DELETE. In each case it effectively provides temporary table(s) that can be referred to in the main command.

7.8.2. Data-Modifying Statements in WITH

You can use data-modifying statements (INSERT, UPDATE, or DELETE) in WITH. This allows you to perform several different operations in the same query. An example is: