PostgreSQL 数组类型使用详解

3A网络
• 阅读 532

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

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

结果如下: PostgreSQL 数组类型使用详解

2 插入数据

插入一条记录,标题是 www.helloworld.net , 对应的标签有 3 个,分别是 helloworld , 技术 , 社区

insert into blog (title,tags) values('www.helloworld.net','{"helloworld","技术","社区"}')

再次查询

select * from blog

结果如下: PostgreSQL 数组类型使用详解

可以看到,已经有了一条数据了,tags 数组里面有 3 个元素,分别是 helloworld , 技术 , 社区

我们再次插入两条数据,方便我们测试

insert into blog (title,tags) values('www.juejin.im','{"掘金","技术","开发者"}');
insert into blog (title,tags) values('www.oschina.net','{"开源中国","oschina","开源"}');

查询结果如下:

PostgreSQL 数组类型使用详解

3 条件查询

3.1 查询标签中有 技术标签的博客,语法 select * from blog where 'xx' = any(数组字段)

sql 语句如下

select * from blog where '技术'= any(tags)

查询结果如下:

PostgreSQL 数组类型使用详解

3.2 查询标签中有 helloworld 标签或者有 开源中国标签的博客

sql 语句如下:

select * from blog where 'helloworld'= any(tags) or '开源中国' = any(tags)

结果如下: PostgreSQL 数组类型使用详解

4 更新

4.1 更新标签的名称

我们将 id = 1 的记录的 tags 数组中, 社区改成开发者社区

注意:pg 中数组类型,索引是从 1 开始,我们将 id = 1 的记录,社区元素索引为 3,修改语法为: update 表名 set 字段[index] = 'xx' where id=1

sql 如下:

update blog set tags[3] = '开发者社区' where id=1 

再次查询,结果如下:

PostgreSQL 数组类型使用详解

可以发现,通过 tags[3] = '开发者社区' ,成功的把 社区修改成了 开发者社区

4.2 添加一个标签

我们把 id=1 的记录,标签再增加一个 程序员标签

可以使用 PostgreSQL 的 array_append 函数

使用方法如下:

sql 写法如下:

update blog set tags = array_append(tags, '程序员'::text) where id=1

再次查询结果如下:

PostgreSQL 数组类型使用详解

5 删除

我们删除标签

把 id= 3 的记录中的标签,删除开源

sql 如下:

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

执行后,再次查询,如下:

PostgreSQL 数组类型使用详解

总结

以上就是关于 PostgreSQL 的数组类型的常见的用法,至于其它的用法,大家可以看一下官方文档,再结合本例的 SQL 写法

应该很容易就能掌握,博主的环境都是搭建在cnaaa服务器上的。

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

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

点赞
收藏
评论区
推荐文章
九路 九路
2年前
PostgreSQL 数组类型使用详解
PostgreSQL数组类型使用详解可能大家对PostgreSQL这个关系型数据库不太熟悉,因为大部分人最熟悉的,公司用的最多的是MySQL我们先对PostgreSQL数据库(下面简称PG)简单的介绍一下,以后有机会,再
待兔 待兔
3年前
PostgreSQL介绍以及如何开发框架中使用PostgreSQL数据库
最近准备下PostgreSQL数据库开发的相关知识,本文把总结的PPT内容通过博客记录分享,本随笔的主要内容是介绍PostgreSQL数据库的基础信息,以及如何在我们的开发框架中使用PostgreSQL数据库,希望大家多多提意见。1、PostgreSQL数据库介绍PostgreSQL是以加州大学伯克利分校计算机系开发的POSTGRES,现在已经更
待兔 待兔
3年前
阿里云德哥:PostgreSQL 数据库的前世今生
内容摘要PostgreSQL是以加州大学伯克利分校计算机系开发的Posrgres,现在已经更名为PostgreSQL。它是一个自由的对象关系数据库服务器(数据库管理系统),它在灵活的BSD风格许可证下发行。PostgreSQL中国社区发起人之一Digoal为我们带来PostgreSQL前世今生、社区理念以及阿里云Postgr
Stella981 Stella981
3年前
PostgreSQL Freeze 风暴预测续
标签PostgreSQL,Freeze,风暴背景PostgreSQL目前默认的存储引擎,事务可见性需要依赖行头的事务号,因为事务号是32位的,会循环使用。在一条记录产生后,如果再次经历了20亿个事务,必须对其进行freeze,否则数据库会认为这条记录是未来事务产生的(可见性判断)。因此FREEZE操作是数据库在32
Stella981 Stella981
3年前
PostgreSQL 学习一 安装和入门
自从MySQL被Oracle收购以后,PostgreSQL逐渐成为开源关系型数据库的首选。  本文介绍PostgreSQL的安装和基本用法,供初次使用者上手。以下内容基于Debian操作系统,其他操作系统实在没有精力兼顾,但是大部分内容应该普遍适用。   !640x220(http://static.oschina.net/uploads/
Wesley13 Wesley13
3年前
Mysql、Oracle、PostgreSql数据库索引失效场景详细讲解
Mysql、Oracle、PostgreSql数据库索引失效场景详细讲解前言前言Mysql、Oracle、PostgreSql数据库索引失效场景详细讲解。废话不多说直接贴:1、任何计算、函数、类型转换2、!、<3、ISNULL或者ISNOTNULL。类似导致索引失效的还有NOTIN
Stella981 Stella981
3年前
PostgreSQL入门
PostgreSQL入门安装与基本使用(Ubuntu)PostgreSQL是一个免费的对象关系数据库服务器(ORDBMS),号称是"世界上最先进的开源关系型数据库"。PostgreSQL是以加州大学计算机系开发的POSTGRES4.2版本为基础的对象关系型数据库。今天在Ubuntu系统上,我们一起来安装并简单使用一下PostgreS
Stella981 Stella981
3年前
Postgresql 数据库控制解析(一)
前面我们通过“探索postgresql数据库(一)”和“探索postgresql数据库(二)”基本了解了postgresql数据库。接下来本文开始介绍下数据库控制(servercontrol)。。一、手动开启数据库(Startingthedatabaseservermanually)默认postgresql会随着系统自动启动,不过
Stella981 Stella981
3年前
PostgreSQL 数据库备份与恢复 pd_dump pg_restore
备份PG数据库生成的文件可以有两种,一种是SQL文件,一种是二进制文件,二进制文件只能使用pg\_restore进行恢复。PostgreSQL数据库操作简要说明PostgreSQL数据库版本psqlversionpsql(PostgreSQL)9.1.3下面是在linux下的操作,在windows下面将sup
Stella981 Stella981
3年前
Postgresql部署及简单操作
PostgreSQL是一个功能强大的开源对象关系数据库管理系统(ORDBMS),在开源数据库使用上与MySQL各领风骚。但也有不少人质疑postgresql的未来,正所谓,赞扬或批判一种数据库都必须先了解它,然后才可有话语权。为了更多的了解postgresql,我们就先部署一套实例作为了解它的基础。!(https://oscimg.oschina