{"Entry":{"collection":"sql","key":"create-materialized-view","name":"CREATE MATERIALIZED VIEW","aliases":["creatematerializedview"],"metadata":{"aliases":["creatematerializedview"],"changed_in":["9.5","12"],"changes":[{"from":"9.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":[],"removed":[]},"status":"added","synopsis":null,"to":"9.3"},{"from":"9.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":{"added":["CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] table_name"],"removed":[]},"to":"9.5"},{"from":"11","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters"],"removed":[]},"status":"changed","synopsis":{"added":["[ (column_name [, ...] ) ]","[ USING method ]"],"removed":[]},"to":"12"},{"from":"12","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"13"},{"from":"13","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"14"},{"from":"14","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"15"},{"from":"16","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"17"},{"from":"17","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"18"}],"content_hash":"78c3a57236be27267b34b8daab1604c8eb4e75852eb24afd977f66ca6f34466c","editorial":{},"first_version":"9.3","group":"table","imported_at":"2026-09-30T17:43:35.9043+08:00","last_version":"20","name":"CREATE MATERIALIZED VIEW","object":"MATERIALIZED VIEW","position":1,"present_in":["9.3","9.4","9.5","9.6","10","11","12","13","14","15","16","17","18","19","20"],"purpose":"define a new materialized view","purpose_zh":"","related":["alter-materialized-view","create-table-as","create-view","drop-materialized-view","refresh-materialized-view"],"slug":"create-materialized-view","source_rev":"a709ab85","synopsis":"CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] table_name\n[ (column_name [, ...] ) ]\n[ USING method ]\n[ WITH ( storage_parameter [= value] [, ... ] ) ]\n[ TABLESPACE tablespace_name ]\nAS query\n[ WITH [ NO ] DATA ]","verb":"CREATE"}},"Definition":{"Collection":"sql","Key":"create-materialized-view","SourceDatabase":"center","Version":"18","SourceTable":"sqlcmd","SourceKey":"create-materialized-view","SourceRevision":"a709ab85","Facts":{"anchor":"SQL-CREATEMATERIALIZEDVIEW","file":"sql-creatematerializedview.html","lang":"en","name":"CREATE MATERIALIZED VIEW","purpose":"define a new materialized view","purpose_zh":"","related":["alter-materialized-view","create-table-as","create-view","drop-materialized-view","refresh-materialized-view"],"sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE MATERIALIZED VIEW\u003c/code\u003e defines a materialized view of a query. The query is executed and used to populate the view at the time the command is issued (unless \u003ccode class=\"command\"\u003eWITH NO DATA\u003c/code\u003e is used) and may be refreshed later using \u003ccode class=\"command\"\u003eREFRESH MATERIALIZED VIEW\u003c/code\u003e.\u003c/p\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE MATERIALIZED VIEW\u003c/code\u003e is similar to \u003ccode class=\"command\"\u003eCREATE TABLE AS\u003c/code\u003e, except that it also remembers the query used to initialize the view, so that it can be refreshed later upon demand. A materialized view has many of the same properties as a table, but there is no support for temporary materialized views.\u003c/p\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE MATERIALIZED VIEW\u003c/code\u003e requires \u003ccode class=\"literal\"\u003eCREATE\u003c/code\u003e privilege on the schema used for the materialized view.\u003c/p\u003e","key":"description","title":"Description"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eIF NOT EXISTS\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eDo not throw an error if a materialized view with the same name already exists. A notice is issued in this case. Note that there is no guarantee that the existing materialized view is anything like the one that would have been created.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name (optionally schema-qualified) of the materialized view to be created. The name must be distinct from the name of any other relation (table, sequence, index, view, materialized view, or foreign table) in the same schema.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name of a column in the new materialized view. If column names are not provided, they are taken from the output column names of the query.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eUSING \u003cem class=\"replaceable\"\u003e\u003ccode\u003emethod\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThis optional clause specifies the table access method to use to store the contents for the new materialized view; the method needs be an access method of type \u003ccode class=\"literal\"\u003eTABLE\u003c/code\u003e. See \u003ca href=\"/docs/18/tableam.html\" title=\"Chapter 62. Table Access Method Interface Definition\"\u003eChapter 62\u003c/a\u003e for more information. If this option is not specified, the default table access method is chosen for the new materialized view. See \u003ca href=\"/docs/18/runtime-config-client.html#GUC-DEFAULT-TABLE-ACCESS-METHOD\"\u003edefault_table_access_method\u003c/a\u003e for more information.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eWITH ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003estorage_parameter\u003c/code\u003e\u003c/em\u003e [= \u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e] [, ... ] )\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThis clause specifies optional storage parameters for the new materialized view; see \u003ca href=\"/docs/18/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS\" title=\"Storage Parameters\"\u003eStorage Parameters\u003c/a\u003e in the \u003ca href=\"/docs/18/sql-createtable.html\" title=\"CREATE TABLE\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE TABLE\u003c/span\u003e\u003c/a\u003e documentation for more information. All parameters supported for \u003ccode class=\"literal\"\u003eCREATE TABLE\u003c/code\u003e are also supported for \u003ccode class=\"literal\"\u003eCREATE MATERIALIZED VIEW\u003c/code\u003e. See \u003ca href=\"/docs/18/sql-createtable.html\" title=\"CREATE TABLE\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE TABLE\u003c/span\u003e\u003c/a\u003e for more information.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eTABLESPACE \u003cem class=\"replaceable\"\u003e\u003ccode\u003etablespace_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe \u003cem class=\"replaceable\"\u003e\u003ccode\u003etablespace_name\u003c/code\u003e\u003c/em\u003e is the name of the tablespace in which the new materialized view is to be created. If not specified, \u003ca href=\"/docs/18/runtime-config-client.html#GUC-DEFAULT-TABLESPACE\"\u003edefault_tablespace\u003c/a\u003e is consulted.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eA \u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\"\u003e\u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e\u003c/a\u003e, \u003ca href=\"/docs/18/sql-select.html#SQL-TABLE\" title=\"TABLE Command\"\u003e\u003ccode class=\"command\"\u003eTABLE\u003c/code\u003e\u003c/a\u003e, or \u003ca href=\"/docs/18/sql-values.html\" title=\"VALUES\"\u003e\u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e\u003c/a\u003e command. This query will run within a security-restricted operation; in particular, calls to functions that themselves create temporary tables will fail. Also, while the query is running, the \u003ca href=\"/docs/18/runtime-config-client.html#GUC-SEARCH-PATH\"\u003esearch_path\u003c/a\u003e is temporarily changed to \u003ccode class=\"literal\"\u003epg_catalog, pg_temp\u003c/code\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eWITH [ NO ] DATA\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThis clause specifies whether or not the materialized view should be populated at creation time. If not, the materialized view will be flagged as unscannable and cannot be queried until \u003ccode class=\"command\"\u003eREFRESH MATERIALIZED VIEW\u003c/code\u003e is used.\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"Parameters"},{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE MATERIALIZED VIEW\u003c/code\u003e is a \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e extension.\u003c/p\u003e","key":"compatibility","title":"Compatibility"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/alter-materialized-view/?v=18\" title=\"ALTER MATERIALIZED VIEW\"\u003e\u003cspan class=\"refentrytitle\"\u003eALTER MATERIALIZED VIEW\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/create-table-as/?v=18\" title=\"CREATE TABLE AS\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE TABLE AS\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/create-view/?v=18\" title=\"CREATE VIEW\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE VIEW\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/drop-materialized-view/?v=18\" title=\"DROP MATERIALIZED VIEW\"\u003e\u003cspan class=\"refentrytitle\"\u003eDROP MATERIALIZED VIEW\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/refresh-materialized-view/?v=18\" title=\"REFRESH MATERIALIZED VIEW\"\u003e\u003cspan class=\"refentrytitle\"\u003eREFRESH MATERIALIZED VIEW\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"See Also"}],"sections_same_as":"","slug":"18","synopsis_html":"CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\n    [ (\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e [, ...] ) ]\n    [ USING \u003cem class=\"replaceable\"\u003e\u003ccode\u003emethod\u003c/code\u003e\u003c/em\u003e ]\n    [ WITH ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003estorage_parameter\u003c/code\u003e\u003c/em\u003e [= \u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e] [, ... ] ) ]\n    [ TABLESPACE \u003cem class=\"replaceable\"\u003e\u003ccode\u003etablespace_name\u003c/code\u003e\u003c/em\u003e ]\n    AS \u003cem class=\"replaceable\"\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e\n    [ WITH [ NO ] DATA ]","synopsis_text":"CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] table_name\n[ (column_name [, ...] ) ]\n[ USING method ]\n[ WITH ( storage_parameter [= value] [, ... ] ) ]\n[ TABLESPACE tablespace_name ]\nAS query\n[ WITH [ NO ] DATA ]"},"ManualEvidence":{},"MeasuredEvidence":{}},"Text":{"Collection":"sql","Key":"create-materialized-view","SourceDatabase":"pgweb","Version":"18","Locale":"zh-Hans","Title":"CREATE MATERIALIZED VIEW","Summary":"定义一个新物化视图","BodyHTML":"\u003cpre\u003eCREATE MATERIALIZED VIEW [ IF NOT EXISTS ] table_name\n[ (column_name [, ...] ) ]\n[ USING method ]\n[ WITH ( storage_parameter [= value] [, ... ] ) ]\n[ TABLESPACE tablespace_name ]\nAS query\n[ WITH [ NO ] DATA ]\u003c/pre\u003e\u003csection\u003e\u003ch2\u003e描述\u003c/h2\u003e\u003cp\u003e\u003ccode\u003eCREATE MATERIALIZED VIEW\u003c/code\u003e定义一个基于查询的物化视图。在发出该命令时，会执行该查询并用其结果填充该视图（除非使用了 \u003ccode\u003eWITH NO DATA\u003c/code\u003e），之后可以使用 \u003ccode\u003eREFRESH MATERIALIZED VIEW\u003c/code\u003e来刷新它。\u003c/p\u003e\u003cp\u003e\u003ccode\u003eCREATE MATERIALIZED VIEW\u003c/code\u003e类似于 \u003ccode\u003eCREATE TABLE AS\u003c/code\u003e，不过它还会记住用于初始化该视图的查询，以便后续按需刷新。物化视图具有很多与表相同的属性，但不支持临时物化视图。\u003c/p\u003e\u003cp\u003e\u003ccode\u003eCREATE MATERIALIZED VIEW\u003c/code\u003e要求对该物化视图所用的模式具有\u003ccode\u003eCREATE\u003c/code\u003e权限。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e参数\u003c/h2\u003e\u003cdiv\u003e\u003cdl\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eIF NOT EXISTS\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e如果已存在同名物化视图，则不要抛出错误。在这种情况下会发出一条提示。请注意，不能保证现有的物化视图与原本会创建出来的那个物化视图有任何相似之处。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要创建的物化视图名称（可以带模式限定）。该名称必须与同一模式中的任何其他关系（表、序列、索引、视图、物化视图或外部表）的名称不同。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e新物化视图中的列名。如果没有提供列名，则采用查询输出列的名称。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eUSING \u003cem\u003e\u003ccode\u003emethod\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该可选子句指定用于存储新物化视图内容的表访问方法；该方法必须是 \u003ccode\u003eTABLE\u003c/code\u003e类型的访问方法。详见\u003ca href=\"/docs/18/tableam.html\" rel=\"nofollow\"\u003e第 62 章\u003c/a\u003e。如果未指定此选项，则会为新物化视图选择默认表访问方法。详见\u003ca href=\"/docs/18/runtime-config-client.html#GUC-DEFAULT-TABLE-ACCESS-METHOD\" rel=\"nofollow\"\u003edefault_table_access_method\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eWITH ( \u003cem\u003e\u003ccode\u003estorage_parameter\u003c/code\u003e\u003c/em\u003e [= \u003cem\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e] [, ... ] )\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该子句为新物化视图指定可选的存储参数；更多信息见\u003ca href=\"/docs/18/sql-createtable.html\" title=\"CREATE TABLE\" rel=\"nofollow\"\u003e\u003cspan\u003eCREATE TABLE\u003c/span\u003e\u003c/a\u003e文档中的\u003ca href=\"/docs/18/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS\" title=\"存储参数\" rel=\"nofollow\"\u003eStorage Parameters\u003c/a\u003e。凡是 \u003ccode\u003eCREATE TABLE\u003c/code\u003e支持的参数，\u003ccode\u003eCREATE MATERIALIZED VIEW\u003c/code\u003e也同样支持。另见\u003ca href=\"/docs/18/sql-createtable.html\" title=\"CREATE TABLE\" rel=\"nofollow\"\u003e\u003cspan\u003eCREATE TABLE\u003c/span\u003e\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eTABLESPACE \u003cem\u003e\u003ccode\u003etablespace_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003cem\u003e\u003ccode\u003etablespace_name\u003c/code\u003e\u003c/em\u003e是在其中创建新物化视图的表空间名称。如果没有指定，将查阅\u003ca href=\"/docs/18/runtime-config-client.html#GUC-DEFAULT-TABLESPACE\" rel=\"nofollow\"\u003edefault_tablespace\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e一个\u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\" rel=\"nofollow\"\u003e\u003ccode\u003eSELECT\u003c/code\u003e\u003c/a\u003e、\u003ca href=\"/docs/18/sql-select.html#SQL-TABLE\" title=\"TABLE 命令\" rel=\"nofollow\"\u003e\u003ccode\u003eTABLE\u003c/code\u003e\u003c/a\u003e或 \u003ca href=\"/docs/18/sql-values.html\" title=\"VALUES\" rel=\"nofollow\"\u003e\u003ccode\u003eVALUES\u003c/code\u003e\u003c/a\u003e命令。该查询将在一种安全受限的操作中运行。特别是，调用那些自身会创建临时表的函数将会失败。另外，在该查询运行期间，\u003ca href=\"/docs/18/runtime-config-client.html#GUC-SEARCH-PATH\" rel=\"nofollow\"\u003esearch_path\u003c/a\u003e会被临时改为 \u003ccode\u003epg_catalog, pg_temp\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eWITH [ NO ] DATA\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该子句指定是否在创建时填充物化视图。如果不填充，则该物化视图会被标记为不可扫描，在使用\u003ccode\u003eREFRESH MATERIALIZED VIEW\u003c/code\u003e 之前不能查询。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e兼容性\u003c/h2\u003e\u003cp\u003e\u003ccode\u003eCREATE MATERIALIZED VIEW\u003c/code\u003e是一种 \u003cspan\u003ePostgreSQL\u003c/span\u003e扩展。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e另见\u003c/h2\u003e\u003cspan\u003e\u003ca href=\"/wiki/sql/alter-materialized-view/?v=18\" title=\"ALTER MATERIALIZED VIEW\" rel=\"nofollow\"\u003e\u003cspan\u003eALTER MATERIALIZED VIEW\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/create-table-as/?v=18\" title=\"CREATE TABLE AS\" rel=\"nofollow\"\u003e\u003cspan\u003eCREATE TABLE AS\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/create-view/?v=18\" title=\"CREATE VIEW\" rel=\"nofollow\"\u003e\u003cspan\u003eCREATE VIEW\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/drop-materialized-view/?v=18\" title=\"DROP MATERIALIZED VIEW\" rel=\"nofollow\"\u003e\u003cspan\u003eDROP MATERIALIZED VIEW\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/refresh-materialized-view/?v=18\" title=\"REFRESH MATERIALIZED VIEW\" rel=\"nofollow\"\u003e\u003cspan\u003eREFRESH MATERIALIZED VIEW\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e\u003c/section\u003e","SourceRevision":"1b5ca64c","ContentHash":"39fb34edb6e9418b4c19c41c89e07d3a4df72ece1e0b57822978de17cc71cc3c","Payload":{"purpose_zh":"定义一个新物化视图","sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE MATERIALIZED VIEW\u003c/code\u003e定义一个基于查询的物化视图。在发出该命令时，会执行该查询并用其结果填充该视图（除非使用了 \u003ccode class=\"command\"\u003eWITH NO DATA\u003c/code\u003e），之后可以使用 \u003ccode class=\"command\"\u003eREFRESH MATERIALIZED VIEW\u003c/code\u003e来刷新它。\u003c/p\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE MATERIALIZED VIEW\u003c/code\u003e类似于 \u003ccode class=\"command\"\u003eCREATE TABLE AS\u003c/code\u003e，不过它还会记住用于初始化该视图的查询，以便后续按需刷新。物化视图具有很多与表相同的属性，但不支持临时物化视图。\u003c/p\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE MATERIALIZED VIEW\u003c/code\u003e要求对该物化视图所用的模式具有\u003ccode class=\"literal\"\u003eCREATE\u003c/code\u003e权限。\u003c/p\u003e","key":"description","title":"描述"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eIF NOT EXISTS\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e如果已存在同名物化视图，则不要抛出错误。在这种情况下会发出一条提示。请注意，不能保证现有的物化视图与原本会创建出来的那个物化视图有任何相似之处。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要创建的物化视图名称（可以带模式限定）。该名称必须与同一模式中的任何其他关系（表、序列、索引、视图、物化视图或外部表）的名称不同。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e新物化视图中的列名。如果没有提供列名，则采用查询输出列的名称。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eUSING \u003cem class=\"replaceable\"\u003e\u003ccode\u003emethod\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该可选子句指定用于存储新物化视图内容的表访问方法；该方法必须是 \u003ccode class=\"literal\"\u003eTABLE\u003c/code\u003e类型的访问方法。详见\u003ca href=\"/docs/18/tableam.html\" title=\"第 62 章 表访问方法接口定义\"\u003e第 62 章\u003c/a\u003e。如果未指定此选项，则会为新物化视图选择默认表访问方法。详见\u003ca href=\"/docs/18/runtime-config-client.html#GUC-DEFAULT-TABLE-ACCESS-METHOD\"\u003edefault_table_access_method\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eWITH ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003estorage_parameter\u003c/code\u003e\u003c/em\u003e [= \u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e] [, ... ] )\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该子句为新物化视图指定可选的存储参数；更多信息见\u003ca href=\"/docs/18/sql-createtable.html\" title=\"CREATE TABLE\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE TABLE\u003c/span\u003e\u003c/a\u003e文档中的\u003ca href=\"/docs/18/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS\" title=\"存储参数\"\u003eStorage Parameters\u003c/a\u003e。凡是 \u003ccode class=\"literal\"\u003eCREATE TABLE\u003c/code\u003e支持的参数，\u003ccode class=\"literal\"\u003eCREATE MATERIALIZED VIEW\u003c/code\u003e也同样支持。另见\u003ca href=\"/docs/18/sql-createtable.html\" title=\"CREATE TABLE\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE TABLE\u003c/span\u003e\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eTABLESPACE \u003cem class=\"replaceable\"\u003e\u003ccode\u003etablespace_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003etablespace_name\u003c/code\u003e\u003c/em\u003e是在其中创建新物化视图的表空间名称。如果没有指定，将查阅\u003ca href=\"/docs/18/runtime-config-client.html#GUC-DEFAULT-TABLESPACE\"\u003edefault_tablespace\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e一个\u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\"\u003e\u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e\u003c/a\u003e、\u003ca href=\"/docs/18/sql-select.html#SQL-TABLE\" title=\"TABLE 命令\"\u003e\u003ccode class=\"command\"\u003eTABLE\u003c/code\u003e\u003c/a\u003e或 \u003ca href=\"/docs/18/sql-values.html\" title=\"VALUES\"\u003e\u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e\u003c/a\u003e命令。该查询将在一种安全受限的操作中运行。特别是，调用那些自身会创建临时表的函数将会失败。另外，在该查询运行期间，\u003ca href=\"/docs/18/runtime-config-client.html#GUC-SEARCH-PATH\"\u003esearch_path\u003c/a\u003e会被临时改为 \u003ccode class=\"literal\"\u003epg_catalog, pg_temp\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eWITH [ NO ] DATA\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该子句指定是否在创建时填充物化视图。如果不填充，则该物化视图会被标记为不可扫描，在使用\u003ccode class=\"command\"\u003eREFRESH MATERIALIZED VIEW\u003c/code\u003e 之前不能查询。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"参数"},{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE MATERIALIZED VIEW\u003c/code\u003e是一种 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e扩展。\u003c/p\u003e","key":"compatibility","title":"兼容性"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/alter-materialized-view/?v=18\" title=\"ALTER MATERIALIZED VIEW\"\u003e\u003cspan class=\"refentrytitle\"\u003eALTER MATERIALIZED VIEW\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/create-table-as/?v=18\" title=\"CREATE TABLE AS\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE TABLE AS\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/create-view/?v=18\" title=\"CREATE VIEW\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE VIEW\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/drop-materialized-view/?v=18\" title=\"DROP MATERIALIZED VIEW\"\u003e\u003cspan class=\"refentrytitle\"\u003eDROP MATERIALIZED VIEW\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/refresh-materialized-view/?v=18\" title=\"REFRESH MATERIALIZED VIEW\"\u003e\u003cspan class=\"refentrytitle\"\u003eREFRESH MATERIALIZED VIEW\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"另见"}],"sections_same_as":"","synopsis_html":"CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\n    [ (\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e [, ...] ) ]\n    [ USING \u003cem class=\"replaceable\"\u003e\u003ccode\u003emethod\u003c/code\u003e\u003c/em\u003e ]\n    [ WITH ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003estorage_parameter\u003c/code\u003e\u003c/em\u003e [= \u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e] [, ... ] ) ]\n    [ TABLESPACE \u003cem class=\"replaceable\"\u003e\u003ccode\u003etablespace_name\u003c/code\u003e\u003c/em\u003e ]\n    AS \u003cem class=\"replaceable\"\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e\n    [ WITH [ NO ] DATA ]","synopsis_text":"CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] table_name\n[ (column_name [, ...] ) ]\n[ USING method ]\n[ WITH ( storage_parameter [= value] [, ... ] ) ]\n[ TABLESPACE tablespace_name ]\nAS query\n[ WITH [ NO ] DATA ]"}},"RequestedLocale":"zh-Hans","Fallback":false,"Versions":["10","11","12","13","14","15","16","17","18","19","20","9.3","9.4","9.5","9.6"],"Locales":["en","zh-Hans"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}