8.15. Arrays · PostgreSQL 中文文檔 9.3

# 8.15\. Arrays PostgreSQL允許將字段定義成變長的多維數組。數組類型可以是任何基本類型或用戶定義類型，枚舉類型或復合類型。不支持域的數組。 ## 8.15.1\. 數組類型的聲明為說明這些用法，我們先創建一個由基本類型數組構成的表： ``` CREATE TABLE sal_emp ( name text, pay_by_quarter integer[], schedule text[][] ); ``` 如上所示，一個數組類型是通過在數組元素類型名后面附加方括弧(`[]`)來命名的。上面的命令將創建一個叫`sal_emp`的表，表示雇員名字的`name` 字段是一個`text`類型字符串，表示雇員季度薪水的`pay_by_quarter` 字段是一個一維`integer`數組，表示雇員周計劃的`schedule` 字段是一個兩維`text`數組。 `CREATE TABLE`的語法允許聲明數組的確切大小，比如： ``` CREATE TABLE tictactoe ( squares integer[3][3] ); ``` 不過，目前的實現忽略任何提供的數組尺寸限制(等價于未聲明長度的數組)。目前的實現也不強制數組維數。特定元素類型的數組都被認為是相同的類型，不管他們的大小或者維數。因此，在`CREATE TABLE` 里定義數字或者維數都不影響運行時的行為。另外還有一種語法，它通過使用關鍵字`ARRAY`遵循 SQL 標準，可以用于聲明一維數組。`pay_by_quarter`可以定義為： ``` pay_by_quarter integer ARRAY[4], ``` 或者不聲明數組的大小： ``` pay_by_quarter integer ARRAY, ``` 不過，如前所述，PostgreSQL并不強制這個尺寸限制。 ## 8.15.2\. 數組值輸入將數組寫成文本的時候，用花括弧把數組元素括起來并且用逗號將它們分開(如果你懂 C ，那么這與初始化一個結構很像)。你可以在數組元素值周圍放置雙引號，但如果這個值包含逗號或者花括弧，那么就必須加上雙引號(下面有更多細節)。因此，一個數組常量的常見格式如下： ``` '{ _val1_ _delim_ _val2_ _delim_ ... }' ``` 這里的`_delim_`是該類型的分隔符，就是在該類型的 `pg_type`記錄中指定的那個。在PostgreSQL 發布提供的標準數據類型里，所有類型都使用逗號(`,`),除了`box` 類型使用分號(`;`)之外。每個`_val_` 要么是一個數組元素類型的常量，要么是一個子數組。一個數組常量的例子如下： ``` '{{1,2,3},{4,5,6},{7,8,9}}' ``` 這個常量是一個 3 乘 3 的兩維數組，由三個整數子數組組成。要將一個數組元素的值設為 NULL ，直接寫上`NULL`即可(大小寫無關)。要將一個數組元素的值設為字符串"NULL"，那么你必須加上雙引號。這種數組常量實際上只是我們在[Section 4.1.2.7](#calibre_link-788) 里討論過的一般類型常量的一種特例。常量最初是當作字符串看待并且傳遞給數組輸入轉換器的，可能需要使用明確的類型聲明。現在我們可以展示一些`INSERT`語句。 ``` INSERT INTO sal_emp VALUES ('Bill', '{10000, 10000, 10000, 10000}', '{{"meeting", "lunch"}, {"training", "presentation"}}'); INSERT INTO sal_emp VALUES ('Carol', '{20000, 25000, 25000, 25000}', '{{"breakfast", "consulting"}, {"meeting", "lunch"}}'); ``` 前面的兩個插入的結果看起來像這樣： ``` SELECT * FROM sal_emp; name | pay_by_quarter | schedule -------+---------------------------+------------------------------------------- Bill | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}} Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}} (2 rows) ``` 多維數組必須匹配每個維的元素數。如果不匹配將導致錯誤： ``` INSERT INTO sal_emp VALUES ('Bill', '{10000, 10000, 10000, 10000}', '{{"meeting", "lunch"}, {"meeting"}}'); ERROR: multidimensional arrays must have array expressions with matching dimensions ``` 我們還可以使用`ARRAY`構造器語法： ``` INSERT INTO sal_emp VALUES ('Bill', ARRAY[10000, 10000, 10000, 10000], ARRAY[['meeting', 'lunch'], ['training', 'presentation']]); INSERT INTO sal_emp VALUES ('Carol', ARRAY[20000, 25000, 25000, 25000], ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]); ``` 請注意數組元素是普通的 SQL 常量或者表達式；比如，字符串文本是用單引號包圍的，而不是像數組文本那樣用雙引號。`ARRAY`構造器語法在[Section 4.2.12](#calibre_link-1640) 里有更詳細的討論。 ## 8.15.3\. 訪問數組現在我們可以在這個表上運行一些查詢。首先，我們演示如何訪問數組的一個元素。這個查詢檢索在第二季度薪水變化的雇員名： ``` SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2]; name ------- Carol (1 row) ``` 數組的下標數字是寫在方括弧內的。PostgreSQL 缺省使用以 1 為基的數組習慣，也就是說，一個`_n_` 元素的數組從`array[1]`開始，到`array[``_n_`]結束。這個查詢檢索所有雇員第三季度的薪水： ``` SELECT pay_by_quarter[3] FROM sal_emp; pay_by_quarter ---------------- 10000 25000 (2 rows) ``` 我們還可以訪問一個數組的任意矩形片段，或稱子數組。對于一維或更多維數組，可以用`_下標下界_`:`_下標上界_` 表示一個數組的某個片段。比如，下面查詢檢索 Bill 該周頭兩天的第一件計劃： ``` SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill'; schedule ------------------------ {{meeting},{training}} (1 row) ``` 如果任意維數被寫為一個片段，也就是，包含一個冒號，那么所有維數都被當做是片段。任意只有一個數字（沒有冒號）的維數是從1開始到聲明的數字為止的。例如，`[2]` 被認為是`[1:2]`，就想下面例子中一樣： ``` SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill'; schedule ------------------------------------------- {{meeting,lunch},{training,presentation}} (1 row) ``` 為了與沒有片段的情況相區分，最好是對所有維數都使用片段語法，例如，`[1:2][1:1]`，而不是 `[2][1:1]`。如果數組本身或任何下標表達式是 NULL ，那么該數組的下標表達式也將生成 NULL 。從一個數組的當前范圍之外抓取數據將生成一個 NULL ，而不是導致錯誤。比如，如果`schedule`目前的維是 `[1:3][1:2]`，然后我們抓取`schedule[3][3]`會生成 NULL 。類似的還有，一個下標錯誤的數組引用也生成 NULL ，而不是錯誤。如果數組本身或任何下標表達式是 NULL ，那么該數組的片段表達式也將生成 NULL 。但在其它其它情況下，比如抓取一個完全在數組的當前范圍之外的數組片斷，將生成一個空數組(零維)而不是 NULL 。（這不匹配無片段數組的行為并且是為歷史原因這樣做的。）如果抓取的片斷部分覆蓋數組的范圍，那么它會自動縮減為抓取覆蓋的范圍而不是返回null。任何數組的當前維數都可以用`array_dims`函數檢索： ``` SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol'; array_dims ------------ [1:2][1:2] (1 row) ``` `array_dims`生成一個`text`結果，對于人類可能比較容易閱讀，但是對于程序可能就不那么方便了。我們也可以用`array_upper`和`array_lower` 函數分別返回數組特定維的上界和下界: ``` SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol'; array_upper ------------- 2 (1 row) ``` `array_length`將返回特定維數數組的長度： ``` SELECT array_length(schedule, 1) FROM sal_emp WHERE name = 'Carol'; array_length -------------- 2 (1 row) ``` ## 8.15.4\. 修改數組一個數組值可以完全被代替： ``` UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}' WHERE name = 'Carol'; ``` 或者使用`ARRAY`構造器語法： ``` UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000] WHERE name = 'Carol'; ``` 或者只是更新某一個元素： ``` UPDATE sal_emp SET pay_by_quarter[4] = 15000 WHERE name = 'Bill'; ``` 或者更新某個片斷： ``` UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}' WHERE name = 'Carol'; ``` 可以通過給一個尚不存在數組元素賦值的辦法擴大數組，所有位于原數組最后一個元素和這個新元素之間的未賦值元素都將設為 NULL 。例如，如果`myarray`數組當前有 4 個元素，在對`myarray[6]`賦值之后它將擁有 6 個元素，其中`myarray[5]` 的值將為 NULL 。目前，只允許對一維數組使用這種方法擴大(對多維數組行不通)。下標賦值允許創建下標不從 1 開始的數組。比如，我們可以給`myarray[-2:7]` 賦值，創建一個下標值在 -2 到 7 之間的數組。新的數組值也可以用連接操作符`||`構造: ``` SELECT ARRAY[1,2] || ARRAY[3,4]; ?column? ----------- {1,2,3,4} (1 row) SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]]; ?column? --------------------- {{5,6},{1,2},{3,4}} (1 row) ``` 連接操作符允許把一個元素壓入一維數組的開頭或者結尾。它還接受兩個`_N_` 維的數組，或者一個`_N_`維和一個`_N+1_`維的數組。當向一維數組的頭部或尾部壓入單獨一個元素后，數組的下標下界保持不變。比如： ``` SELECT array_dims(1 || '[0:1]={2,3}'::int[]); array_dims ------------ [0:2] (1 row) SELECT array_dims(ARRAY[1,2] || 3); array_dims ------------ [1:3] (1 row) ``` 如果將兩個相同維數的數組連接在一起，結果數組將保持左操作數的外層維數的下標下界。結果是這樣一個數組：包含左操作數的每個元素，后面跟著右操作數的每個元素。比如： ``` SELECT array_dims(ARRAY[1,2] || ARRAY[3,4,5]); array_dims ------------ [1:5] (1 row) SELECT array_dims(ARRAY[[1,2],[3,4]] || ARRAY[[5,6],[7,8],[9,0]]); array_dims ------------ [1:5][1:2] (1 row) ``` 如果將一個`_N_`維的數組壓到一個`_N+1_`維數組的開頭或者結尾，結果和上面數組元素的情況類似。每個`_N_`維的子數組實際上都是`_N+1_` 維數組的最外層的元素。比如： ``` SELECT array_dims(ARRAY[1,2] || ARRAY[[3,4],[5,6]]); array_dims ------------ [1:3][1:2] (1 row) ``` 數組也可以用`array_prepend`,`array_append`, `array_cat`函數構造。前兩個只支持一維數組，而`array_cat` 支持多維數組。請注意使用上面討論的連接操作符要比直接使用這些函數好。實際上，這些函數主要用于實現連接操作符。不過，在用戶定義的創建函數里直接使用他們可能有必要。一些例子： ``` SELECT array_prepend(1, ARRAY[2,3]); array_prepend --------------- {1,2,3} (1 row) SELECT array_append(ARRAY[1,2], 3); array_append -------------- {1,2,3} (1 row) SELECT array_cat(ARRAY[1,2], ARRAY[3,4]); array_cat ----------- {1,2,3,4} (1 row) SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]); array_cat --------------------- {{1,2},{3,4},{5,6}} (1 row) SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]); array_cat --------------------- {{5,6},{1,2},{3,4}} ``` ## 8.15.5\. 在數組中檢索要搜索一個數組中的數值，你必須檢查該數組的每一個值。你可以手工處理(如果你知道數組尺寸)。比如： ``` SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR pay_by_quarter[2] = 10000 OR pay_by_quarter[3] = 10000 OR pay_by_quarter[4] = 10000; ``` 不過，對于大數組而言，這個方法很快就會讓人覺得無聊，并且如果你不知道數組尺寸，那就沒什么用了。另外一個方法在[Section 9.23](#calibre_link-1529) 里描述。上面的查詢可以用下面的代替： ``` SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter); ``` 另外，你可以用下面的語句找出數組中所有元素值都等于 10000 的行： ``` SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter); ``` 或者，可以使用`generate_subscripts`函數。例如： ``` SELECT * FROM (SELECT pay_by_quarter, generate_subscripts(pay_by_quarter, 1) AS s FROM sal_emp) AS foo WHERE pay_by_quarter[s] = 10000; ``` 這個函數在[Table 9-50](#calibre_link-2151)里面描述。你可以使用`&&`操作符檢索一個數組，它可以檢查左操作數是否與右操作數重疊。例如： ``` SELECT * FROM sal_emp WHERE pay_by_quarter && ARRAY[10000]; ``` 這個操作符和另外一個數組操作符在[Section 9.18](#calibre_link-1526)里有詳細的描述。它可以通過一個恰當的索引加速，在[Section 11.2](#calibre_link-714)里面描述。 > **Tip:** 數組不是集合；需要像前面那樣搜索數組中的特定元素通常表明你的數據庫設計有問題。數組字段通常是可以分裂成獨立的表。很明顯表要容易搜索得多，并且在元素數目非常龐大的時候也可以更好地伸展。 ## 8.15.6\. 數組輸入和輸出語法一個數組值的外部表現形式由一些根據該數組元素類型的 I/O 轉換規則分析的項組成，再加上一些標明該數組結構的修飾。這些修飾由圍繞在數組值周圍的花括弧(`{` 和`}`)加上相鄰項之間的分隔字符組成。分隔字符通常是一個逗號(`,`) 但也可以是其它的東西：它由該數組元素類型的`typdelim`設置決定。在PostgreSQL提供的標準數據類型里，所有類型都使用逗號，除了`box`類型使用分號(`;`)外。在多維數組里，每個維都有自己級別的花括弧，并且在同級相鄰的花括弧項之間必須寫上分隔符。如果數組元素值是空字符串或者包含花括弧、分隔符、雙引號、反斜杠、空白，或者匹配關鍵字`NULL`，那么數組輸出過程將在這些值周圍包圍雙引號。在元素值里包含的雙引號和反斜杠將被反斜杠逃逸。對于數值數據類型，你可以安全地假設數值沒有雙引號包圍，但是對于文本類型，我們就需要準備好面對有雙引號包圍和沒有雙引號包圍兩種情況了。缺省時，一個數組的某維的下標索引是設置為 1 的。如果一個數組的某維的下標不等于 1 ，那么就會在數組結構修飾域里面放置一個實際的維數。這個修飾由方括弧(`[]`)圍繞在每個數組維的下界和上界索引，中間有一個冒號(`:`)分隔的字符串組成。數組維數修飾后面跟著一個等號操作符(`=`)。比如： ``` SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2 FROM (SELECT '[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}'::int[] AS f1) AS ss; e1 | e2 ----+---- 1 | 6 (1 row) ``` 僅當一個或多個下界不等于 1 時，數組輸出程序才在結果中包含明確的尺寸。如果一個數組元素的值寫成`NULL`(無論大小寫如何)，那么該元素的值就是 NULL 。而引號和反斜杠可以表示輸入文本字符串"NULL"值。另外，為了兼容 8.2 之前的版本，可以將[array_nulls](#calibre_link-2152) 配置參數設為`off`以禁止將`NULL`識別為 NULL 。如前所示，當書寫一個數組值的時候，可以在任何元素值周圍使用雙引號。當元素值可能讓數組值解析器產生歧義時，你_必須_這么做。例如：元素值包含花括號、逗號(或者數據類型分割符)、雙引號、反斜杠、在開頭/結尾處有空白符、匹配 NULL 的字符串。要在元素值中包含雙引號或反斜杠，可以加一個前導反斜杠。當然，你也可以避免引用和使用反斜杠逃逸來保護任何可能引起語法混淆的字符。你可以在左花括弧前面或者右花括弧后面寫空白。你還可以在任意獨立的項字符串前面或者后面寫空白。所有這些情況下，這些空白都會被忽略。不過，在雙引號包圍的元素里面的空白，或者是元素里被兩邊非空白字符包圍的空白，都不會被忽略。 > **Note:** 請記住你在 SQL 命令里寫的任何東西都將首先解釋成一個字符串文本，然后才是一個數組。這樣就造成你所需要的反斜杠數量翻了翻。比如，要插入一個包含反斜杠和雙引號的`text`數組，你需要這么寫： > > ``` > INSERT ... VALUES (E'{"\\\\","\\""}'); > ``` > > 字符串文本處理器去掉第一層反斜杠，然后剩下的東西到了數組數值分析器的時候將變成 `{"\\","\""}`。接著，該字符串傳遞給`text`數據類型的輸入過程，分別變成`\`和`"`。如果我們使用的數據類型對反斜杠也有特殊待遇，比如`bytea`，那么我們可能需要在命令里放多達八個反斜杠才能在存儲態的數組元素中得到一個反斜杠。也可以用美元符界定(參閱[Section 4.1.2.4](#calibre_link-736))來避免雙份的反斜杠。 > **Tip:** `ARRAY`構造器語法(參閱[Section 4.2.12](#calibre_link-1640)) 通常比數組文本語法好用些，尤其是在 SQL 命令里寫數組值的時候。在`ARRAY`里，獨立的元素值的寫法和數組里沒有元素時的寫法一樣。