# PostgreSQL 陣列型別使用詳解

## PostgreSQL 陣列型別使用詳解

可能大家對 PostgreSQL 這個關係型資料庫不太熟悉，因為大部分人最熟悉的，公司用的最多的是 MySQL

我們先對 PostgreSQL 資料庫 （下面簡稱 `PG`）簡單的介紹一下，以後有機會，再單獨寫一篇專門介紹 pgSql 的文章

> The World's Most Advanced Open Source Relational Database

這是 `PG` 官網對自己的介紹，是的，你沒有看錯，“世界上最先進的開源關係型資料庫”。一段嚴重違反我國廣告法的話，上一個敢那麼叫囂的技術是 `PHP`，“世界上最好的語言”，然後這句話就成了碼農界最廣為人知的梗，存在於各種笑料中。

不過 `PG` 並沒有因為這樣明目張膽地自吹自擂而遭到什麼抨擊或調侃，事實上，無論在務實的碼農界，抑或是講究章法的學術界，人們對 `PG` 都是讚許有加，`PG` 是完全當得起這句話的。

### 下面列出一些 PostgreSQL 的特點

- PostgreSQL 是一種功能非常齊全關係型資料庫，由加州大學計算機系開發

- PostgreSQL 開源協議是類 BSD 的自有協議 ，這是一種非常友好的協議，不論是商用還是自用，或者修改程式碼再起個名拿來賣錢，都沒有任何風險

- PostgreSQL 支援的資料型別非常多，除了常用的，還有`列舉型別` , `幾何型別` ,`UUID型別 `， `json型別` , `陣列型別` 等，其中`陣列型別` 也是本篇文章的目的，介紹其中陣列型別的使用

- PostgreSQL 成立時對標的資料庫是 `Oracle` 資料庫，所有 PostgreSQL 的功能和效能是非常強的。

- PostgreSQL 對複雜 SQL 的執行，要好於 MySql

..................

還有很多的特性，這裡只簡單的寫幾個，上面的幾個特點也是我非常在意的，之所` www.helloworld.net` 此次改版把 Mysql 換成了 PostgreSQL , 就是有這些原因。

之前很多人問過，`hellworld開發者社群` 改版用到了哪些技術棧，其中之一，就是把 `Mysql `換成了 `PostgreSQL`

在改版的過程中，所有的表全部重新設計，這對於後端來說，是一個極其需要勇氣的決定，好在我們堅持下來了

在改的過程中，其中有這樣一個場景：

一篇部落格，有多個標籤 ，比如 一個部落格，有`多執行緒` , `併發` , `執行緒池` 這三個標籤

對於這樣的需求，我們可以分析一下

- 一篇部落格，有多個標籤
- 一個標籤，也可有對應多篇部落格

這樣就形成了 `多對多` 的關係，建表的話，就會有一張關聯表，大部分會想到這樣建表

部落格表： `blog`

標籤表： `tag`

標籤部落格表： `tag_blog`

**其中各表的欄位，如下（簡單起見，只列出最少的列）：**

`blog` 表：

- id 數字型別，部落格的 id, 自增長的主鍵
- title 字串型別，部落格的標題

`tag` 表：

- id 數字型別，標籤的 id , 自增長的主鍵
- name 字串型別，標籤的名字

`tag_blog` ：

- id 數字型別， 自增長的主鍵
- tag_id 標籤 id (對應 tag 表中的 id)
- blog_id 部落格 id (對應 blog 表中的 id)

上面這個部落格標籤需求，需要 3 張表，我們知道，PostgreSQL 的列的型別是支援`陣列型別`的

我們是不是可以最佳化一下上面的需求，把 3 張表變成 1 張表

只保留一張 **blog** 表，在 **blog** 表中增加一列 **tags** ， 類似就是 **text[ ]**

**新的部落格表欄位如下：**

`blog` 表：

- id 數字型別 (bigint)，部落格的 id, 自增長的主鍵
- title 字串型別 (text)，部落格的標題
- tags 字串陣列型別 (text [] )

為了方便大家測試，建表 SQL 如下

```sql
CREATE TABLE IF NOT EXISTS public.blog
(
    id bigint NOT NULL DEFAULT nextval('blog_id_seq'::regclass),
    title text COLLATE pg_catalog."default",
    tags text[] COLLATE pg_catalog."default",
    CONSTRAINT blog_pkey PRIMARY KEY (id)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.blog
    OWNER to postgres;
```

下面我們針對 tags 欄位作一些基本操作

