# 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/)上的。

以上的需求，其实实际应用中并不是适合用数组类型解决，数组适合的场景，操作，交互不多，不太重要，可以用。

此例只是方便说明用法，具体实际中怎么用，大家还需要结合自己的业务需求，灵活选择

ITPub博客

PostgreSQL 数组类型使用详解

最新文章