PostgreSQL总结（2）特殊数据类型查询 - array

最新推荐文章于 2024-10-11 23:41:33 发布
最新推荐文章于 2024-10-11 23:41:33 发布
阅读量7k 收藏 6
点赞数 2
分类专栏： Database 文章标签： postgresql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/cqdz_dj/article/details/52355396
版权

回顾

        前面我们介绍了PostgreSQL的安装和对数据库的一些简单操作。我们知道，对于数据库的大部分操作都是查询操作，PostgreSQL具有丰富的数据类型，有一些特殊的数据类型操作与其他关系型数据库有所区别，这里我们来介绍一下对于这些数据类型的查询。

array查询

        若Table_A和Table_B是多对多的关系，在其他关系型数据库中，我们会再创建一张表来保存Table_A与Table_B之间的关系，因为其他关系型数据库无法使用一条记录来保存这种关系。而在PostgreSQL中，则可以在Table_A和Table_B中分别使用一个数组来保存这种关系，就不需要额外的再创建一张表：在 Table_A 中字段 Column_A_B 是数组类型，Column_A_B 数组中的元素作为 Table_B 的外键；在 Table_B 中字段 Column_B_A 是数组类型，Column_B_A 数组中的元素作为 Table_A 的外键。

        为了更加直观，我们举例说明。有两张表：学生表(student)和课程表(course)，一个学生可以选多门课程，一门课程被多个学生选。

-- 学生表
CREATE TABLE student
(
    id bigserial, --主键
    name text, --姓名
    courses bigint[], --所选课程
    CONSTRAINT student_pk PRIMARY KEY(id) 
);

-- 课程表
CREATE TABLE course
(
    id bigserial, --主键
    name text, --课程名称
    e_name text, --课程英文名称
    students bigint[], --选该课程的学生
    CONSTRAINT course_pk PRIMARY KEY(id) 
);

在student表和course表中分别插入几条数据：

-- student插入四条数据
INSERT INTO student
(name, courses)
VALUES
('ZhangSan', array[1,2]),
('LiSi', array[1,3]),
('WangWu', array[2,3]),
('ZhaoLiu', array[1,2,3]);

-- course插入三条数据
INSERT INTO course
(name, e_name, students)
VALUES
('语文', 'Chinese', array[1,2,4]),
('数学', 'Math', array[1,3,4]),
('英语', 'English', array[2,3,4]);

插入结果如下：

=> SELECT * FROM student;
 id |   name   | courses 
----+----------+---------
  1 | ZhangSan | {1,2}
  2 | LiSi     | {1,3}
  3 | WangWu   | {2,3}
  4 | ZhaoLiu  | {1,2,3}
(4 rows)

=> SELECT * FROM course;
 id | name | e_name  | students 
----+------+---------+----------
  1 | 语文 | Chinese | {1,2,4}
  2 | 数学 | Math    | {1,3,4}
  3 | 英语 | English | {2,3,4}
(3 rows)

        目前在student表和course表中都只是保存了其关联的另一张表的id。那么问题来了，如果想查看其关联的表中的其他信息呢？如：想查看”ZhangSan”选了的课程名称有哪些？这就需要使用PostgreSQL中的关键词ANY了（或者使用”&&”）

SELECT s.id sid, c.id cid, s.name sname, s.courses, c.name cname, c.e_name ce_name
FROM student s
LEFT JOIN course c ON c.id = ANY(s.courses)
ORDER BY s.id;

或者：

SELECT s.id sid, c.id cid, s.name sname, s.courses, c.name cname, c.e_name ce_name
FROM student s
LEFT JOIN course c ON array[c.id] && s.courses
ORDER BY s.id;

查询结果如下：

 sid | cid |  sname   | courses | cname | ce_name 
-----+-----+----------+---------+-------+---------
   1 |   1 | ZhangSan | {1,2}   | 语文  | Chinese
   1 |   2 | ZhangSan | {1,2}   | 数学  | Math
   2 |   1 | LiSi     | {1,3}   | 语文  | Chinese
   2 |   3 | LiSi     | {1,3}   | 英语  | English
   3 |   2 | WangWu   | {2,3}   | 数学  | Math
   3 |   3 | WangWu   | {2,3}   | 英语  | English
   4 |   1 | ZhaoLiu  | {1,2,3} | 语文  | Chinese
   4 |   2 | ZhaoLiu  | {1,2,3} | 数学  | Math
   4 |   3 | ZhaoLiu  | {1,2,3} | 英语  | English

        但这个结果好像跟我们预期的还有那么一点点不一样：我们希望的是，关联后的结果条数和student表相同(如分页查询学生所选课程)，课程名称也是一个数组并且和课程的id数组对应。在这里，我们做一些改动，查询语句如下：

