↑↓ 选择↵ 打开⌫ 切换范围完整搜索

PG.CENTER 连接 PostgreSQL 文档、百科与生态知识。由 Pigsty 维护。

Wiki / SQL 命令

ALTER MATERIALIZED VIEW

更改一个物化视图的定义

英文手册覆盖始于 PostgreSQL 6.4. 来源历史与运行验证各自保留独立版本边界。

当前阅读 PG 18·选择有来源记录的版本

9.39.49.59.61011121314151617181920
anchor
SQL-ALTERMATERIALIZEDVIEW
file
sql-altermaterializedview.html
lang
en
name
ALTER MATERIALIZED VIEW
purpose
change the definition of a materialized view
purpose zh
未知
sections same as
未知
slug
18

语法

ALTER MATERIALIZED VIEW [ IF EXISTS ] name
action [, ... ]
ALTER MATERIALIZED VIEW name
[ NO ] DEPENDS ON EXTENSION extension_name
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
ALTER MATERIALIZED VIEW ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]
SET TABLESPACE new_tablespace [ NOWAIT ]

where action is one of:

ALTER [ COLUMN ] column_name SET STATISTICS integer
ALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] )
ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )
ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN | DEFAULT }
ALTER [ COLUMN ] column_name SET COMPRESSION compression_method
CLUSTER ON index_name
SET WITHOUT CLUSTER
SET ACCESS METHOD new_access_method
SET TABLESPACE new_tablespace
SET ( storage_parameter [= value] [, ... ] )
RESET ( storage_parameter [, ... ] )
OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
语法图

从左向右阅读；分支表示选择，旁路表示可选，循环表示重复。点击带下划线的参数可打开对应规则。

ALTER MATERIALIZED VIEW IF EXISTS name action ,
ALTER MATERIALIZED VIEW · 语法 2
ALTER MATERIALIZED VIEW name NO DEPENDS ON EXTENSION extension_name
ALTER MATERIALIZED VIEW · 语法 3
ALTER MATERIALIZED VIEW IF EXISTS name RENAME COLUMN column_name TO new_column_name
ALTER MATERIALIZED VIEW · 语法 4
ALTER MATERIALIZED VIEW IF EXISTS name RENAME TO new_name
ALTER MATERIALIZED VIEW · 语法 5
ALTER MATERIALIZED VIEW IF EXISTS name SET SCHEMA new_schema
ALTER MATERIALIZED VIEW · 语法 6
ALTER MATERIALIZED VIEW ALL IN TABLESPACE name OWNED BY role_name , SET TABLESPACE new_tablespace NOWAIT
action
ALTER COLUMN column_name SET STATISTICS integer ALTER COLUMN column_name SET ( attribute_option = value , ) ALTER COLUMN column_name RESET ( attribute_option , ) ALTER COLUMN column_name SET STORAGE PLAIN EXTERNAL EXTENDED MAIN DEFAULT ALTER COLUMN column_name SET COMPRESSION compression_method CLUSTER ON index_name SET WITHOUT CLUSTER SET ACCESS METHOD new_access_method SET TABLESPACE new_tablespace SET ( storage_parameter = value , ) RESET ( storage_parameter , ) OWNER TO new_owner CURRENT_ROLE CURRENT_USER SESSION_USER

版本定义 PG 18

ALTER MATERIALIZED VIEW [ IF EXISTS ] name
action [, ... ]
ALTER MATERIALIZED VIEW name
[ NO ] DEPENDS ON EXTENSION extension_name
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
ALTER MATERIALIZED VIEW ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]
SET TABLESPACE new_tablespace [ NOWAIT ]

其中 action 可以是以下之一：

ALTER [ COLUMN ] column_name SET STATISTICS integer
ALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] )
ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )
ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN | DEFAULT }
ALTER [ COLUMN ] column_name SET COMPRESSION compression_method
CLUSTER ON index_name
SET WITHOUT CLUSTER
SET ACCESS METHOD new_access_method
SET TABLESPACE new_tablespace
SET ( storage_parameter [= value] [, ... ] )
RESET ( storage_parameter [, ... ] )
OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }

描述

ALTER MATERIALIZED VIEW更改一个现有物化视图的各种辅助属性。

要使用ALTER MATERIALIZED VIEW，你必须拥有该物化视图。要更改一个物化视图的模式，你还必须具有新模式上的CREATE权限。要更改拥有者，你必须能够对新拥有角色执行SET ROLE，并且该角色必须具有该物化视图所在模式上的CREATE权限。（这些限制确保，更改拥有者不会做到你无法通过删除并重新创建该物化视图来完成的事情。不过，超级用户无论如何都可以更改任何视图的所有权。）

可用于ALTER MATERIALIZED VIEW的语句形式和动作是 ALTER TABLE可用形式的一个子集，并且在用于物化视图时具有相同的含义。有关细节请参见ALTER TABLE中的说明。

