Skip to main content

CREATE VIEW

Use the CREATE VIEW command to create a non-materialized view, which runs every time the view is referenced in a query. A non-materialized view can be created based on sources, tables, views, or indexes.

Syntax

CREATE VIEW [IF NOT EXISTS] view_name [ ( column_name [, ...] ) ] AS select_query;

Parameters

ParameterDescription
mv_nameThe name of the view to be created.
column_nameSpecify the columns of the view.
select_queryA SELECT query that retrieves data for the view. See SELECT for the syntax and examples of the SELECT command.

Examples

The following statements create views based a plain table and a table with connector settings, and then create a new view based on the existing views. The data for the table is generated by the built-in load generator.

-- Create a table and add some records.

CREATE TABLE t1 (a int, b int, c int);

INSERT INTO t1 VALUES (115, 1, 8), (585, 2, 3), (601, 3, 7);

-- Create a source (whose data is generated by the built-in generator).

CREATE TABLE s1 (i1 int, c1 varchar)
WITH (
connector = 'datagen',
fields.i1.kind = 'sequence',
fields.i1.start = '1',
fields.c1.kind = 'random',
fields.c1.length = '16',
fields.c1.seed = '3',
datagen.rows.per.second = '10'
) FORMAT PLAIN ENCODE JSON;

-- Create views based on the table, source, and existing views.

CREATE VIEW IF NOT EXISTS v1 (a1, b1) AS SELECT a, b FROM t1;

CREATE VIEW v2 AS SELECT * FROM s1 ORDER BY i1;

CREATE VIEW v3 AS SELECT a1, i1, c1 FROM v1 LEFT JOIN v2 ON v1.b1=v2.i1;

Let's query view v3.

SELECT * FROM v3;
 a1  | i1 |        c1        
-----+----+------------------
115 | 1 | pGWJLsbmPJZZWpBe
585 | 2 | FT7BRdifYMrRgIyI
601 | 3 | 0zsMbNLxQh9yYtHh
note

Names and unquoted identifiers are case-insensitive. Therefore, you must double-quote any of these fields for them to be case-sensitive. See also Identifiers.

Help us make this doc better!