SELECT s.id sid, s.name sname, s.courses, 
    array(SELECT name FROM course WHERE id = ANY(s.courses)) cname,
    array(SELECT e_name FROM course WHERE id = ANY(s.courses)) ce_name
FROM student s
ORDER BY s.id;

查询结果如下：

 sid |  sname   | courses |      cname       |        ce_name         
-----+----------+---------+------------------+------------------------
   1 | ZhangSan | {1,2}   | {语文,数学}      | {Chinese,Math}
   2 | LiSi     | {1,3}   | {语文,英语}      | {Chinese,English}
   3 | WangWu   | {2,3}   | {数学,英语}      | {Math,English}
   4 | ZhaoLiu  | {1,2,3} | {语文,数学,英语} | {Chinese,Math,English}
(4 rows)

        是不是这样的结果更能让人接受？但是问题又来了：如果course表中有很多个字段，都需要像上面那样合并成一个，那岂不是要有N多个子查询？这样会不会效率很低？关于这个问题，笔者也没有什么太好的解决方案，欢迎大家提出更好的解决方案。目前的处理方式是创建一个视图，将这些信息组合的耗时操作集中处理，不会影响用户。视图的DDL如下：

CREATE VIEW course_info AS
SELECT 
    DISTINCT courses,
    array(SELECT name FROM course WHERE id = ANY(s.courses)) cname,
    array(SELECT e_name FROM course WHERE id = ANY(s.courses)) ce_name
FROM student s;

        那么，在进行关联查询的时候，student表就直接和视图course_info进行关联，查询语句如下：

SELECT s.id sid, s.name sname, s.courses, ci.cname,ci.ce_name
FROM student s
LEFT JOIN course_info ci ON s.courses = ci.courses
ORDER BY s.id;

        我们来看看使用视图作为关联和不使用的情况的执行效率对比：

  • student表与course表关联：
EXPLAIN ANALYZE SELECT s.id sid, s.name sname, s.courses, 
    array(SELECT name FROM course WHERE id = ANY(s.courses)) cname,
    array(SELECT e_name FROM course WHERE id = ANY(s.courses)) ce_name
FROM student s
ORDER BY s.id;

执行计划运行结果如下：


                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=32444.80..32446.80 rows=800 width=72) (actual time=0.114..0.115 rows=4 loops=1)
   Sort Key: s.id
   Sort Method: quicksort  Memory: 26kB
   ->  Seq Scan on student s  (cost=0.00..32406.22 rows=800 width=72) (actual time=0.051..0.101 rows=4 loops=1)
         SubPlan 1
           ->  Bitmap Heap Scan on course  (cost=9.59..20.24 rows=10 width=32) (actual time=0.010..0.011 rows=2 loops=4)
                 Recheck Cond: (id = ANY (s.courses))
                 ->  Bitmap Index Scan on course_pk  (cost=0.00..9.58 rows=10 width=0) (actual time=0.005..0.005 rows=2 loops=4)
                       Index Cond: (id = ANY (s.courses))
         SubPlan 2
           ->  Bitmap Heap Scan on course course_1  (cost=9.59..20.24 rows=10 width=32) (actual time=0.005..0.005 rows=2 loops=4)
                 Recheck Cond: (id = ANY (s.courses))
                 ->  Bitmap Index Scan on course_pk  (cost=0.00..9.58 rows=10 width=0) (actual time=0.002..0.002 rows=2 loops=4)
                       Index Cond: (id = ANY (s.courses))
 Total runtime: 0.168 ms
(15 rows)
  • student表与course_info视图关联:
EXPLAIN ANALYZE SELECT s.id sid, s.name sname, s.courses, ci.cname,ci.ce_name
FROM student s
LEFT JOIN course_info ci ON s.courses = ci.courses
ORDER BY s.id;

执行计划运行结果如下：

                                                               QUERY PLAN                                                                  