参数

name

一个现有物化视图的名称（可以是模式限定的）。

column_name

现有列的名称。

extension_name

该物化视图要依赖的扩展名称（如果指定了NO，则表示不再依赖）。删除扩展时，标记为依赖于该扩展的物化视图会被自动删除。

new_column_name

现有列的新名称。

new_owner

该物化视图的新拥有者的用户名。

new_name

该物化视图的新名称。

new_schema

该物化视图的新模式。

示例

把物化视图foo重命名为 bar：

ALTER MATERIALIZED VIEW foo RENAME TO bar;

兼容性

ALTER MATERIALIZED VIEW是一种 PostgreSQL扩展。

另见

CREATE MATERIALIZED VIEW, DROP MATERIALIZED VIEW, REFRESH MATERIALIZED VIEW

比较版本

完整来源事实

synopsis html

ALTER MATERIALIZED VIEW [ IF EXISTS ] <em class="replaceable"><code>name</code></em>
    <em class="replaceable"><code>action</code></em> [, ... ]
ALTER MATERIALIZED VIEW <em class="replaceable"><code>name</code></em>
    [ NO ] DEPENDS ON EXTENSION <em class="replaceable"><code>extension_name</code></em>
ALTER MATERIALIZED VIEW [ IF EXISTS ] <em class="replaceable"><code>name</code></em>
    RENAME [ COLUMN ] <em class="replaceable"><code>column_name</code></em> TO <em class="replaceable"><code>new_column_name</code></em>
ALTER MATERIALIZED VIEW [ IF EXISTS ] <em class="replaceable"><code>name</code></em>
    RENAME TO <em class="replaceable"><code>new_name</code></em>
ALTER MATERIALIZED VIEW [ IF EXISTS ] <em class="replaceable"><code>name</code></em>
    SET SCHEMA <em class="replaceable"><code>new_schema</code></em>
ALTER MATERIALIZED VIEW ALL IN TABLESPACE <em class="replaceable"><code>name</code></em> [ OWNED BY <em class="replaceable"><code>role_name</code></em> [, ... ] ]
    SET TABLESPACE <em class="replaceable"><code>new_tablespace</code></em> [ NOWAIT ]

<span class="phrase">where <em class="replaceable"><code>action</code></em> is one of:</span>

    ALTER [ COLUMN ] <em class="replaceable"><code>column_name</code></em> SET STATISTICS <em class="replaceable"><code>integer</code></em>
    ALTER [ COLUMN ] <em class="replaceable"><code>column_name</code></em> SET ( <em class="replaceable"><code>attribute_option</code></em> = <em class="replaceable"><code>value</code></em> [, ... ] )
    ALTER [ COLUMN ] <em class="replaceable"><code>column_name</code></em> RESET ( <em class="replaceable"><code>attribute_option</code></em> [, ... ] )
    ALTER [ COLUMN ] <em class="replaceable"><code>column_name</code></em> SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN | DEFAULT }
    ALTER [ COLUMN ] <em class="replaceable"><code>column_name</code></em> SET COMPRESSION <em class="replaceable"><code>compression_method</code></em>
    CLUSTER ON <em class="replaceable"><code>index_name</code></em>
    SET WITHOUT CLUSTER
    SET ACCESS METHOD <em class="replaceable"><code>new_access_method</code></em>
    SET TABLESPACE <em class="replaceable"><code>new_tablespace</code></em>
    SET ( <em class="replaceable"><code>storage_parameter</code></em> [= <em class="replaceable"><code>value</code></em>] [, ... ] )
    RESET ( <em class="replaceable"><code>storage_parameter</code></em> [, ... ] )
    OWNER TO { <em class="replaceable"><code>new_owner</code></em> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }

synopsis text

ALTER MATERIALIZED VIEW [ IF EXISTS ] name
action [, ... ]
ALTER MATERIALIZED VIEW name
[ NO ] DEPENDS ON EXTENSION extension_name
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
ALTER MATERIALIZED VIEW ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]
SET TABLESPACE new_tablespace [ NOWAIT ]

where action is one of:

ALTER [ COLUMN ] column_name SET STATISTICS integer
ALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] )
ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )
ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN | DEFAULT }
ALTER [ COLUMN ] column_name SET COMPRESSION compression_method
CLUSTER ON index_name
SET WITHOUT CLUSTER
SET ACCESS METHOD new_access_method
SET TABLESPACE new_tablespace
SET ( storage_parameter [= value] [, ... ] )
RESET ( storage_parameter [, ... ] )
OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }

来源引用

完整定义与证据 JSON

定义来源

center · PostgreSQL 18 · a709ab85

正文语言: zh-Hans · 1b5ca64c