介紹如何在 PostgreSQL 資料庫中使用各類型的陣列(array)儲存一連串的資料。
若在 PostgreSQL 資料庫中想儲存陣列的資料,可以使用陣列類型的資料類型,陣列主要適用於不必與其他資料表連結的狀況。
PostgreSQL 資料庫中的各種資料類型,只要加上中括號([]
)之後,就會變成陣列的類型,或是使用 ARRAY
關鍵字,以下是一些範例。
INTEGER[] |
整數陣列。 |
VARCHAR(256)[] |
字串陣列。 |
INTEGER[][] |
二維整數陣列。 |
INTEGER[4] |
長度為 4 的整數陣列。 |
INTEGER ARRAY |
整數陣列。 |
INTEGER ARRAY[4] |
長度為 4 的整數陣列。 |
建立一張用來儲存員工聯絡電話的資料表,其中聯絡電話欄位(contact
)可能會有很多筆資料,所以採用陣列的方式儲存:
-- 建立員工資料表 CREATE TABLE employees ( id SERIAL PRIMARY KEY, -- 序號,主鍵 name VARCHAR(255), -- 姓名,可變長度文字 contact TEXT[] -- 聯絡電話,字串陣列 );
CREATE TABLE
查看 employees
資料表結構:
-- 查看 employees 資料表結構 \d employees
Table "public.employees" Column | Type | Collation | Nullable | Default ---------+------------------------+-----------+----------+--------------------------------------- id | integer | | not null | nextval('employees_id_seq'::regclass) name | character varying(255) | | | contact | text[] | | | Indexes: "employees_pkey" PRIMARY KEY, btree (id)
在插入資料時,可用 ARRAY
關鍵字搭配中括號來表示陣列資料:
-- 插入員工聯絡電話資料 INSERT INTO employees (name, contact) VALUES ('Alice', ARRAY ['(02)-1234-4567', '0975-123-456']);
INSERT 0 1
也可以使用大括號({}
)來表示陣列資料:
-- 插入員工聯絡電話資料 INSERT INTO employees (name, contact) VALUES ('Steven', '{"(03)-4321-9876", "0912-345-678"}');
INSERT 0 1
也可以插入不同長度的陣列資料:
-- 不同長度的陣列資料 INSERT INTO employees (name, contact) VALUES ('Jackson', ARRAY ['(04)-1111-2222']), ('Lily', ARRAY ['(05)-3333-4444', '0934-333-444', '0956-555-666']);
INSERT 0 2
查詢剛加入的所有員工聯絡電話資料:
-- 查詢員工聯絡電話資料 SELECT * FROM employees;
id | name | contact ----+---------+-------------------------------------------- 1 | Alice | {(02)-1234-4567,0975-123-456} 2 | Steven | {(03)-4321-9876,0912-345-678} 3 | Jackson | {(04)-1111-2222} 4 | Lily | {(05)-3333-4444,0934-333-444,0956-555-666} (4 rows)
陣列內的元素可以使用中括號搭配索引(從 1
起算)來取用。例如查詢每位員工第一筆聯絡電話資料:
-- 查詢每位員工第一筆聯絡電話資料 SELECT name, contact[1] FROM employees;
name | contact ---------+---------------- Alice | (02)-1234-4567 Steven | (03)-4321-9876 Jackson | (04)-1111-2222 Lily | (05)-3333-4444 (4 rows)
查詢第二筆聯絡電話資料為 0934-333-444
的員工:
-- 查詢第二筆聯絡電話資料為 0934-333-444 的員工 SELECT name FROM employees WHERE contact[2] = '0934-333-444';
name ------ Lily (1 row)
若要比對整個陣列中是否含有特定元素值,可以搭配 ANY
運算來查詢。例如查詢聯絡電話資料含有 0912-345-678
的員工:
-- 查詢聯絡電話資料含有 0912-345-678 的員工 SELECT name FROM employees WHERE '0912-345-678' = ANY(contact);
name -------- Steven (1 row)
若要進行多筆陣列元素資料的比對,可以使用 @>
、<@
或 &&
陣列比較運算子(其他陣列運算子可參考 PostgreSQL 的文件):
運算 | 說明 |
---|---|
A 陣列 @> B 陣列 |
A 陣列包含 B 陣列。 |
A 陣列 <@ B 陣列 |
A 陣列包含於 B 陣列。 |
A 陣列 && B 陣列 |
A 陣列與 B 陣列是否有交集。 |
例如查詢聯絡電話資料同時含有 0934-333-444
與 0956-555-666
的員工(也就是判斷 0934-333-444
與 0956-555-666
是否都包含於員工的聯絡電話資料陣列中):
-- 查詢聯絡電話資料含有 0934-333-444 與 0956-555-666 的員工 SELECT name FROM employees WHERE ARRAY['0934-333-444','0956-555-666'] <@ contact;
name ------ Lily (1 row)
陣列的資料可以使用 unnest
展開,例如:
-- 展開所有員工聯絡電話資料 SELECT name, unnest(contact) FROM employees;
name | unnest ---------+---------------- Alice | (02)-1234-4567 Alice | 0975-123-456 Steven | (03)-4321-9876 Steven | 0912-345-678 Jackson | (04)-1111-2222 Lily | (05)-3333-4444 Lily | 0934-333-444 Lily | 0956-555-666 (8 rows)
更新員工聯絡電話資料:
-- 更新員工聯絡電話資料 UPDATE employees SET contact[3] = '0977-999-888' WHERE id = 4;
UPDATE 1
查詢更新後的資料:
-- 查詢更新後的資料 SELECT * FROM employees WHERE id = 4;
id | name | contact ----+------+-------------------------------------------- 4 | Lily | {(05)-3333-4444,0934-333-444,0977-999-888} (1 row)