CREATE VIEW

Synopsis

Use the CREATE VIEW statement to create a view in a database. It defines the view name and the (select) statement defining it.

Syntax

create_view ::= CREATE [ OR REPLACE ] [ TEMPORARY | TEMP ] VIEW 
                qualified_name  [ ( column_name [ , ... ] ) ] AS 
                select

create_view

CREATEORREPLACETEMPORARYTEMPVIEWqualified_name(,column_name)ASselect

Semantics

Create a view.

qualified_name

Specify the name of the view. An error is raised if view with that name already exists in the specified database (unless the OR REPLACE option is used).

column_list

Specify a comma-separated list of columns. If not specified, the column names are deduced from the query.

select

Specify a SELECT or VALUES statement that will provide the columns and rows of the view.

TEMPORARY or TEMP

Using this qualifier will create a temporary view. Temporary views are visible only in the current client session in which they are created and are automatically dropped at the end of the session. See the section Creating and using temporary schema-objects.

Examples

Create a sample table.

CREATE TABLE sample(k1 int, k2 int, v1 int, v2 text, PRIMARY KEY (k1, k2));

Insert some rows.

INSERT INTO sample(k1, k2, v1, v2) VALUES (1, 2.0, 3, 'a'), (2, 3.0, 4, 'b'), (3, 4.0, 5, 'c');

Create a view on the sample table.

CREATE VIEW sample_view AS SELECT * FROM sample WHERE v2 != 'b' ORDER BY k1 DESC;

Select from the view.

yugabyte=# SELECT * FROM sample_view;

 k1 | k2 | v1 | v2
----+----+----+----
  3 |  4 |  5 | c
  1 |  2 |  3 | a
(2 rows)

See also