• 跳至主要導覽
  • 跳至主要內容
  • 跳至主要資訊欄
Office 指南

Office 指南

辦公室工作實用教學

  • Excel
  • Word
  • PowerPoint
  • Windows
  • PowerShell
  • R

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-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

分類:資料庫 標籤:PostgreSQL

讀者互動方式

發佈留言 取消回覆

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

主要資訊欄

搜尋

近期文章

  • C++ 語言使用 Crypto++ 實作 RSA 數位簽章教學與範例
  • C++ 語言使用 Crypto++ 實作 RSA-OAEP 搭配 SHA256 加密教學與範例
  • C++ 語言使用 Crypto++ 實作 AES 加密、解密、認證加密教學與範例
  • C++ 語言使用 Crypto++ 實作 MD5、SHA1、SHA2、BLAKE2 雜湊教學與範例
  • Ubuntu Linux 安裝、使用 Crypto++ 加密函式庫教學與範例
  • C 語言使用 OpenSSL 實作橢圓曲線 ECDH 金鑰交換教學與範例
  • Python 以 eciespy 實作 ECC 非對稱式加密方法教學與範例
  • C 語言使用 OpenSSL 實作 PBKDF2 教學與範例

推薦網站

  • Udemy 線上教學課程
  • Coursera 線上教學課程

關注本站

  • 電子郵件
  • Facebook

公益

  • 家扶基金會
  • 台灣世界展望會
  • Yahoo 奇摩公益
  • igiving 公益網
  • 兒福聯盟

Copyright © 2021 · Office Guide