当前位置: 首页 > news >正文

《PostgreSQL 开发指南》第32篇 物化视图

物化视图概述

物化视图(Materialized View)是 PostgreSQL 提供的一个扩展功能,它是介于视图和表之间的一种对象。

物化视图和视图的最大区别是它不仅存储定义中的查询语句,而且可以像表一样存储数据。物化视图和表的最大区别是它不支持 INSERT、UPDATE、DELETE 以及 MERGE 语句,只能通过刷新物化视图进行数据的更新。

物化视图通过提前运行并存储查询结果,通常用于查询优化、数据仓库、数据集成等场景。

另外,PostgreSQL 目前不支持物化视图的实时更新,因此物化视图中的数据通常不是最新数据。

创建物化视图

PostgreSQL 使用 CREATE MATERIALIZED VIEW 语句创建视图:

CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] name
AS query
[ WITH [ NO ] DATA ];

其中,IF NOT EXISTS 可以避免重复创建一个已经存在的物化视图时产生错误;name 是物化视图的名称;query 是物化视图的查询语句。

WITH DATA 表示创建时填充物化视图中的数据,这个是默认选项;WITH NO DATA 表示创建时不填充数据,此时无法查询物化视图中的数据,需要执行 REFRESH MATERIALIZED VIEW 命令刷新物化视图数据之后才能查询。

💡虽然物化视图定义中的查询语句支持 ORDER BY 子句,但是不推荐使用。如果想要以指定顺序显示数据,应该在查询数据时明确指定排序字段,而不应该依赖表中的数据存储顺序。

以下语句创建了一个包含员工统计信息的物化视图:

CREATE MATERIALIZED VIEW emp_stat_mv
AS 
SELECT d.department_name, j.job_title, count(*)
FROM employees e 
JOIN departments d ON d.department_id = e.department_id 
JOIN jobs j ON j.job_id = e.job_id
GROUP BY d.department_name, j.job_title;

物化视图 emp_stat_mv 包含了按照部门和职位统计的员工数量。

我们可以直接查询 emp_stat_mv,获取员工统计信息:

SELECT * 
FROM emp_stat_mv
ORDER BY department_name;department_name |job_title                      |count|
----------------+-------------------------------+-----+
Accounting      |Public Accountant              |    1|
Accounting      |Accounting Manager             |    1|
Administration  |Administration Assistant       |    1|
Executive       |Administration Vice President  |    2|
Executive       |President                      |    1|
Finance         |Accountant                     |    5|
Finance         |Finance Manager                |    1|
Human Resources |Human Resources Representative |    1|
IT              |Programmer                     |    5|
Marketing       |Marketing Manager              |    1|
Marketing       |Marketing Representative       |    1|
Public Relations|Public Relations Representative|    1|
Purchasing      |Purchasing Clerk               |    5|
Purchasing      |Purchasing Manager             |    1|
Sales           |Sales Representative           |   29|
Sales           |Sales Manager                  |    5|
Shipping        |Stock Manager                  |    5|
Shipping        |Stock Clerk                    |   20|
Shipping        |Shipping Clerk                 |   20|

物化视图可以像表一样支持索引,实现约束和查询优化。例如,以下语句为 emp_stat_mv 创建了一个唯一索引:

CREATE UNIQUE INDEX uk_emp_stat_mv ON emp_stat_mv(department_name, job_title);

刷新物化视图

PostgreSQL 物化视图不会自动刷新数据,需要手动执行 REFRESH MATERIALIZED VIEW 语句:

REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name
[ WITH [ NO ] DATA ];

其中,CONCURRENTLY 可以支持刷新物化视图时其他连接的并发读取。使用该选项的前提是物化视图上至少存在一个唯一索引,而且不能是表达式索引或者部分索引。

WITH DATA 表示刷新时填充物化视图中的数据,这个是默认选项;WITH NO DATA 表示刷新时不填充数据,刷新后物化视图处于不可查询状态。

CONCURRENTLY 和 WITH NO DATA 选项不能一起使用。未填充数据的物化视图刷新时不支持 CONCURRENTLY 选项。

💡如果在物化视图定义的查询语句中指定了 ORDER BY 子句,刷新物化视图数据时不会保证数据仍然按照指定顺序进行存储。

例如,我们可以定期执行以下语句刷新物化视图 emp_stat_mv 中的数据:

REFRESH MATERIALIZED VIEW CONCURRENTLY emp_stat_mv;

或者执行以下语句清空物化视图 emp_stat_mv 中的数据:

REFRESH MATERIALIZED VIEW emp_stat_mv
WITH NO DATA;

