PostgreSQL15——视图

最新推荐文章于 2026-06-23 11:52:16 发布
原创 最新推荐文章于 2026-06-23 11:52:16 发布 · 1.2k 阅读 · 24 ·
本内容遵循CC 4.0 BY-SA版权协议
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
GEO检测
收录于
PostgreSQL

当前文章被收录于：

PostgreSQL
32 篇文章 2 人学习
订阅专栏 查看详情

开发板推荐：天空星STM32F407VET6开发板

超高性价比 STM32主控 | 超高主频 | 一板兼容百芯 | 比赛神器 | 沉金彩色丝印

点击查看

视图

一、视图概述

视图（View）本质上是一个存储在数据库中的查询语句。视图本身不包含数据，也被称为虚拟表。我们在创建视图时给它指定了一个名称，然后可以像表一样对其进行查询。

在这里插入图片描述

合理使用的视图可以给我们带来以下好处：

  • 替代复杂查询，减少复杂性。将复杂的查询语句定义为视图，然后使用视图进行查询，可以隐藏具体的实现；
  • 提供一致性接口，实现业务规则。在视图的定义中增加业务逻辑，对外提供统一的接口；当底层表结构发生变化时，只需要修改视图接口，而不需要修改外部应用，可以简化代码的维护并减少错误；
  • 控制对于表的访问，提高安全性。通过视图为用户提供数据访问，而不是直接访问表；同时可以限制允许访问某些敏感信息，例如身份证号、工资等。

二、创建视图

PostgreSQL 使用 CREATE VIEW 语句创建视图：

CREATE VIEW view_name AS query;

其中，view_name 是视图的名称；AS 之后是视图的查询语句，可以是简单查询或者复杂的查询。以下语句创建了一个包含员工详细信息的视图：

CREATE VIEW emp_details_view
AS SELECT
	e.employee_id,
	e.job_id,
	e.manager_id,
	e.department_id,
	d.location_id,
	e.first_name,
	e.last_name,
	e.salary,
	e.commission_pct,
	d.department_name,
	j.job_title
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
JOIN jobs j ON (j.job_id = e.job_id);

该视图使用了连接查询从 3 个表中获取信息。

接下来，我们可以直接从视图中查询数据，不需要每次编写复杂的连接查询：

SELECT * FROM emp_details_view
WHERE department_name = 'IT';

在这里插入图片描述

该语句返回了 IT 部门的员工信息。

三、修改视图

如果需要修改视图定义中的查询，可以使用CREATE OR REPLACE 语句：

CREATE OR REPLACE VIEW view_name
AS
query

PostgreSQL 目前只支持追加视图定义中的字段，不支持减少字段或者修改字段的名称或顺序。例如，我们可以为视图 emp_details_view 增加一个字段 hire_date：

CREATE OR REPLACE VIEW emp_details_view
AS SELECT
	e.employee_id,
	e.job_id,
	e.manager_id,
	e.department_id,
	d.location_id,
	e.first_name,
	e.last_name,
	e.salary,
	e.commission_pct,
	d.department_name,
	j.job_title,
	e.hire_date
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
JOIN jobs j ON (j.job_id = e.job_id);

另外，PostgreSQL 还提供了 ALTER VIEW 语句修改视图的属性。例如以下语句用于修改视图的名称：

ALTER VIEW emp_details_view RENAME TO emp_info_view;

该语句将视图 emp_details_view 重命名为emp_info_view。

四、删除视图

使用DROP VIEW 语句删除一个已有的视图：

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

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

我们将视图 emp_info_view 删除：

DROP VIEW emp_info_view;

五、递归视图

通用表表达式（CTE），包括使用 RECURSIVE 关键字实现递归查询。与此类似，视图的定义中也可以使用RECURSIVE：

CREATE RECURSIVE VIEW view_name (column_names) AS query;

需要注意的是，递归视图需要指定字段的名称column_names。以上语句实际上等价于：

CREATE VIEW view_name AS
WITH RECURSIVE cte_name (column_names) AS (query)
SELECT column_names FROM cte_name;

通过递归 CTE 遍历组织结构的查询语句定义为一个递归视图：

