↑↓ select↵ open⌫ change scopeOpen full search

PG.CENTER connects PostgreSQL documentation, reference, and ecosystem knowledge. Maintained by Pigsty.

Wiki / SQL Commands

ALTER MATERIALIZED VIEW

change the definition of a materialized view

English manual coverage begins with PostgreSQL 6.4. Source history and runtime observations have their own recorded version boundaries.

Reading PG 18·Select a recorded version

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
Unknown
slug
18

Syntax

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 }
Syntax diagrams

Read left to right. Branches are choices, bypasses are optional, loops repeat. Select an underlined parameter to open its rule.

ALTER MATERIALIZED VIEW IF EXISTS name action ,
ALTER MATERIALIZED VIEW · Syntax 2
ALTER MATERIALIZED VIEW name NO DEPENDS ON EXTENSION extension_name
ALTER MATERIALIZED VIEW · Syntax 3
ALTER MATERIALIZED VIEW IF EXISTS name RENAME COLUMN column_name TO new_column_name
ALTER MATERIALIZED VIEW · Syntax 4
ALTER MATERIALIZED VIEW IF EXISTS name RENAME TO new_name
ALTER MATERIALIZED VIEW · Syntax 5
ALTER MATERIALIZED VIEW IF EXISTS name SET SCHEMA new_schema
ALTER MATERIALIZED VIEW · Syntax 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

Definition 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 ]

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 }

Description

ALTER MATERIALIZED VIEW changes various auxiliary properties of an existing materialized view.

You must own the materialized view to use ALTER MATERIALIZED VIEW. To change a materialized view's schema, you must also have CREATE privilege on the new schema. To alter the owner, you must be able to SET ROLE to the new owning role, and that role must have CREATE privilege on the materialized view's schema. (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the materialized view. However, a superuser can alter ownership of any view anyway.)

The statement subforms and actions available for ALTER MATERIALIZED VIEW are a subset of those available for ALTER TABLE, and have the same meaning when used for materialized views. See the descriptions for ALTER TABLE for details.

Parameters

name

The name (optionally schema-qualified) of an existing materialized view.

column_name

Name of an existing column.

extension_name

The name of the extension that the materialized view is to depend on (or no longer dependent on, if NO is specified). A materialized view that's marked as dependent on an extension is automatically dropped when the extension is dropped.

new_column_name

New name for an existing column.

new_owner

The user name of the new owner of the materialized view.

new_name

The new name for the materialized view.

new_schema

The new schema for the materialized view.

Examples

To rename the materialized view foo to bar:

ALTER MATERIALIZED VIEW foo RENAME TO bar;

Compatibility

ALTER MATERIALIZED VIEW is a PostgreSQL extension.

See Also

CREATE MATERIALIZED VIEW, DROP MATERIALIZED VIEW, REFRESH MATERIALIZED VIEW

Compare versions

Complete source facts

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 }

Source references

Full definition and evidence JSON

Definition sources

center · PostgreSQL 18 · wiki-repair-20261023:767b07b99dd3816050212860cc14fb4dfbad0fbc8a132aabf78bc26fc4e494d3

Text language: en · wiki-repair-20261023:767b07b99dd3816050212860cc14fb4dfbad0fbc8a132aabf78bc26fc4e494d3