Categories: 資料庫

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

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

PostgreSQL 的陣列資料

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

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

INTEGER[] 整數陣列。
VARCHAR(256)[] 字串陣列。
INTEGER[][] 二維整數陣列。
INTEGER[4] 長度為 4 的整數陣列。
INTEGER ARRAY 整數陣列。
INTEGER ARRAY[4] 長度為 4 的整數陣列。
PostgreSQL 資料庫目前對於陣列的實作上並沒有任何陣列長度與維度的限制,在建立資料表時所指定的陣列長度與維度都只是標示性質,實質上並沒有差異。

建立含有陣列的資料表

建立一張用來儲存員工聯絡電話的資料表,其中聯絡電話欄位(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-4440956-555-666 的員工(也就是判斷 0934-333-4440956-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)

參考資料

Share
Published by
Office Guide
Tags: PostgreSQL

Recent Posts

Python 使用 PyAutoGUI 自動操作滑鼠與鍵盤

本篇介紹如何在 Python ...

1 年 ago

Ubuntu Linux 以 WireGuard 架設 VPN 伺服器教學與範例

本篇介紹如何在 Ubuntu ...

1 年 ago

Linux 網路設定 ip 指令用法教學與範例

本篇介紹如何在 Linux 系...

1 年 ago

Linux 以 Cryptsetup、LUKS 加密 USB 隨身碟教學與範例

介紹如何在 Linux 系統中...

1 年 ago