当前位置: 首页 > 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; …...

dedecms 织梦自定义表单留言增加ajax验证码功能

增加ajax功能模块&#xff0c;用户不点击提交按钮&#xff0c;只要输入框失去焦点&#xff0c;就会提前提示验证码是否正确。 一&#xff0c;模板上增加验证码 <input name"vdcode"id"vdcode" placeholder"请输入验证码" type"text&quo…...

《用户共鸣指数(E)驱动品牌大模型种草:如何抢占大模型搜索结果情感高地》

在注意力分散、内容高度同质化的时代&#xff0c;情感连接已成为品牌破圈的关键通道。我们在服务大量品牌客户的过程中发现&#xff0c;消费者对内容的“有感”程度&#xff0c;正日益成为影响品牌传播效率与转化率的核心变量。在生成式AI驱动的内容生成与推荐环境中&#xff0…...

如何在看板中有效管理突发紧急任务

在看板中有效管理突发紧急任务需要&#xff1a;设立专门的紧急任务通道、重新调整任务优先级、保持适度的WIP&#xff08;Work-in-Progress&#xff09;弹性、优化任务处理流程、提高团队应对突发情况的敏捷性。其中&#xff0c;设立专门的紧急任务通道尤为重要&#xff0c;这能…...

HBuilderX安装(uni-app和小程序开发)

下载HBuilderX 访问官方网站&#xff1a;https://www.dcloud.io/hbuilderx.html 根据您的操作系统选择合适版本&#xff1a; Windows版&#xff08;推荐下载标准版&#xff09; Windows系统安装步骤 运行安装程序&#xff1a; 双击下载的.exe安装文件 如果出现安全提示&…...

DBAPI如何优雅的获取单条数据

API如何优雅的获取单条数据 案例一 对于查询类API&#xff0c;查询的是单条数据&#xff0c;比如根据主键ID查询用户信息&#xff0c;sql如下&#xff1a; select id, name, age from user where id #{id}API默认返回的数据格式是多条的&#xff0c;如下&#xff1a; {&qu…...

html-<abbr> 缩写或首字母缩略词

定义与作用 <abbr> 标签用于表示缩写或首字母缩略词&#xff0c;它可以帮助用户更好地理解缩写的含义&#xff0c;尤其是对于那些不熟悉该缩写的用户。 title 属性的内容提供了缩写的详细说明。当用户将鼠标悬停在缩写上时&#xff0c;会显示一个提示框。 示例&#x…...

redis和redission的区别

Redis 和 Redisson 是两个密切相关但又本质不同的技术&#xff0c;它们扮演着完全不同的角色&#xff1a; Redis: 内存数据库/数据结构存储 本质&#xff1a; 它是一个开源的、高性能的、基于内存的 键值存储数据库。它也可以将数据持久化到磁盘。 核心功能&#xff1a; 提供丰…...

机器学习的数学基础:线性模型

线性模型 线性模型的基本形式为&#xff1a; f ( x ) ω T x b f\left(\boldsymbol{x}\right)\boldsymbol{\omega}^\text{T}\boldsymbol{x}b f(x)ωTxb 回归问题 利用最小二乘法&#xff0c;得到 ω \boldsymbol{\omega} ω和 b b b的参数估计$ \boldsymbol{\hat{\omega}}…...

安全领域新突破:可视化让隐患无处遁形

在安全领域&#xff0c;隐患就像暗处的 “幽灵”&#xff0c;随时可能引发严重事故。传统安全排查手段&#xff0c;常常难以将它们一网打尽。你是否好奇&#xff0c;究竟是什么神奇力量&#xff0c;能让这些潜藏的隐患无所遁形&#xff1f;没错&#xff0c;就是可视化技术。它如…...

分布式计算框架学习笔记

一、&#x1f310; 为什么需要分布式计算框架&#xff1f; 资源受限&#xff1a;单台机器 CPU/GPU 内存有限。 任务复杂&#xff1a;模型训练、数据处理、仿真并发等任务耗时严重。 并行优化&#xff1a;通过任务拆分和并行执行提升效率。 可扩展部署&#xff1a;适配从本地…...