這章中說明 SQL 的使用語法。從這裡建立後續章節所需的理解基礎,然後進一步瞭解 SQL 如何使用去定義及修改資料。
我們也建議已經熟悉 SQL 語法的使用者,仔細地閱讀本章,因為這裡包含了一些有別於其他 SQL 資料庫或專屬於 PostgreSQL 的規則和觀念。
PostgreSQL 允許函數呼叫的時候,使用編號或名稱記號。名稱記號特別好用在於有很多參數的時候,因為它能讓參數與實際的引數有更明確的關連,也更有信賴感。使用編號記號的話,函數呼叫就會依其宣告時的參數次序給予編號;而使用名稱記號的話,參數就會依宣告時的名稱配對,不需要次序對應。
不論哪一種記號方式,如果在宣告時有設定預設值的話,那就不一定要在呼叫時設定其值。不過這點對名稱記號特別好用,因為任何參數的組合都可以省略,而編號記號時就只有從最右邊的參數開始省略。
PostgreSQL 也支援混合式的記號方式,也就是同時使用編號,也使用名稱。在這個例子中,編號的參數會先使用,然後名稱的參數在其之後使用。
接下來的例子,將會描繪所有三種記號方式,都使用下列的函數定義:
函數 concat_lower_or_upper 有兩個必要的參數,a 與 b。然後有一個參數是選擇性的,uppercase 的預設值是 false。參數 a 和 b 的文字會被連結起來,然後依 uppercase 的設定,強制轉換為大寫或小寫字母。這個函數定義的其他部份在這裡並不重要(詳情請參閱第 37 章)。
編號記號是 PostgreSQL 傳統的參數呼叫方式,如下所示:
所有的參數會依序指定。結果是全大寫,因為 uppercase 設定為 true。另一個例子如下:
這裡的 uppercase 省略了,所以會使用預設值 false,結果就以小寫字母輸出。在編號的記號方式時,參數的省略是由右至左,只有具有預設值的部份才能省略。
使用名稱作為參數記號方式的話,每一個參數名使用「=>」來指定其所代表的表示式,如下所示:
In named notation, each argument's name is specified using=>
to separate it from the argument expression. For example:
再一次省略 uppercase,所以它自動設為 false。使用名稱記號的一項好處就是參數不用固定次數,如下例所示:
有一種比較舊的語法是使用「:=」,因為相容性而保留下來:
混用記號指的就是混合使用編號及名稱來設定參數。然而,如前所述,名稱參數不能先於編號參數。例如:
在上面的查詢中,a 和 b 兩個參數以編號指定,而 uppercase 就以名稱指定。在本例子,只有增加一點點內容而已。使用比較複雜的函數時,會有許多參數設定了預設值,以名稱或混合的方式來設定參數,可以節省許多撰寫的程式碼,也可以減少出錯的可能性。
名稱記號和混用記號目前不能用於彙總函數的呼叫(但如果是用於窗函數是就可以)。
參數表示式用在許多不同的方面,像是 SELECT 指令中的回傳列表;在 INSERT 或 UPDATE 指令中指定欄位的新值;又或是在一些命令中,指出搜尋的條件等。參數表示式的結果,有時候會被稱作 scalar,以有別於表格表示式(就是一個表格)的結果。參數表示式也可以稱作 scalar expressions(賦值表示式),甚或簡化為 expressions (表示式)。表示式的語法容許其值為各種運算的單一結果,如數學、邏輯、集合、或其他運算。
參數表示式可以是下列的其中一種形態:
常數或文字內容
欄位的引用
函數參數的引用,在函數裡或預備指令(prepared statement)中
子參數表示式
欄位選擇表示式
運算子宣告
函數呼叫
彙總表示式
窗函數呼叫
型別轉換
校對轉換(collation expression)
賦值子查詢(scalar subquery)
陣列建構式
列建構式
其他被括號括住的參數表示式(用於群組子表示式和強制調整運算優先權)
除了這個列表之外,還有一些建構式也會應用到表示式,但並沒有特別定義語法規則。一般來說,他們會包含函數或運算子的操作,在第 9 章中會有適當的說明。其中有一個例子便是 IS NULL 字句。
我們已經在 4.1.2 節中討論過常數了,所以接下來就從常數以下的項目繼續說明。
要引要一個欄位的話,請使用下列的形式:
「correlation」(所屬名稱)是其所屬表格的名稱(也可能需要包含結構名),或是表格的別名(在 FROM 子句中所定義的)。所屬名稱和分隔用的句點是可以省略的,如果欄位名稱在目前查詢中的所有表格中是唯一的話。(參閱第 7 章)
函數參數的引用,用來指定一個不在該 SQL 指令中的值。參數是使用在 SQL 函數定義或預備查詢之中。有一些用戶端函式庫也支援將資料數值與 SQL 指令分離,在這種情境下,參數就會用來指向外部的資料數值。參數引用的形式如下:
舉個例子,有一個函數 dept 的宣告如下:
這裡的 $1 指的是函數被呼叫時的第 1 個輸入參數:
如果表示式要產生陣列的結果的話,指定陣列中某個元素,請使用:
或是要取得陣列中多個相隣的元素,請使用:
每一個「subscript」本身都是一個表示式,必須要產生一個整數值。
一般來說,陣列表示式必須被括號起來,但如果該表示式只是一個欄位或參數的引用的話,那麼括號可以省略。然後,多個子參數表示式可以連在一起使用,當你需要陣列表達多維度的概念時。舉例如下:
在最後一個例子中,括號是必須的。關於陣列,在 8.15 節有更多說明。
如果一個表示式產生了複合性的型別(列型別),那麼要指定其中的某個欄位時,請使用:
一般來說,列的表示式必須被括號起來,但如果該表示式只是一個欄位或參數的引用的話,那麼括號可以省略。舉例如下:
(然而,有限制的欄位引用,實際上就是一種欄位選擇語法的特列。)有一種重要的特例是從某個複合型別的表格欄位中取其子欄位的值:
在這裡,括號是必要的,以表示 compositecol 是一個欄位名稱,但不是表格名稱。而在第二個例子中,mytable 是表格名稱,而非結構名稱。
你可以取得複合資料的所有欄位值,使用「.*」:
這個記號在不同的地方有不同的用法,請參閱 8.16.5 節的說明。
有三種用來進行運算子宣告的語法:
運算子記號的語法規則依 4.1.3 節的說明,或是關鍵字 AND、OR、和 NOT,又或是如下形式的限定運算子名稱:
哪些特定的運算子的使用與運算方式,端看系統與使用者如何定義。在第 9 章中會說明內建的運算子詳情。
函數呼叫的語法是,函數的名稱(可能還會加上結構名)接著一連串用括號括起來的參數列表:
舉個例子,下面的函數呼叫可以計算 2 的平方根:
內建函數在第 9 章說明,其他的函數可由使用者自訂。
參數可以是選擇性的附加名稱,請參閱 4.3 節的內容。
函數如果只有一個參數,而又是複合型別的話,就稱作使用了欄位選擇語法;反過來說,欄位選擇語法也可以寫成函數的形式。這是因為 col(table) 和 table.col 是可以互換的。這並非標準 SQL,但 PostgreSQL 支援了,因為這使得函數的使用可以模擬「計算欄位」(computed fields)。更多資訊請參閱 8.16.5 節。
彙總表示式用在查詢時,過濾資料進行彙總函數計算的應用。彙總函數壓縮了大量資料輸入成為一個單一的輸出值,例如加總或平均數。彙總表示式的語法可以是下列其中之一:
這裡的 agregate_name 是預先就定義好的(可能還需要加上結構名稱),表示式可以是任何的函數形態,但不能包含彙總函數或窗函數。而 order_by_clause 和 filter_clause 後續進行說明。
第一種形式的彙總表示式用於每次輸入一列的情況;第二種形式和第一種相同,當 ALL 是預設的時候;第三種形式彙總不重覆的資料(或在多種表示式的時候,取不重覆的集合);第四種形式也是每次輸入一列,但沒有限定輸入條件,通常是用於 count(*);最後一種形式用於有次序的彙總函數,稍後說明。
大多數的彙總函數會忽略空值,所以如果表示式計算的結果是空值的話,就會忽略不計。這樣的假設除非有特別設定,對所有內建的函數都是如此。
舉例來說,count(*) 計算輸入列的個數,而 count(f1) 是計算輸入列中 f1 欄位非空值的個數,因為 count 會忽略空值;然而,count(distinct f1) 則是計算 f1 欄位不重覆又非空值的個數。
通常彙總函數在處理輸入資料時,都是未排序過的。在大多數的情況沒有關係,例如:min 最小值的計算,與其輸入的次序沒有關係。然而,還是有些彙總函數的結果,與其處理次序是有關連的,例如:array_agg 和 string_agg。ORDER BY 字句就可以達到此效果,其與一般查詢語法 ORDER BY 的用法相同,詳細說明在 7.5 節,除非該表示式無法輸出成欄位名稱或數字。舉例如下:
操作到多參數的彙總函數時,注意 ORDER BY 會處理過所有的彙總參數,例如:
但不能這樣寫:
這在語法上沒有不合法,但這表示一個單參數的彙總函數,使用了兩個排序的關鍵值(第二個完全沒用,因為它是常數)。
如果 DISTINCT 被加到 ORDER BY 子句裡的話,那麼所有的 ORDER BY 表示式都必須符合彙總函數的參數,也就是說,你不能使用不在 DISTINCT 列表中的表示式來排序。
在彙總函數中使用 DISTINCT 和 ORDER BY,都是 PostgreSQL 的延伸。
把 ORDER BY 放進彙總函數的參數列表中,就如同到目前為止的描述,用於排序輸入值,進行一般性的處理或統計彙總,而排序是選擇性的。有另一種類型的彙總函數稱作有次序彙總,它們就必須要有 ORDER BY 子句,通常就是因為這些函數的計算結果,只會對某些特定次序的資料產生效果。典型的有次序彙總例子,包含排名和累計百分比計算。對於有次序彙總計算,將 ORDER BY 字句寫進 WITHIN GROUP (...) 中,如同上述最後一個語法例子。在 ORDER BY 子句中的表示式會處理每一筆輸入資料,如同一般的彚總函數,然後將其依子句中的表示式計算並排序,最後再依序轉送給彙總函數處理。(這和非處理 WITHIN GROUP 中的 ORDER BY 不同,它們不會再轉送給彙總函數。)如果有在 WITHIN GROUP 之前的表示式的話,稱作直接參數,會和有 ORDER BY 的參數有區分。不像一般的彙總參數,直接參數只會被處理一次,而不是每一筆都一次。這意思是只有在 GROUP BY 中,這些變數才會被彙總處理。這樣的限制就如同直接參數不在彙總表示式之中一樣。直接參數一般用於累計分配,只有在每一次彙整完的值才有意義。直接參數可以是空值,在這個例子中,使用的是 (),而非 (*)。(PostgreSQL 兩種寫法都可以接受,但標準 SQL 只接受前者。)
有次序彙總查詢如下:
這裡包含了 50% 的累計,或是中間數累計,來源是表格 households 的 income 欄位。其中,0.5 是直接參數,它不影響百分累計彙整計算過程。
如果使用了 FILTER,那就只有符合 FILTER 子句條件的資料會被彙總處理,其他的資料都會被忽略掉。舉例來說:
預先內建的彙總函數將在 9.20 節中介紹,其他彙總函數可以由使用者自行設計。
彙總表示式只可以用於結果列表或 SELECT 中的 HAVING 子句。在其他子句中是被禁止的,像是 WHERE,因為這些子句邏輯上都是在彙總處理前就得處理資料。
當彙總表示式使用在子查詢(參閱 4.2.11 節及 9.22 節)中時,彙總計算就會一般性地處理子查詢中的資料。但如果該彙總計算的參數用到了外層的變數時,就會產生例外情況:彙整計算是屬於最接近的外層查詢,並且只處理該層的查詢資料。這個彙總表示式對整體而言,只是一個子查詢的引用,它會被視為一個常數的結果,限制它只會出現在 HAVING 子句的運算層次而已。
窗函數呼叫指的是使用類似彙總函數的使用方式,只是僅用於查詢中部份列的選擇上。和非窗函數不同的是,這並不會只輸出為單一列—每一列都仍然分開輸出。然而,窗函數也是處理了所有該列所屬群組的其他列(PARTITION BY),依其窗函數所定義的範圍。窗函數呼叫的方式可以是下列其中之一:
定義「窗」,請使用下列語法:
選擇性的 frame_clause 語法如下:
frame_start 及 frame_end 的語法如下:
在這裡的表示式(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 節。
型別轉換指定從一種資料型別轉換為另一種資料型別。PostgreSQL 接受兩種用於型別轉換的等效語法:
CAST 語法符合 SQL 標準;帶「::」的語法是 PostgreSQL 既有的用法。
當強制轉換應用於已知型別的值表示式時,它表示執行時型別轉換。只有定義了合適的型別轉換操作,操作才能成功。請注意,這與使用帶常數的強制轉換略有不同,如 4.1.2.7 節所示。應用於未經修飾的字串文字的強制轉換表示將型別初始分配給文字常數,因此對於任何型別(如果字串文字的內容都是資料型別的可接受輸入語法)都會成功。
如果對於值表示式必須產生的型別沒有歧義(例如,當它被分配給資料表欄位),通常可以省略顯式的型別轉換;系統將在這種情況下自動套用型別轉換。但是,只有在系統目錄中標記為「可以隱式套用」的強制轉換才會執行自動強制轉換。其他強制轉換必須使用顯式強制轉換語法來使用。此限制旨在防止系統默默地套用令人意外的轉換。
也可以使用函數式語法來指定型別轉換:
但是,這僅適用於名稱也可以作為函數名稱使用的型別。例如,雙精度不能用這種方式,但等價的 float8 可以。而且,由於語法衝突,名稱間隔,時間和時間戳記只能使用雙引號才能用於這種方式。因此,使用類似功能的轉換語法會導致不一致,因此可能應該避免。
函數式語法實際上只是一個函數呼叫。當兩個標準轉換語法之一用於執行轉換時,它將在內部呼叫已註冊的函數來執行轉換。按照慣例,這些轉換函數與它們的輸出類型具有相同的名稱,因此「函數式語法」只不過是直接呼叫底層的轉換函數。顯然,這不是一個可移植式應用程序應該依賴的東西。有關更多詳情,請參閱 CREATE CAST。
COLLATE 子句用於覆蓋排序規則的表示式。它附加到所套用的表示式上:
排序規則是一種可以綱要限定識別指標。COLLATE 子句比運算子更緊密;必要時可以使用括號。
如果沒有明確指定排序規則,那麼資料庫系統會從表示式中涉及的欄位中衍生一個排序規則,或者如果表示式中未包含任何欄位,則預設為資料庫的預設排序規則。
COLLATE 子句的兩個常見用法是重寫 ORDER BY 子句中的排序順序,例如:
並覆蓋具有語言環境特性結果的函數或運算子呼叫的排序規則,例如:
請注意,在後者的情況下,COLLATE 子句附加到我們希望影響的運算子的輸入參數。 無論運算子或函數呼叫 COLLATE 子句的哪個參數被附加到哪個參數都沒有關係,因為運算子或函數套用的排序規則是透過考慮所有參數衍生的,並且顯式 COLLATE 子句將覆蓋所有其他排序規則參數。(然而,將不匹配的 COLLATE 子句連接到多個參數是錯誤的,更多細節請參閱第 23.2 節)。因此,這會産生與前面的例子相同的結果:
但是這會有錯:
因為它試圖將排序規則應用於「>」運算子的結果,該運算符是不可排序的布林資料型別。
Scalar 子查詢指的是括號中的普通 SELECT 查詢,但它只回傳一個資料列的一個欄位。(有關撰寫查詢的訊息,請參閱第 7 章。)執行 SELECT 查詢並在周圍的值表示式中使用單個回傳的值。使用回傳多於一個資料列或多於一個欄位的查詢作為 scalar 子查詢是錯誤的。(但是,如果在特定執行過程中子查詢不回傳任何資料列,則不會出現錯誤;Scalar 結果將視為空)。子查詢可以引用周圍查詢中的變數,該變數在任何一次運算期間都將用作常數的子查詢。有關子查詢的其他表示式,另請參閱第 9.22 節。
例如,以下是每個州中最大的城市人口數量:
陣列建構函數是一種使用其成員元素的值建構陣列的表示式。一個簡單的陣列建構函數由關鍵字 ARRAY,左方括號 [,陣列元素值的表示式列表(用逗號分隔),最後一個右方括號 ] 組成。例如:
預設情況下,陣列元素型別是成員表示式的通用型別,使用與 UNION 或 CASE 結構相同的規則來決定(參閱 10.5 節)。您也可以透過明確將陣列建構函數轉換為所需的型別來覆蓋它,例如:
這與分別將每個表示式轉換為陣列元素型別的效果相同。有關型別轉換的更多訊息,請參閱第 4.2.9 節。
可以透過巢狀的陣列建構函數來建構多維陣列。在內部的建構函數中,關鍵字 ARRAY 可以省略。例如,這些語法會產生相同的結果:
由於多維陣列必須是矩形,因此同一級別的內部建構函數必須産生具有相同維數的子陣列。套用於外部 ARRAY 建構函數的任何強制型別都會自動轉送給所有內部建構函數。
多維陣列建構函數的元素可以是任何產生適當型別陣列的東西,不僅只是一個子 ARRAY 結構。例如:
你可以建構一個空陣列,但由於不可能有一個沒有型別的陣列,所以你必須明確地將你的空陣列轉換為所需的型別。例如:
也可以從子查詢的結果中建構一個陣列。在這種形式下,陣列建構函數使用關鍵字 ARRAY 和小括號(不是中括號)的子查詢寫入。例如:
子查詢必須回傳一個資料列。如果子查詢的輸出欄位是非陣列型別,則産生的一維陣列將具有子查詢結果中每個資料列的元素,其元素型別與子查詢的輸出欄位匹配。如果子查詢的輸出欄位是一個陣列型別,則結果將是一個相同型別的陣列,但會是一個更高的維度;在這種情況下,所有子查詢資料列都必須産生具有相同維度的陣列,否則結果將不是矩形。
用 ARRAY 建構的陣列索引值的下標始終以 1 開頭。有關陣列的更多訊息,請參閱第 8.15 節。
資料列建構函數是一個表示式,它使用其成員字串的值建構資料列內容(也稱為複合值)。資料建構函數由關鍵字 ROW,左括號,資料列字串的零個或多個表示式(以逗號分隔)所組成,最後則是右括號。例如:
當列表中有多個表示式時,關鍵詞 ROW 是選用的。
資料列建構函數可以包含語法 rowvalue.,它將被延展為資料列內容的元素列表,就像在 SELECT 回傳列表的使用 . 語法時一樣(請參閱第 8.16.5 節)。例如,如果資料列具有欄位 f1 和 f2,則這些欄位是相同的:
在 PostgreSQL 8.2 之前,. 語法在資料列建構函數中不會展開,因此寫了ROW(t., 42) 會建立一個兩個字串欄位的資料列,其第一個是欄位是另一個資料列值。新的建構行為通常更有用。如果您需要嵌套資料列值的舊行為,請不要使用 .* 的內部資料列值,例如 ROW(t, 42)。
預設情況下,由 ROW 表示式建立的值是匿名記錄型別。如有必要,可將其轉換為指定的複合型別 - 資料表的資料列型別或使用 CREATE TYPE AS 建立的複合型別。可能需要明確表示以避免歧義。例如:
資料列建構函數可用於建構要儲存在複合型別資料表欄位中的複合內容,或者要傳遞給接受複合參數的函數。此外,可以比較兩個資料列值或用 IS NULL 或 IS NOT NULL 來測試資料列,例如:
更多細節請參閱第 9.23 節。資料列建構函數也可以與子查詢結合使用,如第 9.22 節所述。
並沒有定義子表示式的運算順序。特別是,運算子或函數的輸入不一定是從左到右或以任何其他固定順序進行運算。
進一步來說,如果一個表示式的結果可以透過只運算它的某些部分來得到,那麼其他子表示式可能根本就不會被運算。 例如,如果有人寫了:
那麼 somefunc() 將(可能)根本不會被呼叫。如果有人寫了:
請注意,這與在某些程語言中發現的布林運算是從左到右的「短路」不同。
因此,將具有副作用的函數用作複雜表示式的一部分是不明智的。在 WHERE 和 HAVING 子句中依賴副作用或運算順序是特別危險的,因為這些子句作為製定執行計劃的一部分經常式會被重新運算。這些子句中的布林表示式(AND / OR / NOT 組合)可以按照布林代數法則的任何方式重新組織。
如果必須強制執行某部份的運算指令,則可以使用 CASE 結構(請參閱第 9.17 節)。例如,這是試圖避免在 WHERE 子句中除以零不可信任的方式:
但這樣是安全的:
以這種方式使用的 CASE 構造將放棄最佳化嘗試,因此只能在必要時進行。(在這個特定的例子中,透過改寫為 y> 1.5 * x 來避免這個問題會更好。)
然而,CASE 對於這些問題並不是萬能的。上述技術的一個局限是它不能阻止對常數子表示式的預先評估。如第 37.6 節所述,標記為 IMMUTABLE 的函數和運算子可以在查詢計劃時進行運算,而不是在執行時進行運算。因此,例如:
由於查詢規劃試圖簡化常數子表示式,因此即使資料表中的每一個資料列都具有 x> 0,以至於在執行時永遠不會走到 ELSE,也可能導致除以零的例外情況。
雖然這個特殊的例子看起來很愚蠢,但是在函數中執行的查詢中可能會出現不明顯涉及常數的情況,因為函數參數和局部變數的值可以作為常數插入到查詢中以用於查詢規劃。例如,在 PL/pgSQL 函數中,使用 IF-THEN-ELSE 語句來保護有風險的運算要比將它嵌套在 CASE 表示式中要安全得多。
同一種類型的另一個限制是,CASE 無法阻止運算其中包含的彙總表示式,因為需要在 SELECT 資料列表或 HAVING 子句中的其他表示式之前計算彙總表示式。例如,下面的查詢可能會導致一個除以零例外情況,儘管似乎已經受到保護:
min() 和 avg() 彙總運算是在所有輸入的資料列上同時計算的,因此如果任何員工的資料等於零,則在有任何測試 min() 結果的機會之前,發生除以零的錯誤。相反,使用 WHERE 或 FILTER 子句來防止有問題的輸入資料列,將可以在彙總函數之前來預防這種情況發生。
expression operator expression
(雙元中置運算子)
operator expression
(單元前置運算子)
expression operator
(單元後置運算子)
SQL 語法包含一連串的命令,命令是由一系列的指示記號所組合而成,以分號結尾。最後如果是串流輸入,也會結束一個命令。指示的合法性是由特別的命令語法所定義的。
指示記號可能是關鍵字、識別項、引號識別項、文字、或一個特別的字元符號。指示一般來說是以空白分隔(空白符號、定位符號、換行符號),但如果不會混淆的話,也不一定需要。(一般只出現在特殊字元用來調整了其他指示的型別)
舉個例子,下面就是一個合法(符合語法)的 SQL 輸入:
這個序列包含了 3 個命令,每行一個(然而這不是一定的,同一行可以超過一個命令,而一個命令也可以分解為多行使用)。
順帶一提的是,註解也是 SQL 輸入的一部份,但不屬於任何指示記號,他們等同於空白字元。
SQL 語法並不是很嚴格要求什麼樣的指示記號來識別命令,或是哪些是運算子或參數。通常最前面的指示記號是命令的名稱,以上面的例子來說,我們通常會說是一個「SELECT」、一個「UPDATE」、以及一個「INSERT」命令。但對於 UPDATE 命令而言,有一個 SET 指示記號出現在某個地方是必要的;同樣地,INSERT 也需要有 VALUES 來搭配。精確的語法規則都在第 6 部份中的章節進行說明。
在上面的例子中的 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 檔案中。
關鍵字和無引號識別項都是不分大小寫的,所以:
等同於:
有一種寫法很常使用,就是把關鍵字用大寫表示,而識別項名稱使用小寫,例如:
第二種要介紹的識別項是,受限制的識別項,或是引號識別項。它的形式就是以雙引號括住的任何字串。受限制的識別項,就一定是識別項,不會是關鍵字。所以,「"select"」就會被識別為名稱為「select」的表格或欄位,而無引號的 select 就會被視為是關鍵字,也可能會產生解譯錯誤,如果剛好用在可能是表格或欄位名稱的位置上的話。使用引號識別項的例子如下:
引號識別項可以包含任何字元,除了字元碼為 0 的字元以外。(要包含雙引號字元的話,請使用連續兩個雙引號。)這可以用來建立原來不能使用的表格或欄位名稱,甚至是包含空白或"&"。但長度的限制仍然要遵守。
還有一種變形的引號識別項,允許包含跳脫的形式來表現萬國碼(unicode)。這種變形會以「U&」開頭(U大小寫皆可)緊接在前面的雙引號的前面,不能有任何空白在它們之間,例如:U&"foo"。(注意,這可能會和運算子的 & 產生混淆,但可以在運算子的 & 前後都加上空白來避免這個問題。)在雙引號內,萬國碼字元以跳脫的形式表現,也就是以倒斜線再接 4 位數的 16 進位碼,或倒斜線接一個加號再串一組 6 位數的 16 進位碼。例如,識別項 "data" 可以寫成這樣:
下面是稍微不簡明的例子是,俄文的"slon"(大象),以希伯萊文字母表現:
如果希望以不同的跳脫字元來代替倒斜線的話,那麼可以雙引號結束後使用 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"。如果你要增加語法的可攜性的話,建議最好都使用引號括起特別的名稱,或者都不要使用引號。)
PostgreSQL 中有三種隱含型別的常數:字串、位元字串、和數值。常數也可以強制型別,有助於更精確的表達,也可以讓系統處理更有效率。接下來就開始進行相關的說明。
在 SQL 中,所謂的字串常數,指的是用單引號括住的任意字元串列,例如:'This is a string'。如果在字串常數內需要有單引號的話就使用連續兩個單引號,例如:'Dianne''s horse'。注意這不是雙引號,是兩個單引號。
兩個字串常數如果只用空白及至少一個換行符號所分隔的話,那個它們會被連在一起,和寫成一個字串是一樣的。舉例來說:
等同於:
但如果是這樣:
語法上就不正確了。(這是來自於 SQL 奇怪的常規,PostgreSQL 單純只是遵循。)
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 的字元不能使用在字串常數當中。
PostgreSQL 也支援其他跳脫字串的語法,可以用來直接輸入任意的萬國碼字元。萬國碼跳脫字串常數是以 U& (U& 或 u& 皆可)開頭,然後緊接著單引號括住的字串,記得中間不能有任何空白,例如:U&'foo'。(注意這可能會混淆到 & 的使用,最好在其他使用 & 作為運算子的指令中,在 & 前後 加上空白字元,以避免這個問題。)在括住的內容裡,萬國碼字元可以使用跳脫字元來指定,也就是使用倒斜線再接一組 4 位數的 16 進位值,或者以倒斜線加上加號再接一組 6 位數的 16 進位值。舉個例子,字串 'data' 也可以寫成:
下面是稍微不簡明的例子是,俄文的"slon"(大象),以希伯萊文字母表現:
如果希望以不同的跳脫字元來代替倒斜線的話,那麼可以雙引號結束後使用 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 次。
標準的語法用於字串常數的設定很方便的,但如果字串裡有很多單引號或倒斜線,可讀性就很低了,因為它們都必須再連續多一個符號輸入。像這樣的例子,要改善可讀性的話,PostgreSQL 提供了另一個方式,稱作「錢字引號」(dollar quoting),來描述字串常數。錢字引號字串常數包含一個錢字號($),可省略或多個字元所組成的「標籤」,另一個錢字號,組成字川的任何序列文字,再一個錢字號,與起始的錢字引號同樣的標籤,再一個錢字號。舉例來說,這裡有兩個不同使用錢字引號的方式,但都是「Dianne's horse」
注意在錢字引號字串中,單引號的使用就不需要跳脫處理了。實際上,在錢字引號字串中,沒有字元需要跳脫處理:字串內容就原樣輸出。倒斜錢並不特別,就算是錢字號也是,除非它們是引號標籤配對的一部份。
巢狀錢字字串常數是可以的,只要在不同層選擇不同的標籤就好。最常見的用途就是撰寫函數定義。舉例如下:
這裡,「$q$[\t\r\n\v\]$q$」以錢字引號字串輸出就是「[\t\r\n\v\]」,作為 PostgreSQL 的函數內容。但這個字串並不會和外層的 $function$ 配對。對外層的字串而言,它只是被包裏的一部份字元而已。
以錢字符作為標籤(如果有的話)的引號字串和無引號的識別項,遵循相同的規則,除了它無法包含錢字符號以外。標籤是區分大小寫的,所以 $tag$String content$tag$ 是正確的,而 $TAG$String content$tag$ 是不合法的。
錢字引號字串緊接著關鍵字或識別項的話,就必須以空白分隔;否則錢字號的終止符可能會被當作前面識別項的一部份。
錢字引號並不是標準 SQL 的用法,但當撰寫一些複雜字串的時候,會比標準語法更為便利。當字串常數內嵌於另一個常數時,也是很好用的情境,像自訂函數時就時常用到。使用單引號的語法時,前面例子中的每一個倒斜線,需要使用 4 個倒斜線才能表示(原來字串常數時需要雙倒斜線,然後在執行階段時也需要雙倒斜線,一共就是 4 倍)。
位元字串常數看起來就像是一般的字串常數,只是將 B(大小寫皆可)放在引號的前面(不能有空白),例如:B'1001'。而在位元字串當中,只能有 0 或 1 的存在。
另一方面,位元字串常數也可以表示一個 16 進位的值,使用的先導字為 X(大小寫皆可),例如:X'1FF'。這個撰寫方式與使用前段方式,以 4 位數 2 進位表示每一個 16 進位位數,是相同的結果。
這兩種位元字串常數的表達方式,都可以在字串中換行,如同一般的字串常數。錢字引號表示方式不能使用在位元字串常數上。
數值常數可以以下列語法輸入:
這裡的 digits 指的是 0 到 9 的多位數十進位數字。如果有小數點的話,在小數點之前或之後要有數字。在指數標記 e 之前,也必須要有數字。字串中間不能再有其他字元或空白出現。注意,最前面正負號並不是數值常數的一部份,它是屬於運算子的概念。
下面是一些合法數值常數的例子:
42 3.5 4. .001 5e2 1.925e-3
數值常數如果沒有小數點或指數標記的話,預設就會被假定為整數,32 位元以內的為整數型別(interger),否則就會以 64 位元的大整數型別(bigint)來處理。其次就會宣告為數值型別(numeric)。只要包含小數點或指數標記的數值,都會預設使用數值型別。
預設數值常數的資料型別只是整個型別解析演算法的開端而已。在多數的情況下,各種常數會自動被轉換為最貼近內容的適當型別。不過,如果需要的話,你可以強制指定一個資料型別給該常數。舉例來說,你可以強制以實數型別(real 或 float4)來處理該數值:
實際上,在型別轉換上還有一些特殊的情況,留待後續探討。
任意型別的常數,可以使用下列的語法來表示:
字串常數的內容會由型別轉換的程序 type 來處理,其結果就會得到該常數的專屬型別。明定型別轉換可以被省略,如果不會混淆的話(舉例來說,要輸入給特定的表格欄位的話,因為已有型別宣告,就不會混淆),那麼就會自動給定型別。
字串常數可以使用一般 SQL 標準寫法,或是錢字引號寫法。
還可以使用函數式的語法來撰寫:
但並非所有的型別都可以使用這個方式,請參閱 4.2.9 節取得詳細說明。
「::」、CAST()、及函數式語法,也可以用來指定任何表示式在執行中的型別轉換,如同 4.2.9 節中所描述的。要避免語法上的混淆,「type 'string'」這個語法,只能用在指定簡單的文字常數,另一個限制是,不能用於陣列型別。陣列常數的型別指定,請使用 :: 或 CAST() 的語法。
一個運算子最長可以是 NAMEDATALEN - 1(預設為 63 個字元),除了以下的字元之外:
* / <> = ~ ! @ # % ^ & | ` ?
還有一些運算子的限制:
「--」和「/*」都不能出現在運算子裡,因為它們表示註解的開始。
多字元的運算子不能以 + 或 - 結尾,除非名稱裡也包含了下列字元:
~ ! @ # % ^ & | ` ?
舉個例子,@- 可以是合法的運算子,但 *- 就不合法。這個限制是讓 PostgreSQL 解譯 SQL 語法時,可以不需要在不同的標記間使用空白分隔。
當使用非 SQL 標準的運算子時,你通常需要在相隣的運算子間使用空白以免混淆。舉例來說,如果你已經定義了一個左側單元運算子 @,你就不能使用 X*@Y,必須寫成 X* @Y,以確保 PostgreSQL 可以識別為兩個運算子,而不是一個。
有一些字元並不是字母型態,而具有特殊意義,但並非運算子。詳細的說明請參閱相對應的語法說明。本節僅簡要描述這些特殊字元的使用情境。
錢字號($)其後接著數字的話,用來表示函數宣告或預備指令的參數編號。其他的用法還有識別項的一部份,或是錢字引號常數。
小括號(( ))一般用來強調表示式並且優先運算。還有某些情況用於表示某些 SQL 指令的部份的必要性。
中括號([ ])用於組成陣列的各個元素。詳情請參閱 8.15 節有關於陣列的內容。
逗號(,)用於一般語法上的結構需要,來分隔列表中的單元。
分號(;)表示 SQL 指令的結束。它不能出現在指令中的其他位置,除非是在字串常數當中,或是引號識別項。
冒號(:)用在取得陣列的小項。(參閱 8.15 節)在某些 SQL 分支(篏入式 SQL 之類的)中,冒號用來前置變數名稱。
米字號(*)用來表示表格中所有的欄位,或複合性的內容。它也可以用於函數宣告時,不限制固定數量的參數。
頓號(.)用在數值常數之中,也用於區分結構、表格、及欄位名稱。
註解是以連續兩個破折號開頭,一直到行結尾的字串。例如:
另外,C 語言的註解語法也可以使用:
這樣的註解,以「/*」開頭,一直持續到對應的「*/」出現才結束。這樣區塊式的註解可以巢狀使用,所以你可以一次註解掉一堆包含註解的指令。這點是 SQL 的標準,和 C 語言的使用不太一樣的地方。
註解會在進一步的語法分析前被消去,也可以方便地以空白字元替代。
Table 4.2 列出在 PostgreSQL 中,運算子的運算優先權及運算次序。大多數的運算子都是相同的運算優先權,並且是左側運算。這些優先權與次序是撰寫在解譯器的程式當中的。
你有時候需要加上括號,當遇到二元運算子與一元運算子一起出現時。舉個例子:
會被解譯為:
因為解譯器並不知道實際的情況,所以它可能會搞錯。「!」是一個後置運算子,並非中置運算子。在這個例子中,要以想要的方式進行運算的話,你必須要改寫為:
這是為了延展性而需要付出的代價。
Table 4.2. Operator Precedence (highest to lowest)
注意,使用與內建運算子同名的自訂運算子,運算優先權的規則也會以原規則適用,如同上面的樣子。舉例來說,如果你定義了一個「+」的運算子,用於自訂的資料型態,那麼它就會和內建的「+」擁有相同的運算優先權,而與你的運算內容無關。
當某個結構操作的運算子用於 OPERATOR 語法之中時,如下所示:
OPERATOR 建構式被用來為任何運算子,取得如 Table 4.2 中所示的預設運算優先權。不論在 OPERATOR() 中指定什麼運算子,都會回傳 true 的結果。
PostgreSQL 在 9.5 之前的運算優先權有一些不同。比較特別的是,比較運算子「<= >= <>」是和一般其他運算子是相同等級的;「IS」先前的優先權較高;而「NOT BETWEEN」和相關的建構式行為不一致,使得在某些情況下,「NOT」和「BETWEEN」的優先權不同。這些規則的改變是為了與 SQL 標準有更好的相容性,減少因為等價轉換的不一致處理所造成的困擾。大多數的情況,這些改變並不需要使用習慣的改變,也不會產生沒有運算子的錯誤,而且都可以透過增加括號來解決。然而,有一些極端的情況可能會在沒有錯誤的情況改變其運算行為。如果你很關心這些變化,很擔心這些無聲的錯誤,你可以打開參數 operator_precedence_warning 來測試你的程式,然後檢查是否有警告被記錄下來。
倒斜線跳腳字串
字元意義
\b
backspace(倒退)
\f
form feed(換頁)
\n
newline(換行)
\r
carriage return(回到行首)
\t
tab(定位符號)
\o
,\oo
,\ooo
(o
= 0 - 7)
octal byte value(8 進位值)
\xh
,\xhh
(h
= 0 - 9, A - F)
hexadecimal byte value(16 進位值)
\uxxxx
,\Uxxxxxxxx
(x
= 0 - 9, A - F)
16 or 32-bit hexadecimal Unicode character value(16 位元或 32 位元的 16 進位萬國碼字元值)
Operator/Element
Associativity
Description
.
left
table/column name separator
::
left
PostgreSQL-style typecast
[]
left
array element selection
+-
right
unary plus, unary minus
^
left
exponentiation
*/%
left
multiplication, division, modulo
+-
left
addition, subtraction
(any other operator)
left
all other native and user-defined operators
BETWEEN / IN / LIKE / ILIKE / SIMILAR
range containment, set membership, string matching
<>=<=>=<>
comparison operators
IS / ISNULL/ NOTNULL
IS TRUE
,IS FALSE
,IS NULL
,IS DISTINCT FROM
, etc
NOT
right
logical negation
AND
left
logical conjunction
OR
left
logical disjunction