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.
Overview
CTAS combines table creation and data loading into a single, atomic statement — no need to declare column types up front, since they're inferred from the query's result:
Copy code
CREATE TABLE monthly_revenue AS
SELECT DATE_TRUNC('month', order_date) AS month, SUM(revenue) AS revenue
FROM orders
GROUP BY ALL;
This is the standard pattern for materializing intermediate results, snapshotting a table at a point in time, or restructuring data (e.g., pivoting or denormalizing) into a new physical table for faster downstream querying.
CTAS vs a view
A CTAS-created table stores a physical copy of the query's result at creation time — it does not update automatically if the underlying source tables change. A VIEW, by contrast, stores only the query definition and re-executes it on every read. Use CTAS when you want to freeze/materialize a result for repeated fast access; use a view when you always want the latest data and can tolerate re-computing it per query.
DuckDB specifics
DuckDB supports standard CREATE TABLE ... AS SELECT ..., plus CREATE OR REPLACE TABLE ... AS SELECT ... to atomically replace an existing table (dropping and recreating it) in one statement, which is convenient in ELT scripts that rebuild a table on every run:
Copy code
CREATE OR REPLACE TABLE monthly_revenue AS
SELECT DATE_TRUNC('month', order_date) AS month, SUM(revenue) AS revenue
FROM orders
GROUP BY ALL;
DuckDB also supports CREATE TEMP TABLE ... AS SELECT ... for a session-scoped materialization, and CTAS works seamlessly with DuckDB's file-reading functions, so you can materialize a table directly from external files: CREATE TABLE t AS SELECT * FROM read_parquet('s3://bucket/data/*.parquet').
Related terms
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,…
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.
SELECT statement →The SELECT statement is the workhorse of SQL, used to retrieve and transform data from tables, views, or other data sources.
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.
table →A table is a fundamental structure in a relational database that organizes data into rows and columns, similar to a spreadsheet.
ALTER TABLE statement →The ALTER TABLE statement allows you to modify the structure of an existing database table without having to recreate it from scratch.
FAQS
No. CTAS materializes the query result as a physical table at creation time; it does not automatically refresh if the source tables later change. Use CREATE OR REPLACE TABLE ... AS SELECT to rebuild it, or use a VIEW if you always want live results.
Yes. CREATE OR REPLACE TABLE new_table AS SELECT ... atomically drops the existing table (if any) and recreates it from the query result, which is a common pattern in scripts that rebuild derived tables on each run.