CREATE RECURSIVE VIEW employee_path(employee_id, employee_name, path) AS
	SELECT employee_id, 
		   CONCAT(first_name, ',', last_name), 
		   CONCAT(first_name, ',', last_name) AS path
	FROM employees
	WHERE manager_id IS NULL
	UNION ALL
	SELECT e.employee_id, 
		   CONCAT(e.first_name, ',', e.last_name),
	 	   CONCAT(ep.path, '->', e.first_name, ',', e.last_name)
	FROM employee_path ep
	JOIN employees e ON ep.employee_id = e.manager_id;

六、可更新视图

如果一个视图满足以下条件：

  • 视图定义的FROM 子句中只包含一个表或者可更新视图；
  • 视图定义的最顶层查询语句中不包含以下子句： GROUP BY、 HAVING、 LIMIT、 OFFSET、DISTINCT、WITH、UNION、INTERSECT 以及EXCEPT；
  • SELECT 列表中不包含窗口函数、集合函数或者聚合函数（例如 SUM、COUNT、AVG等）。

那么该视图被称为可更新视图（updatable view），意味着我们可以对其执行 INSERT、UPDATE 以及 DELETE 语句，PostgreSQL 会将这些操作转换为对底层表的操作。

我们创建一个视图 employees_it：

CREATE VIEW employees_it AS
SELECT employee_id,
	   first_name,
	   last_name,
	   email,
	   phone_number,
	   hire_date,
	   job_id,
	   manager_id,
	   department_id
FROM employees
WHERE department_id = 60;

视图 employees_it 只包含了 IT 部门的员工信息，并且不包含员工的月薪字段。该视图目前只能查询到 5 条数据：

SELECT employee_id,first_name, last_name 
FROM employees_it;

在这里插入图片描述

我们通过视图 employees_it 为employees 表增加一个员工：

INSERT INTO employees_it (employee_id, first_name, last_name, email, phone_number, hire_date, job_id,
manager_id, department_id)
VALUES(207, 'Tony', 'Dong', 'DONG', '590.423.5568', '2020-05-06', 'IT_PROG',
103, 60);


SELECT employee_id,first_name, last_name FROM employees_it;

在这里插入图片描述

需要注意，不在视图定义中的字段不能通过视图进行修改。以下语句尝试通过视图employees_it 修改员工的月薪：

UPDATE employees_it
SET salary = 5000
WHERE employee_id = 207;

在这里插入图片描述

不在视图定义中的字段不能通过视图进行操作。我们将新增的员工从 employees 表中删除：

DELETE FROM employees_it
WHERE employee_id = 207;

6.1、WITH CHECK OPTION

我们再次通过视图 employees_it 为 employees 表增加一个员工，此时将部门编号改为 80：

INSERT INTO employees_it(employee_id, first_name, last_name, email, phone_number, hire_date, job_id,
manager_id, department_id)
VALUES(207, 'Tony', 'Dong', 'DONG', '590.423.5568', '2020-05-06', 'IT_PROG',
149, 80);

SELECT employee_id,first_name, last_name
FROM employees_it
WHERE employee_id = 207;

在这里插入图片描述

SELECT employee_id,first_name, last_name
FROM employees
WHERE employee_id = 207;

在这里插入图片描述

新增加的员工不属于 IT 部门，增加之后无法通过视图查询，但是可以通过 employees 表查询。

为了防止通过视图插入或者修改视图不可见的数据，可以使用WITH CHECK OPTION 选项：

CREATE OR REPLACE VIEW employees_it AS
SELECT employee_id,
	   first_name,
	   last_name,
	   email,
	   phone_number,
	   hire_date,
	   job_id,
	   manager_id,
	   department_id
FROM employees
WHERE department_id = 60
WITH CHECK OPTION;

再次通过视图 employees_it 为employees 表增加一个员工：

DELETE FROM employees
WHERE employee_id = 207;

INSERT INTO employees_it(employee_id, first_name, last_name, email, phone_number, hire_date, job_id,
manager_id, department_id)
VALUES(207, 'Tony', 'Dong', 'DONG', '590.423.5568', '2020-05-06', 'IT_PROG',
149, 80);

在这里插入图片描述

执行结果显示违反检查选项，无法插入数据。
WITH CASCADED CHECK OPTION 选项会对视图以及它所依赖的其他视图进行级联检查；

WITH LOCAL CHECK OPTION 选项只对当前视图进行检查。默认为 CASCADED。

