CREATE VIEW

The CREATE VIEW statement creates a new view that exposes the result of a query as a named relation.

A view stores only the query text and plans it afresh on every reference, so nothing is precomputed. If you want the result stored as a physical table and kept up to date automatically as its sources change, use CREATE MATERIALIZED VIEW instead.

The defining query is resolved when the view is created, and the columns it produces — their names and their types — are recorded alongside it, so tools can describe the view without planning its SQL. This means the query must be valid at the moment you create the view: every relation it reads has to exist and be readable by you. A view cannot be defined ahead of the tables it reads.

Syntax

sql

CREATE [ OR REPLACE ] VIEW [ IF NOT EXISTS ] <workspace>.<collection>.<view_name> AS
SELECT ...;

Parameters

<workspace>.<collection>.<view_name> — fully qualified name of the view to create.
OR REPLACE — overwrite an existing view's definition instead of failing if one already exists under this name.
IF NOT EXISTS — leave an existing view untouched instead of failing if one already exists under this name. A true no-op: a second CREATE VIEW IF NOT EXISTS with a different body does not take effect. Cannot be combined with OR REPLACE — the first always redefines, the second only ever no-ops.

Examples

Basic View

sql

CREATE VIEW my_workspace.my_collection.active_users AS
SELECT id, name, email, created_at
  FROM users
 WHERE active = TRUE;

Complex View with Joins

sql

CREATE VIEW sales.analytics.order_summary AS
SELECT 
  o.order_id,
  c.customer_name,
  o.order_date,
  o.amount,
  COUNT(*) OVER (PARTITION BY c.customer_id) AS customer_order_count
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'completed';

View Based on Another View

sql

CREATE VIEW my_workspace.my_collection.high_value_customers AS
SELECT customer_id, SUM(amount) AS total_spent
FROM order_summary
GROUP BY customer_id
HAVING SUM(amount) > 10000;

Notes

Views are virtual relations defined by queries; they don't store data.
Use fully qualified names: <workspace>.<collection>.<view_name>.
Views are read-only in most contexts.
View definitions are stored and can be modified with ALTER VIEW or removed with DROP VIEW.
The recorded columns describe the definition as it was written. A view defined with SELECT * records the columns its sources had at that moment; the view itself still expands the * on every read, so what it returns always follows the sources, and only the recorded description can fall behind. Redefining the view refreshes it.