修改物化视图

ALTER MATERIALIZED VIEW 语句可以修改物化视图的一些属性,例如名称、字段名等:

ALTER MATERIALIZED VIEW [ IF EXISTS ] name
RENAME [ COLUMN ] column_name TO new_column_name;ALTER MATERIALIZED VIEW [ IF EXISTS ] name
RENAME TO new_name;ALTER MATERIALIZED VIEW [ IF EXISTS ] name
SET SCHEMA new_schema;

第一个语法用于修改物化视图的字段名称,第二个语法用于修改物化视图的名称,第三个语法用于修改物化视图所在的模式。

例如,以下语句将物化视图 emp_stat_mv 的名称修改为 emp_stat_mv2:

ALTER MATERIALIZED VIEW emp_stat_mv 
RENAME TO emp_stat_mv2;

ALTER MATERIALIZED VIEW 语句不能修改物化视图定义中的查询语句;如果想要修改物化视图定义中的查询语句,需要删除并重建物化视图。

ALTER MATERIALIZED VIEW 语句还提供了其他的修改功能,具体可以参考官方文档。

删除物化视图

PostgreSQL 使用 DROP MATERIALIZED VIEW 语句删除物化视图:

DROP MATERIALIZED VIEW [ IF EXISTS ] name [ CASCADE | RESTRICT ];

其中,IF EXISTS 可以避免删除一个不存在的物化视图时产生错误;CASCADE 表示级联删除依赖于该物化视图的对象;RESTRICT 表示如果存在依赖对象则提示错误信息,这是默认值。

我们可以使用以下语句删除物化视图 emp_stat_mv2:

DROP MATERIALIZED VIEW emp_stat_mv2;

相关文章:

《PostgreSQL 开发指南》第32篇 物化视图

物化视图概述 物化视图(Materialized View)是 PostgreSQL 提供的一个扩展功能,它是介于视图和表之间的一种对象。 物化视图和视图的最大区别是它不仅存储定义中的查询语句,而且可以像表一样存储数据。物化视图和表的最大区别是它…...

【RocketMQ】快速入门

文章目录 消费模式同步消息异步消息单向消息延迟消息批量消息顺序消息事务消息Tag标签和Key键Tag的使用Key的使用 首先引入rocketmq的依赖 <dependency><groupId>org.apache.rocketmq</groupId><artifactId>rocketmq-client</artifactId><ve…...

AB跳转轮询:让你的独立站收款智能化

独立站在近两年成为跨境电商的热门布局之一&#xff0c;特别是在亚马逊封号潮后&#xff0c;许多卖家开始转向独立站运营。然而&#xff0c;在迅速发展的同时&#xff0c;也不可避免地出现了一些问题&#xff0c;比如很多卖家的资金经常被不同程度地冻结&#xff0c;好不容易出…...

所有用户都能使用sudo吗

是的&#xff0c;Linux系统中的普通用户可以通过配置访问 sudo 命令来获得超级用户&#xff08;root&#xff09;权限的临时访问权。这使得普通用户可以在需要时执行需要管理员权限的操作&#xff0c;而无需永久性地切换到超级用户账户。 通过 sudo 命令&#xff0c;系统管理员…...

【广州华锐视点】VR警务教育实训系统模拟真实场景进行实践训练

随着科技的发展&#xff0c;虚拟现实技术在教育领域得到了广泛的应用。VR警务教育实训系统就是其中的一种应用&#xff0c;该系统由广州华锐互动开发&#xff0c;可以模拟真实的警务场景&#xff0c;让学生通过虚拟现实技术进行实践训练&#xff0c;提高学生的实践能力和技能水…...

【深入浅出C#】章节 7: 文件和输入输出操作:处理文本和二进制数据

文件和输入输出操作在计算机编程中具有重要性&#xff0c;因为它们涉及数据的持久化存储和交互。数据可以是不同类型的&#xff0c;例如文本、图像、音频、视频和二进制数据。这些不同类型的数据具有不同的存储需求。 文本数据是最常见的数据类型之一&#xff0c;用于存储和传输…...

Matlab中图例的位置(图例放在图的上方、下方、左方、右方、图外面)等

一、图例默认位置 默认的位置在NorthEast r 10; a 0; b 0; t0:0.1:2.1*pi; xar*cos(t); ybr*sin(t); A1plot(x,y,r,linewidth,4);%圆 hold on axis equal A2plot([0 0],[1 10],b,linewidth,4);%直线 legend([A1,A2],圆形,line)二、通过Location对legend的位置进行改变 变…...

【算法学习】两数之和II - 输入有序数组

