postgresql-物化视图

物化视图

创建物化视图

postgresql使用create materialized view 语句创建视图

create materialized view
if not exists name
as query
[with [NO] data];
1
2
3
4

-- 创建一个包含员工统计信息的物化视图
-- emp_stat_mv 包含了按照部门和职位统计的员工数量
create materialized view emp_stat_mv as
select 
d.department_name,
j.job_title,
count(*) 
from cps.public.employees e 
join cps.public.departments d on d.department_id  = e.department_id 
join cps.public.jobs j on j.job_id = e.job_id 
group by d.department_name,j.job_title ;
1
2
3
4
5
6
7
8
9
10
11

-- 测试
select * from emp_stat_mv;
1
2

-- 物化视图可以像表一样支持索引，实现约束和查询优化，为emp_stat_mv创建唯一索引
create unique index uk_emp_stat_mv on emp_stat_mv (department_name,job_title);
1
2

刷新物化视图

postgresql物化视图不会自动刷新数据，需要手动执行refresh materialized view语句：

refresh materialized view [concurrently]name[ with [no] data];
1

-- 我们可以定期执行以下语句刷新物化视图emp_stat_mv
refresh materialized view concurrently cps.public.emp_stat_mv;
1
2

-- 或者执行以下语句清空物化视图emp_stat_mv中的数据
refresh materialized view emp_stat_mv
with no data;
1
2
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;
1
2
3
4
5
6
7
8
9
10

--  修改物化视图名称为emp_stat_mv2
alter materialized view emp_stat_mv rename to emp_stat_mv2;
1
2

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

alter materialized view 语句还提供了其他的修改功能，具体参考官网介绍

删除物化视图

postgresql使用drop materialized view 语句删除物化视图：

drop materialized view [if exists] name [cascade | restrict];
1

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

--可以使用以下语句删除物化视图emp_stat_mv2
drop materialized view emp_stat_mv2;
1
2