Defines a new materialized view.
CREATE MATERIALIZED VIEW <table_name>
[ (<column_name> [, ...] ) ]
[ WITH ( <storage_parameter> [= <value>] [, ... ] ) ]
[ TABLESPACE <tablespace_name> ]
AS <query>
[ WITH [ NO ] DATA ]
[DISTRIBUTED {| BY <column> [<opclass>], [ ... ] | RANDOMLY | REPLICATED }]
CREATE MATERIALIZED VIEW
defines a materialized view of a query. The query is run and used to populate the view at the time the command is issued (unless WITH NO DATA
is used) and can be refreshed using REFRESH MATERIALIZED VIEW
.
CREATE MATERIALIZED VIEW
is similar to CREATE TABLE AS
, except that it also remembers the query used to initialize the view, so that it can be refreshed later upon demand. To refresh materialized view data, use the REFRESH MATERIALIZED VIEW
command. A materialized view has many of the same properties as a table, but there is no support for temporary materialized views or automatic generation of OIDs.
CREATE TABLE
are also supported for
CREATE MATERIALIZED VIEW
with the exception of OIDS. See
CREATE TABLE for more information.
WITH DATA
is the default, populate the materialized view. For
WITH NO DATA
, the materialized view is not populated with data, is flagged as unscannable, and cannot be queried until
REFRESH MATERIALIZED VIEW
is used to populate the materialized view. An error is returned if a query attempts to access an unscannable materialized view.
Materialized views are read only. The system will not allow an INSERT
, UPDATE
, or DELETE
on a materialized view. Use REFRESH MATERIALIZED VIEW
to update the materialized view data.
If you want the data to be ordered upon generation, you must use an ORDER BY
clause in the materialized view query. However, if a materialized view query contains an ORDER BY
or SORT
clause, the data is not guaranteed to be ordered or sorted if SELECT
is performed on the materialized view.
Create a view consisting of all comedy films:
CREATE MATERIALIZED VIEW comedies AS SELECT * FROM films
WHERE kind = 'comedy';
This will create a view containing the columns that are in the film
table at the time of view creation. Though *
was used to create the materialized view, columns added later to the table will not be part of the view.
Create a view that gets the top ten ranked baby names:
CREATE MATERIALIZED VIEW topten AS SELECT name, rank, gender, year FROM
names, rank WHERE rank < '11' AND names.id=rank.id;
CREATE MATERIALIZED VIEW
is a Greenplum Database extension of the SQL standard.
SELECT, VALUES, CREATE VIEW, ALTER MATERIALIZED VIEW, DROP MATERIALIZED VIEW, REFRESH MATERIALIZED VIEW
Parent topic: SQL Commands