Inserting Data Into Materialized Tables¶

There are two ways of inserting data into a materialized view:

Syntax 1

INSERT INTO <materialized table> ( <fields> )
VALUES (<values> ) [, ( <values> ) ]*

Inserts one or more rows into a materialized table.

This statement supports the same options as the one available for base and derived views (see Syntax of the INSERT statement) with the addition than you can insert several rows within the same statement. For example:

INSERT INTO usa_state (name, abbreviation, capital) VALUES
  ('Arizona', 'AZ', 'Phoenix')
, ('California', 'CA', 'Sacramento')
, ('New York', 'NY', 'Albany')
, ('North Dakota', 'ND', 'Bismarck');

Syntax 2

INSERT INTO <materialized table> ( <SELECT clause> )

Inserts the result of a SELECT clause into a materialized table. The benefit over the previous syntax is that with this one, you can insert several rows at once.

The result of the query has to have the same schema as the materialized table and in the result, the fields must be in the same order as in the target table.

INSERT INTO usa_state (
  SELECT 'Arizona', 'AZ', 'Phoenix'
);