### 陣列型別的基本操作

### 1 查詢

現在表中沒有資料，我們查詢一下看看

```
select * from blog
```

結果如下： ![image-20221021201052272](https://img-hello-world.oss-cn-beijing.aliyuncs.com/imgs/b0e98efeb17c40eb886cf9f38cae4f02.png)

### 2 插入資料

插入一條記錄，標題是 `www.helloworld.net` , 對應的標籤有 3 個，分別是 `helloworld` , `技術` , `社群`

```sql
insert into blog (title,tags) values('www.helloworld.net','{"helloworld","技術","社群"}')
```

再次查詢

```sql
select * from blog
```

結果如下： ![image-20221021201706232](https://img-hello-world.oss-cn-beijing.aliyuncs.com/imgs/88133026c53021962c33b663ed333de7.png)

可以看到，已經有了一條資料了，tags 陣列裡面有 3 個元素，分別是 `helloworld` , `技術` , `社群`

我們再次插入兩條資料，方便我們測試

```sql
insert into blog (title,tags) values('www.juejin.im','{"掘金","技術","開發者"}');
insert into blog (title,tags) values('www.oschina.net','{"開源中國","oschina","開源"}');
```

查詢結果如下：

![image-20221021202041981](https://img-hello-world.oss-cn-beijing.aliyuncs.com/imgs/2fdcffa4275957aef06b77678b079747.png)

### 3 條件查詢

#### 3.1 查詢標籤中有 `技術`標籤的部落格，語法 `select * from blog where 'xx' = any(陣列欄位)`

sql 語句如下

```sql
select * from blog where '技術'= any(tags)
```

查詢結果如下：

![image-20221021202412709](https://img-hello-world.oss-cn-beijing.aliyuncs.com/imgs/53f14cd37df2420011e62ec75926a431.png)

#### 3.2 查詢標籤中有 `helloworld` 標籤或者有 `開源中國`標籤的部落格

sql 語句如下：

```sql
select * from blog where 'helloworld'= any(tags) or '開源中國' = any(tags)
```

結果如下： ![image-20221021203227244](https://img-hello-world.oss-cn-beijing.aliyuncs.com/imgs/9ea8b2c18998eeb7a87d58765228be44.png)

### 4 更新

#### 4.1 更新標籤的名稱

我們將 id = 1 的記錄的 tags 陣列中， `社群`改成`開發者社群`

注意：pg 中陣列型別，索引是從 1 開始，我們將 id = 1 的記錄，`社群`元素索引為 3，修改語法為： `update 表名 set 欄位[index] = 'xx' where id=1`

sql 如下:

```sql
update blog set tags[3] = '開發者社群' where id=1 
```

再次查詢，結果如下：

![image-20221021203813747](https://img-hello-world.oss-cn-beijing.aliyuncs.com/imgs/29677582a4c8ccb5040ed48998dcd10a.png)

可以發現，透過 `tags[3] = '開發者社群'` ，成功的把 `社群`修改成了 `開發者社群`

#### 4.2 新增一個標籤

我們把 id=1 的記錄，標籤再增加一個 `程式設計師`標籤

可以使用 PostgreSQL 的 `array_append` 函式

使用方法如下：

sql 寫法如下：

```sql
update blog set tags = array_append(tags, '程式設計師'::text) where id=1
```

再次查詢結果如下：

![image-20221021204437307](https://img-hello-world.oss-cn-beijing.aliyuncs.com/imgs/fe2ba796fa178a67ba8f146851756f8b.png)

### 5 刪除

我們刪除標籤

把 id= 3 的記錄中的標籤，刪除`開源`

sql 如下：

```sql
update blog set tags = array_remove(tags, '開源'::text) where id=3
```

執行後，再次查詢，如下：

![image-20221021204731808](https://img-hello-world.oss-cn-beijing.aliyuncs.com/imgs/b142eeeb600c6ee18e09d467938dbe66.png)

## 總結

以上就是關於 PostgreSQL 的陣列型別的常見的用法，至於其它的用法，大家可以看一下官方文件，再結合本例的 SQL 寫法

應該很容易就能掌握，博主的環境都是搭建在[cnaaa伺服器](https://www.cnaaa.com/)上的。

以上的需求，其實實際應用中並不是適合用陣列型別解決，陣列適合的場景，操作，互動不多，不太重要，可以用。

此例只是方便說明用法，具體實際中怎麼用，大家還需要結合自己的業務需求，靈活選擇

PostgreSQL 陣列型別使用詳解

相關文章