PostgreSQL 資料庫陣列 Arrays 資料類型使用教學與範例

介紹如何在 PostgreSQL 資料庫中使用各類型的陣列（array）儲存一連串的資料。

PostgreSQL 的陣列資料

若在 PostgreSQL 資料庫中想儲存陣列的資料，可以使用陣列類型的資料類型，陣列主要適用於不必與其他資料表連結的狀況。

PostgreSQL 資料庫中的各種資料類型，只要加上中括號（[]）之後，就會變成陣列的類型，或是使用 ARRAY 關鍵字，以下是一些範例。

建立含有陣列的資料表

建立一張用來儲存員工聯絡電話的資料表，其中聯絡電話欄位（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 的文件）：

例如查詢聯絡電話資料同時含有 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)

參考資料

• PostgreSQL Documentation：Arrays
• Guru99：PostgreSQL Array: Functions, Type, Example
• PopSQL：How to Query Arrays in PostgreSQL