在 PostgreSQL 18 中，數(shù)組類(lèi)型提供了非常豐富的內(nèi)置函數(shù)，可以極大地提升對(duì)數(shù)組數(shù)據(jù)的處理效率。以下為整理了 PostgreSQL 操作數(shù)組的常用函數(shù)全集及詳細(xì)講解：

?? 創(chuàng)建數(shù)組

在 PostgreSQL 中創(chuàng)建數(shù)組，通常分為在表中定義數(shù)組字段和在查詢(xún)中直接構(gòu)造數(shù)組兩種情況。

以下是詳細(xì)的操作方法：

1. 在表中定義數(shù)組字段

在創(chuàng)建表時(shí)，可以在任何標(biāo)準(zhǔn)的數(shù)據(jù)類(lèi)型（如 TEXT、INTEGER 等）后面加上方括號(hào) [] 來(lái)聲明該字段為數(shù)組。

• 一維數(shù)組：使用 數(shù)據(jù)類(lèi)型[]
• 多維數(shù)組：使用 數(shù)據(jù)類(lèi)型[][]（實(shí)際上 PostgreSQL 不強(qiáng)制限制維度，但這樣寫(xiě)語(yǔ)義更清晰）

-- 創(chuàng)建一個(gè)包含一維和二維數(shù)組字段的表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT,
    tags TEXT[],           -- 定義一個(gè)文本類(lèi)型的一維數(shù)組
    scores INTEGER[],      -- 定義一個(gè)整數(shù)類(lèi)型的一維數(shù)組
    matrix INTEGER[][]     -- 定義一個(gè)整數(shù)類(lèi)型的二維數(shù)組
);

PostgreSQL 也支持符合 SQL 標(biāo)準(zhǔn)的 ARRAY 關(guān)鍵字語(yǔ)法，例如 tags TEXT ARRAY 或 scores INTEGER ARRAY。

不過(guò)需要注意，PostgreSQL 目前不會(huì)強(qiáng)制限制數(shù)組的實(shí)際長(zhǎng)度或維度，聲明時(shí)指定的大?。ㄈ?``）僅起到文檔說(shuō)明的作用2。

2. 向數(shù)組字段插入數(shù)據(jù)

插入數(shù)組數(shù)據(jù)主要有兩種寫(xiě)法：使用花括號(hào) {} 或者使用 ARRAY 構(gòu)造器2。

-- 方式一：使用 ARRAY 構(gòu)造器（推薦，更直觀）
INSERT INTO users (name, tags, scores, matrix)
VALUES ('Alice', ARRAY['developer', 'gamer'], ARRAY, ARRAY[,]);


-- 方式二：使用花括號(hào)字面量（注意內(nèi)部字符串通常需要加雙引號(hào)）
INSERT INTO users (name, tags, scores)
VALUES ('Bob', '{"teacher", "writer"}', '{75, 80, 85}');

3. 在查詢(xún)中直接創(chuàng)建/構(gòu)造數(shù)組

如果不需要將數(shù)組存入表中，只是想在 SQL 查詢(xún)過(guò)程中臨時(shí)創(chuàng)建一個(gè)數(shù)組，可以使用以下幾種方式：

使用 ARRAY[] 構(gòu)造器：sql

SELECT ARRAY;                -- 結(jié)果: {1,2,3}
SELECT ARRAY['a', 'b', 'c'];          -- 結(jié)果: {a,b,c}
SELECT ARRAY[, ];           -- 結(jié)果: {{1,2},{3,4}} (二維數(shù)組)

使用 string_to_array 函數(shù)（將字符串按分隔符轉(zhuǎn)為數(shù)組）：sql

SELECT string_to_array('apple,banana,orange', ','); 

-- 結(jié)果: {apple,banana,orange}

使用 array_agg 聚合函數(shù)（將多行數(shù)據(jù)合并為一個(gè)數(shù)組）：sql

-- 假設(shè)有一個(gè)包含分?jǐn)?shù)的表，將分?jǐn)?shù)聚合成數(shù)組
SELECT array_agg(score) FROM user_scores WHERE user_id = 1;

?? 補(bǔ)充小知識(shí)

數(shù)組下標(biāo)從 1 開(kāi)始：PostgreSQL 的數(shù)組索引默認(rèn)是從 1 開(kāi)始的，而不是 01。

• 例如 SELECT tags FROM users; 會(huì)取出數(shù)組的第一個(gè)元素。

數(shù)組查詢(xún)：可以使用 ANY 或包含操作符 @> 來(lái)查詢(xún)數(shù)組中是否包含某個(gè)元素。

