view

Q: How do you create a view in DuckDB?

CREATE VIEW name AS SELECT ... . Views can define column aliases and can reference other views to build layered abstractions.

A view is a virtual table based on the result of a SQL query.

Definition

A view is a virtual table based on the result of a SQL query. Unlike physical tables that store data directly, a view acts as a stored query that appears as a table when referenced. Views provide a way to simplify complex queries, restrict access to underlying data, and create logical abstractions of your data model.

Usage in DuckDB

DuckDB supports creating views using the CREATE VIEW syntax. Views in DuckDB are not materialized - they run their underlying query each time they are accessed. Here are some examples:

Basic view creation:

Copy code
CREATE VIEW monthly_sales AS
SELECT 
    date_trunc('month', sale_date) as month,
    sum(amount) as total_sales
FROM sales
GROUP BY 1;

View with column aliases:

Copy code
CREATE VIEW customer_summary (customer_name, total_spent, order_count) AS
SELECT 
    name,
    sum(order_total),
    count(*)
FROM orders
GROUP BY name;

Views can also reference other views:

Copy code
CREATE VIEW high_value_customers AS
SELECT *
FROM customer_summary
WHERE total_spent > 10000;

Key Features

Unlike some databases that support materialized views (which store the results), DuckDB views are always virtual and compute their results on demand. This means they always return current data but may have performance implications for complex view definitions. Views can be particularly useful when working with DuckDB's support for directly querying files like CSV and Parquet, as they can provide a cleaner interface over file-based tables.

Common Use Cases

Views are commonly used to:

Simplify complex joins or aggregations into a simple logical table
Create standardized filters or transformations that can be reused
Hide complexity of underlying data structures
Provide row or column level security by exposing only certain data through views
Create backward compatibility when schema changes occur

Related terms

Materialized view →

A materialized view stores the physical, precomputed result of a query as if it were a table, so reads are fast, at the cost of needing an explicit refresh when the underlying data changes.

CREATE TABLE AS SELECT (CTAS) →

CREATE TABLE ... AS SELECT (CTAS) creates a new table and populates it in one statement, using the result of a query to define both its schema and its data.

table →

A table is a fundamental structure in a relational database that organizes data into rows and columns, similar to a spreadsheet.

schema →

A schema is a logical container or blueprint that defines how data is organized within a database.

CREATE TABLE statement →

The CREATE TABLE statement is a fundamental SQL command that defines a new table in a database, specifying its structure including column names, data types,…

Denormalized table →

A denormalized table is a table that intentionally stores redundant, pre-joined data from multiple normalized sources, trading storage and update simplicity for faster, simpler reads.

FAQS

A view is a virtual table defined by a stored SQL query. It appears like a table when referenced but doesn't store data itself—it runs its underlying query on access.

CREATE VIEW name AS SELECT .... Views can define column aliases and can reference other views to build layered abstractions.

A table physically stores data; a view is a saved query evaluated each time it's used. Views simplify complex queries and restrict access without duplicating data. DuckDB views are not materialized.