题目描述 原题链接 给你一个下标从 1 开始的整数数组 numbers &#xff0c;该数组已按 非递减顺序排列 &#xff0c;请你从数组中找出满足相加之和等于目标数 target 的两个数。如果设这两个数分别是 numbers[index1] 和 numbers[index2] &#xff0c;则 1 < index1 < …...

聚观早报|京东称在技术投入没有止境;木蚁机器人完成B2轮融资

【聚观365】8月18日消息 京东零售CEO表示在技术上投入没有止境 木蚁机器人完成B2轮超亿元融资 耐能推出AI芯片KL730 三星电子泰勒晶圆厂首家客户是AI半导体厂商 韩国新能源汽车7月出口额同比大增36% 京东零售CEO表示在技术上投入没有止境 近日&#xff0c;京东零售CEO辛利…...

C语言:选择+编程(每日一练)

目录 选择题&#xff1a; 题一&#xff1a; 题二&#xff1a; 题三&#xff1a; 题四&#xff1a; 题五&#xff1a; 编程题&#xff1a; 题一&#xff1a;尼科彻斯定理 示例1 题二&#xff1a;等差数列 示例2 本人实力有限可能对一些地方解释和理解的不够清晰&…...

信道数据传输速率、码元传输速率、调制速度,信号传播速度之间的关系

1、信道数据传输速率&#xff08;bit/s&#xff09; 举例&#xff1a;移动通信中的数据传输速率。假设你的手机连接到4G网络&#xff0c;该网络的最大理论数据传输速率为100 Mbps。这意味着在理想情况下&#xff0c;你的手机可以以每秒100兆比特的速度传输数据。 2、码元传输速…...

docker的使用方法总结

Docker是一个非常强大的工具&#xff0c;它可以用于创建、部署和运行应用程序。以下是一些docker相关的常用指令&#xff0c; 1、查看docker版本 docker version 2、查看正在运行的Docker容器 docker ps 3、查看所有的docker容器&#xff08;包括没有运行的容器&#xff0…...

【C#】条码管理操作手册

前言&#xff1a;本文档为条码管理系统操作指南&#xff0c;介绍功能使用、参数配置、资源链接&#xff0c;以及异常的解决等。思维导图如下&#xff1a; 一、思维导图 二、功能操作–条码打印&#xff08;客户端&#xff09; 2.1 参数设置 功能介绍&#xff1a;二维码图片样…...

RabbitMq-发布确认高级(避坑指南版)

在初学rabbitMq的时候&#xff0c;伙伴们肯定已经接触到了“发布确认”的概念&#xff0c;但是到了后期学习中&#xff0c;会接触到“springboot”中使用“发布确认”高级的概念。后者主要是解决什么问题呢&#xff1f;或者是什么样的场景引出这样的概念呢&#xff1f; 在生产环…...

Blender增强现实3D模型制作指南【AR】

推荐&#xff1a;用 NSDT编辑器 快速搭建可编程3D场景 将静态和动画 3D 内容集成到移动增强现实 (AR) 体验中是增强用户沉浸感和参与度的高效方法。 然而&#xff0c;为 AR 创建 3D 对象可能相当艰巨&#xff0c;尤其是对于那些缺乏 3D 建模经验的人来说。 与添加视频或照片 AR…...

Java查看https证书过期时间(JKS,CERT)

在这里需要使用X.509 证书的抽象类 X509Certificate 。此类提供了一种访问 X.509 证书所有属性的标准方式。 这些证书被广泛使用以支持 Internet 安全系统中的身份验证和其他功能。常见的应用包括增强保密邮件 (PEM)、传输层安全 (SSL)、用于受信任软件发布的代码签名和安全电…...

关于vue,记录一次修饰符.stop和.once的使用,以及猜想。

内置指令 | Vue.js 在vue的api里&#xff0c;关于v-on有stop和once两个事件标签。 .stop - 调用 event.stopPropagation()。.once - 最多触发一次处理函数。 原有主要代码和页面效果 &#xff08;无stop和once&#xff09;: ...<div class"div" click"di…...

解决git reset --soft HEAD^撤销commit时报错

今天在使用git回退功能的时候&#xff0c;遇到以下错误&#xff1a; 解决git reset --soft HEAD^撤销commit时报错 问题&#xff1a; 在进行完commit后&#xff0c;想要撤销该commit&#xff0c;于是使用了git reset --soft HEAD^命令&#xff0c;但是出现如下报错&#xff1…...

【BASH】回顾与知识点梳理(三十四)