----------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=40608.35..40610.35 rows=800 width=136) (actual time=0.076..0.076 rows=4 loops=1)
   Sort Key: s.id
   Sort Method: quicksort  Memory: 26kB
   ->  Hash Right Join  (cost=32440.22..40569.78 rows=800 width=136) (actual time=0.066..0.071 rows=4 loops=1)
         Hash Cond: (s_1.courses = s.courses)
         ->  HashAggregate  (cost=32412.22..40511.28 rows=200 width=32) (actual time=0.047..0.047 rows=4 loops=1)
               ->  Seq Scan on student s_1  (cost=0.00..32406.22 rows=800 width=32) (actual time=0.025..0.043 rows=4 loops=1)
                     SubPlan 1
                       ->  Bitmap Heap Scan on course  (cost=9.59..20.24 rows=10 width=32) (actual time=0.005..0.005 rows=2 loops=4)
                             Recheck Cond: (id = ANY (s_1.courses))
                             ->  Bitmap Index Scan on course_pk  (cost=0.00..9.58 rows=10 width=0) (actual time=0.002..0.002 rows=2 loops=4)
                                   Index Cond: (id = ANY (s_1.courses))
                     SubPlan 2
                       ->  Bitmap Heap Scan on course course_1  (cost=9.59..20.24 rows=10 width=32) (actual time=0.002..0.002 rows=2 loops=4)
                             Recheck Cond: (id = ANY (s_1.courses))
                             ->  Bitmap Index Scan on course_pk  (cost=0.00..9.58 rows=10 width=0) (actual time=0.001..0.001 rows=2 loops=4)
                                   Index Cond: (id = ANY (s_1.courses))
         ->  Hash  (cost=18.00..18.00 rows=800 width=72) (actual time=0.008..0.008 rows=4 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 1kB
               ->  Seq Scan on student s  (cost=0.00..18.00 rows=800 width=72) (actual time=0.003..0.004 rows=4 loops=1)
 Total runtime: 0.116 ms

        我们可以看到，student表直接与course表关联查询，执行需要0.168 ms，与course_info视图进行关联查询，执行需要0.116 ms。对于关联信息越多的情况，效率提升越明显。

itcqdzdj
关注
专栏目录
【PostgreSQL教程】PostgreSQL 数据类型
No8g攻城狮的博客
07-20 1169
我们将讨论 PostgreSQL 的数据类型，数据类型是我们在创建表的时候为每个字段设置的。PostgreSQL提 供了丰富的数据类型。用户可以使用 CREATE TYPE 命令在数据库中创建新的数据类型。PostgreSQL 的数据类型有很多种，下面我们具体来说明。
2.00003《Postgresql内幕探索》走读 之 查询优化
四库全书的酷
03-04 416
在PostgreSQL中，尽管9.6版本中实现的并行查询使用了多个后台工作进程，但一个后端进程基本上处理连接的客户端发出的所有查询。 解析器 Parser解析器从明文的SQL语句生成解析树。 分析仪/分析仪 Analyzer分析器/分析器对解析树进行语义分析，生成查询树。 重写器 Rewriter重写器使用存储在规则体系rules 计划员 Planner计划员从查询树中生成最有效执行的计划树。 执行者 Executor。
【PostgreSql】各种数据类型查询
凤凰涅槃
07-15 1102
目录数据类型array {18，19，20}jsonb 数据类型 array {18，19，20} // 查询年龄等于18的 SELECT * FROM A表 WHERE id=1 AND 18=ANY(age) // 查询年龄大于18的 SELECT * FROM A表 WHERE id=1 AND 18<ANY(age) jsonb 例子：content ={"name": 张三, "age": 57, "sex": "男"} SELECT * FROM A表 WHERE (content:
postgreSQL ARRAY 类型的使用
马金波
09-02 1万+
postgreSQL 的数组的使用
PostgreSQL：数组类型处理
最新发布
m0_72560900的博客
10-11 636
在 PostgreSQL 中，数组是一种非常有用的数据类型，可以存储多个值，并且提供了丰富的函数和操作符来处理数组。下面展示一些常见的处理数组数据的案例，包括数组的创建、插入、查询以及数组的常见操作。
Postgresql数据库中的Array类型字段官方文档说明
XiaoCai
03-09 4375
PostgreSQL允许将表的列定义为可变长多维数组。可以创建任何内置或用户定义的基类型、枚举类型、组合类型、范围类型或域的数组。
【PostgreSQL】从零开始:（三十）数据类型-Arrays数组类型
圈小圈DBA的博客
12-26 2127
数组是一种数据结构，可以容纳多个相同类型的元素。数组可以存储基本数据类型（如整数、浮点数等）或者对象类型（如字符串、自定义对象等）。在大多数编程语言中，数组有固定的大小，一旦声明后，大小不可改变。数组的元素可以通过索引访问，索引通常从0开始。数组可以用于存储和操作大量数据，提供了快速访问和修改元素的能力。有些编程语言还提供了一些方便的方法和函数来处理数组，如排序、查找、插入和删除元素等。
postgresql数组查询
z917185537的专栏
02-22 1万+
测试数据 CREATE TABLE arr_test ( name text, color varchar[], size varchar[] ); INSERT INTO arr_test VALUES ('adult_f','{"grey","black","white"}','{"s","m","l","xl","xxl"}'), ('adult_w','{"red","black","white","grey","blue"}','{"
PostgreSQL基本数据类型
AntdonYu的博客
06-19 4940
PostgreSQL 提供了丰富的数据类型。用户可以使用 CREATE TYPE 命令在数据库中创建新的数据类型。PostgreSQL 的数据类型被分为四种，分别是基本数据类型、复合数据类型、域和伪类型。 ​ 基本数据类型是数据库内置的数据类型，包括integer、char、varchar等数据类型。表6-1列出了`PostgreSQL提供的所有基本数据类型。复合数据类型是用户自己定义的，使用CREATE TYPE命令就能创建一个复合数据类型。域是一种特殊的基本数据类型，它由基本数据类型加上一个约束条
PostgreSQL数据类型大总结，一家老小都在这里
IT邦德
02-19 5562
本文对PostgreSQL数据类型做了很全面的总结，希望对大家有所帮助
PostgreSQL SQL 语言：数据类型
weixin_33712987的博客
08-17 2012
本文档为PostgreSQL 9.6.0文档，本转载已得到原译者彭煜玮授权。 1. 数字类型 数字类型由2、4或8字节的整数以及4或8字节的浮点数和可选精度小数组成。Table 8-2列出了所有可用类型。 Table 8-2. 数字类型 下面的几节详细描述这些类型。 1.1. 整数类型 类型smallint、integer和bigint存储各种范围的全...
PostgreSQL的数组
07-02
PostgreSQL的数组
PostgreSQL 查看数据表对应的数据类型
越努力 越幸运
12-17 5791
select column_name,data_type from information_schema.columns where table_name = 'icustays'
PostgreSQL Array 教程
neweastsun的专栏
07-04 7130
本文带你学习PostgreSQL Array 类型，包括如何操作数组类型以及一些常用函数。 Array类型 数组类型是 Postgresql 的重要角色之一。每种数据都有其伴随的数组类型，如integer 对应 integer[] ,character 对应 character[] 。针对自定义数据类型，PostgreSQL在后台也创建了对应的伴随数组类型。 PostgreSQL 支持任何有效的数据类型列为数组类型，包括内置类型、用户定义类型、枚举类型。下面示例，定义contacts 表，phone .
2.Postgresql--array
qq_24223159的博客
07-14 1135
array
postgres 数组
晨风
12-01 4862
1.来自：http://pgguide.lxneng.com/sexy/arrays.html Postgres 允许把字段定义为可变长度的数组. 数据的类型可以是内置的类型, 用户自定义的类型或者枚举类型. 在创建表时声明数组字段: CREATE TABLE rock_band (    name text,    members text[] ) 上面的语句将创
postgresql的查询函数(数组)
热门推荐
djskl的专栏
03-16 1万+
示例表： CREATE TABLE students ( sno character varying(32) NOT NULL, --学号 sname character varying(64) NOT NULL, --姓名 sage integer, --年龄 scid character varying(32), -- 班级id scname character vary
PostgreSQL数组使用
Jon_Celoon的博客
09-02 5065
本文搬运自https://my.oschina.net/Kenyon/blog/133974 开发的语言有数组的概念，对应于postgresql也有相关的数据字段类型，数组是英文array的翻译，可以定义一维，二维甚至更多维度，数学上跟矩阵很类似。在postgres里面可以直接存储使用，某些场景下使用很方便，也很强大。 环境： OS：CentOS 6.2 DB: PostgreSQL 9.2.41.数组的定义 不一样的维度元素长度定义在数据库中的实际存储都是一样的，数组元素的长度和类型必须要保持.
PostgreSQL Array 数组类型与 FreeSql 打出一套【组合拳】
dotnetCore的博客
06-07 1144
PostgreSQL 是世界公认的功能最强大的开源数据库，除了基础数据类型 int4/int8/varchar/numeric/timestamp 等，还支持 int4[]/int8[]/varchar[]/numeric[]/timestamp[] 数组类型、hstore 类型（类似 c# Dictionary）、gis 类型。选择一项技术一定是可以帮助我们提升效率，并且使用一定是非常简便的，才能让我们赚取更多时间，早点下班甚至上班摸鱼。这篇文章主要讲数组类型，如何与 Fr...
评论 3
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值