Materialize Documentation
s
Join the Community github/materialize
Home  /  Reference

CREATE VIEWS

New in v0.8.0.

CREATE VIEWS creates views for the distinct components of a multiplexed stream. For example, it separates the replication stream of a Postgres source out into views that reproduce the upstream tables that populate the stream.

CREATE VIEWS is distinct from both the more general CREATE VIEW command, which provides an alias for SELECT statements, and from materialized views.

Syntax

CREATE MATERIALIZED TEMP TEMPORARY VIEWS IF NOT EXISTS FROM SOURCE " src_name " ( " upstream_table " AS " new_view_name " )
Field Use
MATERIALIZED Create the view as materialized. To minimize memory usage, we recommend that you first create nonmaterialized views to see tables instead of the multiplexed data stream and only materialize the views for the queries on those initial table-like views.
TEMP / TEMPORARY Mark the view as temporary.
IF NOT EXISTS If specified, do not generate an error if a view of the same name already exists.

If not specified, throw an error if a view of the same name already exists. (Default)
src_name The name of the Postgres source for which you are creating table views.
upstream_table Optional. The names of the upstream table for which to create a view. You can include multiple tables as a comma-separated list. If unspecified, views will be created for all tables in the publication.
new_view_name Optional. The name for the new view. If unspecified, the name of the upstream table will be used.

Details

Postgres schemas

For Postgres sources, CREATE VIEWS will attempt to create each upstream table in the same schema as Postgres. If the publication contains tables "public"."foo" and "otherschema"."foo", CREATE VIEWS is the equivalent of

CREATE VIEW "public"."foo";
CREATE VIEW "otherschema"."foo";

Therefore, in order for CREATE VIEWS to succeed, all upstream schemas included in the publication must exist in Materialize as well, or you must explicitly specify the downstream schemas and rename the resulting tables.

For example:

CREATE VIEWS FROM "mz_source"
("public"."foo" AS "foo", "otherschema"."foo" AS "foo2");

Temporary views

The TEMP/TEMPORARY keyword creates temporary views. Temporary views are automatically dropped at the end of the SQL session and are not visible to other connections. They are always created in the special mz_temp schema.

Temporary views may depend upon other temporary database objects, but non-temporary views may not depend on temporary objects.

Examples

Creating views for all tables included in the publication

CREATE VIEWS FROM SOURCE "mz_source";
SHOW FULL VIEWS;

Creating views for specific tables

This command creates views from table1 and table2.

CREATE VIEWS FROM SOURCE "mz_source" ("table1", "table2");
SHOW FULL VIEWS;

Creating views and renaming tables

CREATE VIEWS FROM "mz_source"
("public"."table1" AS "table1", "otherschema"."table1" AS "table2");