【BASH】回顾与知识点梳理 三十四 三十四. 认识系统服务&#xff08;二&#xff09;34.1 systemctl 针对 service 类型的配置文件systemctl 配置文件相关目录简介systemctl 配置文件的设定项目简介[Unit] 部份[Service] 部份[Install] 部份 两个 vsftpd 运作的实例多重的重复设…...

Python可视化在量化交易中的应用(11)_Seaborn折线图

举个栗子&#xff0c;用seaborn绘制折线图。 Seaborn中折线图的绘制方法 在seaborn中&#xff0c;我们一般使用sns作为seaborn模块的别名&#xff0c;因此&#xff0c;在下文中&#xff0c;均以sns指代seaborn模块。 seaborn中绘制折线图使用的是sns.plot()函数&#xff1a; …...

使用docker在3台服务器上搭建基于redis 6.x的一主两从三台均是哨兵模式

一、环境及版本说明 如果服务器已经安装了docker,则忽略此步骤,如果没有安装,则可以按照一下方式安装: 1. 在线安装(有互联网环境): 请看我这篇文章 传送阵>> 点我查看 2. 离线安装(内网环境):请看我这篇文章 传送阵>> 点我查看 说明&#xff1a;假设每台服务器已…...

【力扣数据库知识手册笔记】索引

索引 索引的优缺点 优点1. 通过创建唯一性索引&#xff0c;可以保证数据库表中每一行数据的唯一性。2. 可以加快数据的检索速度&#xff08;创建索引的主要原因&#xff09;。3. 可以加速表和表之间的连接&#xff0c;实现数据的参考完整性。4. 可以在查询过程中&#xff0c;…...

AtCoder 第409​场初级竞赛 A~E题解

A Conflict 【题目链接】 原题链接&#xff1a;A - Conflict 【考点】 枚举 【题目大意】 找到是否有两人都想要的物品。 【解析】 遍历两端字符串&#xff0c;只有在同时为 o 时输出 Yes 并结束程序&#xff0c;否则输出 No。 【难度】 GESP三级 【代码参考】 #i…...

React Native在HarmonyOS 5.0阅读类应用开发中的实践

一、技术选型背景 随着HarmonyOS 5.0对Web兼容层的增强&#xff0c;React Native作为跨平台框架可通过重新编译ArkTS组件实现85%以上的代码复用率。阅读类应用具有UI复杂度低、数据流清晰的特点。 二、核心实现方案 1. 环境配置 &#xff08;1&#xff09;使用React Native…...

STM32标准库-DMA直接存储器存取

文章目录 一、DMA1.1简介1.2存储器映像1.3DMA框图1.4DMA基本结构1.5DMA请求1.6数据宽度与对齐1.7数据转运DMA1.8ADC扫描模式DMA 二、数据转运DMA2.1接线图2.2代码2.3相关API 一、DMA 1.1简介 DMA&#xff08;Direct Memory Access&#xff09;直接存储器存取 DMA可以提供外设…...

[10-3]软件I2C读写MPU6050 江协科技学习笔记(16个知识点)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16...

现代密码学 | 椭圆曲线密码学—附py代码

Elliptic Curve Cryptography 椭圆曲线密码学&#xff08;ECC&#xff09;是一种基于有限域上椭圆曲线数学特性的公钥加密技术。其核心原理涉及椭圆曲线的代数性质、离散对数问题以及有限域上的运算。 椭圆曲线密码学是多种数字签名算法的基础&#xff0c;例如椭圆曲线数字签…...

CMake 从 GitHub 下载第三方库并使用

有时我们希望直接使用 GitHub 上的开源库,而不想手动下载、编译和安装。 可以利用 CMake 提供的 FetchContent 模块来实现自动下载、构建和链接第三方库。 FetchContent 命令官方文档✅ 示例代码 我们将以 fmt 这个流行的格式化库为例,演示如何: 使用 FetchContent 从 GitH…...

微信小程序云开发平台MySQL的连接方式

注&#xff1a;微信小程序云开发平台指的是腾讯云开发 先给结论&#xff1a;微信小程序云开发平台的MySQL&#xff0c;无法通过获取数据库连接信息的方式进行连接&#xff0c;连接只能通过云开发的SDK连接&#xff0c;具体要参考官方文档&#xff1a; 为什么&#xff1f; 因为…...

大学生职业发展与就业创业指导教学评价

这里是引用 作为软工2203/2204班的学生&#xff0c;我们非常感谢您在《大学生职业发展与就业创业指导》课程中的悉心教导。这门课程对我们即将面临实习和就业的工科学生来说至关重要&#xff0c;而您认真负责的教学态度&#xff0c;让课程的每一部分都充满了实用价值。 尤其让我…...