This is the 18th day of my participation in the August Genwen Challenge.More challenges in August

How materialized view names are defined

view_name::= re(‘[a-zA-Z_0-9]+’)

Create materialized views

create_materialized_view_statement::= CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] view_name 
    AS select_statement 
    PRIMARY KEY '(' primary_key')' 
    WITH table_options

Copy the code

A chestnut

CREATE MATERIALIZED VIEW monkeySpecies_by_population AS
    SELECT * FROM monkeySpecies 
    WHERE population IS NOT NULL AND species IS NOT NULL 
    PRIMARY KEY (population, species) 
    WITH comment='Allow query by population instead of species';
Copy the code

The CREATE MATERIALIZED VIEW statement creates a new MATERIALIZED VIEW. Each such view is a set of rows that correspond to rows that exist in the underlying or base table as specified in the SELECT statement. Materialized views cannot be updated directly, but updates to the base table cause corresponding updates to the view.

There are three main parts to creating materialized views:

  • Select statements that restrict the data contained in a view.
  • Primary key definition for a view.
  • View options.

Attempts to create an existing materialized view will return an error unless this IF NOT EXISTS option is used. If used, the statement will be a null operation if the materialized view already exists.

Materialized view SELECT

The SELECT statement that creates a materialized view defines which base tables are contained in the view. This statement is limited in a number of ways:

  • The selection is limited to those that select only columns in the base table. In other words, you can’t use any functions (aggregation or not), casts, terminology, etc. Aliases are also not supported. However, you can use * as a shortcut to select all columns. In addition, static columns cannot be included in materialized views. Therefore, SELECT * disallows commands if the base table has static columns. The WHERE clause has the following restrictions:

    • Can’t include anybind_marker
    • You cannot have columns that do not belong to a partThe base tableNot by a limited primary keyIS NOT NULLlimit
    • No other restrictions are allowed
    • Can’t let belong toviewThe primary keyIS NOT NULLColumn empty, they must always be at least limited (or any other limit, but they must have a limit).

  • Cannot have a limit or ALLOW FILTERING clause

Materialized view primary key

The view must have a primary key, and the primary key must meet the following restrictions:

  • It must contain all primary key columns of the base table. This ensures that each row of the view corresponds to a row of the base table.
  • It can contain only one column that is not the primary key column in the base table.

For example, give the following base table definition:

CREATE TABLE t (
    k int,
    c1 int,
    c2 int,
    v1 int,
    v2 int,
    PRIMARY KEY (k, c1, c2)
);
Copy the code

The following view definitions are allowed:

CREATE MATERIALIZED VIEW mv1 AS
   SELECT * FROM t
   WHERE k IS NOT NULL AND c1 IS NOT NULL AND c2 IS NOT NULL
   PRIMARY KEY (c1, k, c2);

CREATE MATERIALIZED VIEW mv1 AS
  SELECT * FROM t
  WHERE k IS NOT NULL AND c1 IS NOT NULL AND c2 IS NOT NULL
  PRIMARY KEY (v1, k, c1, c2);
Copy the code

But none of the following would be allowed:

// Error: cannot include both v1 and v2 in the primary key as both are not in the base table primary key

CREATE MATERIALIZED VIEW mv1 AS
   SELECT * FROM t
   WHERE k IS NOT NULL AND c1 IS NOT NULL AND c2 IS NOT NULL AND v1 IS NOT NULL
   PRIMARY KEY (v1, v2, k, c1, c2);

// Error: must include k in the primary as it's a base table primary key column

CREATE MATERIALIZED VIEW mv1 AS
   SELECT * FROM t
   WHERE c1 IS NOT NULL AND c2 IS NOT NULL
   PRIMARY KEY (c1, c2);
Copy the code

Materialized view Options

Materialized views are implemented internally by tables, so creating an MV allows same options than creating a table

.

Change the materialized view

Once created, you can change the MATERIALIZED VIEW options using the following ALTER MATERIALIZED VIEW statements:

alter_materialized_view_statement::= ALTER MATERIALIZED VIEW view_name WITH table_options
Copy the code

The options that can be updated are the same as when they were created, so same than for tables

.

Delete materialized views

DROP the MATERIALIZED VIEW using the following DROP MATERIALIZED VIEW statement:

drop_materialized_view_statement::= DROP MATERIALIZED VIEW [ IF EXISTS ] view_name;
Copy the code

IF the materialized view does not exist, this statement returns an error unless IF EXISTS uses the operation in which case there is no operation