• 例如：SELECT * FROM users WHERE 'gamer' = ANY(tags);

?? 數(shù)組基本信息與維度函數(shù)

array_length(anyarray, int)
返回?cái)?shù)組在指定維度上的長(zhǎng)度。

SELECT array_length(ARRAY, 1);  
-- 返回 

array_lower(anyarray, int)
返回?cái)?shù)組在指定維度上的下界（最低索引）。

SELECT array_lower(ARRAY, 1);  
-- 返回 1

array_upper(anyarray, int)
返回?cái)?shù)組在指定維度上的上界（最高索引）。

SELECT array_upper(ARRAY, 1);  
-- 返回 

array_ndims(anyarray)
返回?cái)?shù)組的維數(shù)（例如一維數(shù)組返回1，二維數(shù)組返回2）。

1SELECT array_ndims(ARRAY[, ]);  

-- 返回 2

array_dims(anyarray)
返回?cái)?shù)組維數(shù)的文本表示（包含下標(biāo)范圍）。

SELECT array_dims(ARRAY[, ]);  

-- 返回 '[1:2][1:3]'

? 數(shù)組增刪與拼接函數(shù)

array_append(anyarray, anyelement)
向數(shù)組的末尾添加一個(gè)元素。

SELECT array_append(ARRAY, 3);  

-- 返回 {1,2,3}

array_prepend(anyelement, anyarray)
向數(shù)組的開(kāi)頭添加一個(gè)元素。

SELECT array_prepend(1, ARRAY);  

-- 返回 {1,2,3,4,5}

array_cat(anyarray, anyarray)
將兩個(gè)數(shù)組拼接成一個(gè)新數(shù)組。

SELECT array_cat(ARRAY1, ARRAY2); 

 -- 返回 {1,2,3,4,5,6}

array_remove(anyarray, anyelement)
從數(shù)組中刪除所有等于指定值的元素。

-- 例如從數(shù)組中刪除元素 3
SELECT array_remove(ARRAY, 3);  

-- 返回 {1,2,4}

?? 數(shù)組搜索與定位函數(shù)

array_position(anyarray, anyelement [, integer])
返回指定元素在數(shù)組中第一次出現(xiàn)的下標(biāo)（索引從1開(kāi)始）。第三個(gè)可選參數(shù)表示從哪個(gè)下標(biāo)開(kāi)始搜索。

SELECT array_position(ARRAY['sun','mon','tue'], 'mon');  
-- 返回 2

array_positions(anyarray, anyelement)
返回指定元素在數(shù)組中所有出現(xiàn)位置的下標(biāo)數(shù)組。

SELECT array_positions(ARRAY, 1);  
-- 返回 {1,4}

?? 數(shù)組轉(zhuǎn)換與聚合函數(shù)

unnest(anyarray)
將數(shù)組展開(kāi)，返回一組行（Set of rows），常用于將數(shù)組數(shù)據(jù)轉(zhuǎn)換成多行記錄。

SELECT unnest(ARRAY);  

-- 返回三行，分別為 1, 2, 3

array_agg(expression)
聚合函數(shù)，將多行查詢(xún)結(jié)果收集并合并成一個(gè)數(shù)組。

SELECT array_agg(id) FROM users;  

-- 將 users 表的所有 id 聚合成一個(gè)數(shù)組

array_to_string(anyarray, text [, text])
將數(shù)組元素用指定的分隔符拼接成一個(gè)字符串。第三個(gè)可選參數(shù)用于替代數(shù)組中的 NULL 值。

SELECT array_to_string(ARRAY, ', ');  

-- 返回 '1, 2, 3'

string_to_array(text, text [, text])
將字符串按照指定的分隔符拆分成一個(gè)數(shù)組。第三個(gè)可選參數(shù)用于指定哪些字符串被視為 NULL。

SELECT string_to_array('1,2,3', ','); 

 -- 返回 {1,2,3}

array_fill(anyelement, int[], [int[]])
使用提供的值和維度（以及可選的下界）創(chuàng)建一個(gè)新數(shù)組。

SELECT array_fill(7, ARRAY);  

-- 返回 {7,7,7}

array_sort(array1 )

對(duì)數(shù)組的第一維元素進(jìn)行升序排序。

-- 將數(shù)組元素按升序排列
SELECT array_sort(ARRAY1);

-- 結(jié)果: {1,1,3,4,5,9}

array__reverse(anyarray)