七、物化视图

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

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

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

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

7.1、创建物化视图

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 MATERIALIZEDVIEW 命令刷新物化视图数据之后才能查询。

虽然物化视图定义中的查询语句支持 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;

在这里插入图片描述

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

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

7.2、刷新物化视图

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;

7.3、修改物化视图

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;

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

7.4、删除物化视图

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;

开发板推荐：天空星STM32F407VET6开发板

超高性价比 STM32主控 | 超高主频 | 一板兼容百芯 | 比赛神器 | 沉金彩色丝印

点击查看
标签
#数据库
《PostgreSQL开发指南》第32篇 物化视图
Tony.Dong的专栏
08-18 2475
物化视图（Materialized View）是 PostgreSQL 提供的一个扩展功能，它是介于视图和表之间的一种对象。
postgresql-物化视图
weixin_40719943的博客
02-01 1021
每次调用物化视图，如果补刷新物化视图，每次得到的物化视图结果都是一样的，：在视图的基础上进行扩展，可以存储物理数据。：本身不保存数据，是底层物理表的虚拟表；
PostgreSQL物化视图Materialized View
zxrhhm的博客
05-11 605
PostgreSQL物化视图(Materialized View)是将视图查询结果持久化存储的数据库对象，相比普通视图具有查询性能高但数据实时性低的特点。本文详细介绍了物化视图的创建语法、刷新方式（包括全量刷新和并发刷新）以及性能对比示例。特别说明了PostgreSQL原生不支持增量刷新，但可通过pg_ivm扩展或触发器实现类似功能。文章还提供了三种定时刷新方案：操作系统crontab、pg_cron扩展以及触发器异步通知机制。物化视图适用于报表、复杂聚合等对实时性要求不高但需要快速查询的场景，合理使用可显
【postgresql 物化视图】自动刷新物化视图2种方法
snowyar的专栏
12-30 1341
新版的物化视图新增了 concurrently 参数，可以使在刷新视图时不会锁住该物化视图的查询工作。pg_cron是基于cron的作业调度插件，语法与常规cron相同，但它可以直接从数据库执行PostgreSQL命令。│ │ │ │ ┌───────────────── 一周中的某一天 ：0 ~ 6，0表示周日。│ │ │ ┌──────────────── 月份： 1 ~ 12。│ │ ┌─────────────── 日期： 1 ~ 31。│ ┌────────────── 小时： 0 ~ 23。
PostgreSQL视图和物化视图
weixin_45706122的博客
07-04 8638
PostgreSQL 视图、物化视图
PostgreSQl 物化视图
林胖的专栏
07-11 1704
物化视图（Materialized View）是PostgreSQL提供的一个扩展功能，它是介于视图和表之间的一种对象。
PostgreSQL技术内幕15：物化视图
qq_40529346的博客
10-19 1136
本节主要介绍PG的物化视图，从物化视图产生背景到使用方式，实现思路以及源码分析多个方面来对物化视图进行详细的分析。
【PostgreSQL15-beta1版本系统表、系统视图、等待事件变化】
小怪兽的博客
07-04 3530
PostgreSQL15-beta1版本系统表、系统视图、等待事件变化
Navicat老版本连接PostgreSQL15报错？一招修改libcc.dll搞定（附详细步骤）
weixin_30335575的博客
03-25 138
本文详细解析了Navicat旧版连接PostgreSQL 15时出现的'ERROR: column datlastsysoid does not exist'报错问题，并提供了三种解决方案：修改libcc.dll文件、创建兼容性视图和使用连接中间件。重点介绍了通过十六进制编辑器修改libcc.dll的详细步骤，帮助开发者在不升级Navicat的情况下解决兼容性问题。
PostgreSQL物化视图实战：预计算加速OLAP查询
最新发布
dy_f的专栏
06-23 227
物化视图是关系型数据库中实现预计算的核心机制，其原理是将复杂查询结果持久化为物理表，绕过实时JOIN与聚合开销，从而在保证SQL兼容性前提下显著提升分析类查询性能。技术价值在于以极低架构侵入性解耦读写压力，避免引入Redis缓存一致性难题或OLAP组件运维负担；典型应用场景包括电商实时报表、BI亚秒级拖拽分析、创业公司轻量数仓建设等。本文聚焦PostgreSQL原生物化视图，深入解析预计算落地路径、CONCURRENTLY刷新机制及增量更新实践，覆盖从定义、索引、查询重写到生产避坑的完整工程链路。
PostgreSQL：语言基础与数据库操作
在技术的广袤天地里，本博客如精准罗盘。剖析前沿科技，深掘代码奥秘，以精炼笔触，带您穿越复杂技术迷宫，速达知识彼岸。
03-20 2280
PostgreSQL 15的新特性如MERGE命令、构造函数等，正在不断扩展其应用边界。向量相似度搜索：结合pgvector扩展实现AI特征检索分布式架构：使用Citus实现水平扩展时序数据优化：TimescaleDB的深度集成机器学习集成：MADlib库的统计分析应用。
PostgreSQL14/15 pg_buffercache 知识点汇总
ly0425的博客
01-08 1871
postgresql 14 15 pg_buffercache
PostgreSQL15升级后Navicat报错？手把手教你绕过datlastsysoid字段问题
weixin_29234423的博客
04-01 348
本文详细解析了PostgreSQL 15升级后Navicat报错的原因，特别是datlastsysoid字段移除导致的问题，并提供了四种解决方案：升级Navicat客户端、修改libcc.dll文件、创建兼容性视图以及使用替代客户端工具，帮助用户快速恢复数据库连接。
Navicate 连接 PostgreSQL 看不到库表 但是SQL正常执行
weixin_44495781的博客
10-14 5911
笔者用的Navicate 12 连接postgreSQL 只能看到视图信息 看不到表 执行查询sql正常执行 解决： 更新Navicate 到 15 问题解决
2、PostgreSQL 15：强大新特性全解析
efc123456的博客
07-21 112
PostgreSQL 15 引入了众多强大新特性，涵盖数据库管理、开发功能、性能优化和数据复制等多个方面。本文详细解析了与DBA相关的功能改进，如移除旧版pg_dump支持、弃用Python 2、修复公共模式安全问题等；针对开发者，新增MERGE命令、支持ICU语言环境、改进数值类型处理等；性能方面优化了并行查询、统计信息处理和WAL恢复预取；复制功能也增强了逻辑解码、行和列过滤以及基础备份压缩支持。这些特性使PostgreSQL 15在安全性、效率和可用性上得到全面提升。
15款优秀的mysql管理工具与应用程序推荐
weixin_64261178的博客
07-26 6426
对于开发人员来说，mysql管理工具可以说是必不可缺的，它能帮助开发人员在许多方面支持包括PostgreSQL、MySQL、Redis、MongoDB等在内的多种数据库；同时还能提供包括触发器、事件、视图、数据备份、对象结构等多种功能。 本文帮大家整理了15款优秀的MySQL管理工具和应用软件，大家可以从中选择一个，从而帮助你轻松快速完成工作
Ubuntu 20.04 安装 PostgreSQL 14/15 最稳 APT 实战指南
weixin_34152820的博客
06-21 452
PostgreSQL 是支持标准 SQL、ACID 事务与复杂分析的开源关系型数据库，其核心能力包括窗口函数、JSONB 处理、逻辑复制及扩展生态（如 pgvector、TimescaleDB）。在 Ubuntu 系统中，APT 包管理提供最佳集成度：原生 systemd 服务、日志轮转、安全更新与无缝升级。相比源码编译（耗时长、维护难）和 Docker（网络/存储调试复杂），APT 方式兼顾生产稳定性与运维可见性。本文聚焦 Ubuntu 20.04 用户真实痛点——解决 'psql command not
mysql dba管理_你用了吗？DBA必备的15款MySQL管理工具
weixin_30000283的博客
01-19 384
【IT168 评论】如今，Web应用程序的响应速度是成功的关键法宝之一。它与用户互动，用户对网站的看法，甚至谷歌网站排名情况都有着密不可分的关系。数据库性能是响应速度最重要的因素之一，一旦出错，所有程序都将会宕机。工欲善其事，必先利其器。几乎每一个Web开发人员都有一个最钟爱的MySQL管理工具，它帮助开发人员在许多方面支持包括PostgreSQL，MySQL，SQLite，Redis，Mongo...
PG15流复制环境搭建
slj_2018的博客
02-05 330
进程，说明备库已成功连接主库。，证明备库只读模式生效。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值