Cloud

CREATE VIEW

The CREATE VIEW statement creates a view, a named SELECT query that you can reference like a table.

Syntax

CREATE VIEW [IF NOT EXISTS] [schema_name.]view_name AS select_statement;

  • IF NOT EXISTS: Optional. If a table or view with the same name already exists, the statement does nothing instead of returning an error. Redpanda SQL still validates the query.

  • schema_name: Optional. The Redpanda SQL schema to create the view in. If you omit it, Redpanda SQL creates the view in the current schema, such as public.

  • view_name: Name of the view. Views and tables share names in a schema, so a view cannot have the same name as an existing table or view.

  • select_statement: The SELECT query that defines the view. The query can read tables in a Redpanda catalog and other views. It can use joins, common table expressions (WITH), UNION, ORDER BY, and LIMIT.

How views work

  • A view stores no data. Redpanda SQL stores the query, not its results. Each time you query the view, Redpanda SQL runs the stored query, so the results reflect the current data.

  • A view is read-only. You cannot insert rows into it.

  • A view can read other views.

  • You create a view in a Redpanda SQL schema, not in a catalog. This applies to Redpanda catalogs and Iceberg catalogs. A view name that uses the catalog=> form fails with views on external catalogs are not supported.

  • To query a view, use its name with or without its schema, such as large_orders or public.large_orders. Do not add a catalog name.

  • Redpanda SQL resolves unqualified table names in the query with the search path that was in effect when you created the view.

  • A common table expression with the same name as a view hides the view in that query. To reach the view, qualify it with its schema, such as public.large_orders.

Column shape

Redpanda SQL stores the view’s column names and types when you create the view. Every later query of the view must produce the same columns.

If a table that the view reads gains a column, a view that uses SELECT * no longer matches. Queries of the view then fail with an error that ends with drop and recreate the view. To avoid this, list the columns in the view’s query instead of using SELECT *.

Dependencies

Redpanda SQL tracks which views a view reads:

  • You cannot drop a view while another view reads it. Drop the dependent view first. The DROP VIEW statement has no CASCADE option.

  • Redpanda SQL does not track tables in a Redpanda catalog or an Iceberg catalog. You can drop such a table while a view reads it. Queries of the view then fail.

Permissions

  • To create a view, you need the CREATE privilege on the schema and the SELECT privilege on each table and view that the query reads. You become the owner of the view.

  • To query a view, a user needs only the SELECT privilege on the view. Redpanda SQL runs the view’s query with the privileges of the view owner. If the owner loses access to a table that the view reads, queries of the view fail for every user.

  • To change the owner, use ALTER TABLE view_name OWNER TO role_name. Use TABLE in this statement, even for a view.

  • To let another role query the view, use GRANT: GRANT SELECT ON TABLE view_name TO role_name. Use TABLE in this statement, even for a view. There is no ON VIEW form.

A user who can query a view can read the data that the view returns, even without a grant on the tables that the view reads. Grant SELECT on a view only to users who can see that data.

Limitations

  • CREATE OR REPLACE VIEW and ALTER VIEW are not supported. To change a view, drop it and create it again.

  • A column name list, such as CREATE VIEW v (a, b) AS …​, is not supported. Rename columns with aliases in the query instead, such as SELECT customer AS buyer.

  • Temporary views, recursive views, and WITH CHECK OPTION are not supported.

  • information_schema.tables and the psql commands \d and \dv do not list views. To list views, query pg_views.

Examples

These examples use an orders table in default_redpanda_catalog, mapped from a Redpanda topic with the columns order_id, customer, product, amount, and status.

Create a view

Create a view of large orders:

CREATE VIEW large_orders AS
SELECT order_id, customer, amount
FROM default_redpanda_catalog=>orders
WHERE amount > 100;

Query the view like a table:

SELECT customer, count(*) AS order_count
FROM large_orders
GROUP BY customer;

Create a view that reads another view

Create a view of the 10 customers with the highest total in large_orders:

CREATE VIEW top_customers AS
SELECT customer, sum(amount) AS total
FROM large_orders
GROUP BY customer
ORDER BY total DESC
LIMIT 10;

Create a view only if it does not exist

Create the large_orders view only if no table or view with that name exists. If one exists, the statement does nothing:

CREATE VIEW IF NOT EXISTS large_orders AS
SELECT order_id, customer, amount
FROM default_redpanda_catalog=>orders
WHERE amount > 100;

Let another role query a view

Grant SELECT on the large_orders view to another role:

GRANT SELECT ON TABLE large_orders TO "analyst@example.com";

Suggested reading