將數(shù)組中的元素順序完全反轉(zhuǎn)。

-- 將數(shù)組元素倒序排列
SELECT array_reverse(ARRAY);

-- 結(jié)果: {5,4,3,2,1}

? (二)，數(shù)組操作符

在 PostgreSQL 中，除了豐富的數(shù)組函數(shù)外，數(shù)組操作符也是處理數(shù)組數(shù)據(jù)的核心工具。

?? 數(shù)組比較操作符

這些操作符用于對(duì)兩個(gè)數(shù)組進(jìn)行比較，它們會(huì)對(duì)數(shù)組內(nèi)容進(jìn)行逐個(gè)元素的對(duì)比。

 原生數(shù)組比較是嚴(yán)格匹配的，它不僅要求數(shù)組中的元素完全一致，還要求元素的順序和數(shù)組的維度也必須完全相同。

順序敏感：如果兩個(gè)數(shù)組包含的元素相同，但順序不同，它們會(huì)被視為不相等。

類(lèi)型與維度敏感：即使通過(guò)類(lèi)型轉(zhuǎn)換讓元素值看起來(lái)一樣，如果底層類(lèi)型或維度不同，也可能影響比較結(jié)果（不過(guò)在標(biāo)準(zhǔn)的同類(lèi)型比較中，= 會(huì)進(jìn)行合理的類(lèi)型匹配）。

• =：判斷兩個(gè)數(shù)組是否相等。
• <> ：判斷兩個(gè)數(shù)組是否不相等。
• < ：判斷左側(cè)數(shù)組是否小于右側(cè)數(shù)組。
• > ：判斷左側(cè)數(shù)組是否大于右側(cè)數(shù)組。
• <=：判斷左側(cè)數(shù)組是否小于或等于右側(cè)數(shù)組。
• >=：判斷左側(cè)數(shù)組是否大于或等于右側(cè)數(shù)組。

?? 提示：數(shù)組的排序比較（<, > 等）是基于 B-tree 比較函數(shù)逐個(gè)元素進(jìn)行的，直到遇到第一個(gè)不同的元素來(lái)決定大小。

由于原生的 = 操作符對(duì)順序敏感，可以通過(guò)先排序再比較的方式來(lái)實(shí)現(xiàn)。

?? 數(shù)組拼接操作符 (||)

|| 操作符非常靈活，既可以用于數(shù)組之間的拼接，也可以用于將單個(gè)元素與數(shù)組拼接。

數(shù)組與數(shù)組拼接：將兩個(gè)數(shù)組合并。

SELECT ARRAY || ARRAY;  

-- 返回 {1,2,3,4,5,6}

元素與數(shù)組拼接（前置） ：將元素添加到數(shù)組的開(kāi)頭。

SELECT 3 || ARRAY;  

-- 返回 {3,4,5,6}

數(shù)組與元素拼接（后置） ：將元素添加到數(shù)組的末尾。

SELECT ARRAY || 7;  

-- 返回 {4,5,6,7}

?? 數(shù)組包含與重疊操作符

這些操作符常用于復(fù)雜的條件過(guò)濾，判斷數(shù)組之間的包含關(guān)系或是否有共同元素。

• @> ：包含。判斷左側(cè)數(shù)組是否包含右側(cè)數(shù)組（即右側(cè)數(shù)組的所有元素都在左側(cè)數(shù)組中出現(xiàn)過(guò)）。
• <@ ：被包含于。判斷左側(cè)數(shù)組是否被右側(cè)數(shù)組包含（即左側(cè)數(shù)組的所有元素都在右側(cè)數(shù)組中出現(xiàn)過(guò)）。

SELECT ARRAY1 <@ ARRAY2;  

-- 返回 true

&& ：重疊。判斷兩個(gè)數(shù)組是否有共同的元素（只要有一個(gè)元素相同即為 true）。sq

SELECT ARRAY1 && ARRAY2;  

-- 返回 true

?? 提示：在包含操作符（@> 和 <@）中，重復(fù)的元素值不做特殊處理。例如 ARRAY 和 ARRAY 會(huì)被認(rèn)為互相包含。

掌握這些操作符，配合上一輪提到的數(shù)組函數(shù)，就能在 PostgreSQL 中游刃有余地處理各種復(fù)雜的數(shù)組業(yè)務(wù)邏輯了

到此這篇關(guān)于PostgreSQL數(shù)組函數(shù)的使用的文章就介紹到這了,更多相關(guān)PostgreSQL數(shù)組函數(shù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！?

最新評(píng)論