What does CREATE TABLE AS SELECT copy from the source query, and what does it leave behind?

## The statement `CREATE TABLE ... AS SELECT`, universally shortened to CTAS, is a single statement that does two things: it defines a new table and it populates it. Crucially, it defines that table from a **query result**, not from a source table's definition. ```sql CREATE TABLE order_summary AS SELECT customer_id, COUNT(*) AS order_count, SUM(net_amount) AS total_net FROM orders GROUP BY customer_id; ``` The resulting table has three columns because the query produced three, and it holds one row per customer because the query did. ## Where the column names come from The new table's column names are the output column names of the select list. A plain column reference carries its own name across. An expression has no natural name, so it needs an explicit alias — `COUNT(*) AS order_count` above. Without the alias, some engines invent a placeholder name and others reject the statement outright, and either outcome makes the table awkward to query afterwards. Aliasing every computed column is not stylistic advice here; it is part of writing a correct CTAS. Most engines also let you override the names in the statement itself: ```sql CREATE TABLE order_summary (customer_id, order_count, total_net) AS SELECT customer_id, COUNT(*), SUM(net_amount) FROM orders GROUP BY customer_id; ``` ## Where the column types come from Types are inferred from the expressions. `customer_id` keeps the source column's type. `COUNT(*)` becomes whatever integer type the engine uses for counts. `SUM(net_amount)` becomes a widened numeric type, which may not equal the type of `net_amount`. Concatenations, `CASE` expressions and arithmetic all produce engine-determined types, and precision or length may differ from what you expected. If the resulting types matter — because another process reads the table, or because it feeds a later join — cast explicitly in the select list rather than trusting inference: ```sql SELECT CAST(SUM(net_amount) AS DECIMAL(12,2)) AS total_net ``` ## What is not copied This is the heart of the question. Because the input is a result set, anything that exists only as a property of the original table cannot travel: - primary keys and unique constraints - foreign keys, in either direction - `CHECK` constraints - indexes - column `DEFAULT` clauses - identity / auto-increment behaviour and the sequence behind it - triggers, comments and privileges In PostgreSQL the new columns do not even inherit `NOT NULL`. Engines differ in small details — some carry over more column attributes than others — so the safe mental model is: **CTAS gives you columns, types and rows, and you should assume nothing else.** ## WITH DATA and WITH NO DATA Standard SQL lets the statement say explicitly whether rows are copied: ```sql CREATE TABLE order_summary AS SELECT ... WITH NO DATA; ``` `WITH DATA` is the default meaning; `WITH NO DATA` creates the empty shell. PostgreSQL supports both. Not every engine accepts the clause, and where it is missing the portable equivalent is a predicate that matches nothing: ```sql CREATE TABLE order_summary AS SELECT ... FROM orders WHERE 1 = 0; ``` That produces the same empty, constraint-free shell. If what you actually want is a structural clone *with* the source's defaults and nullability, CTAS is the wrong tool — the `LIKE` table element exists for that. ## Dialect spellings The CTAS shape is widely available, but not universally spelled the same way. Microsoft SQL Server's traditional form is `SELECT ... INTO new_table FROM ...`, which is a `SELECT` statement that creates its target rather than a `CREATE TABLE` statement. Because the spelling varies, a migration script that must run on more than one engine is one of the few places where writing the `CREATE TABLE` and a separate `INSERT ... SELECT` is genuinely the more portable choice. ## When CTAS is the right tool CTAS shines where the output *is* a result set and nothing more is expected of it: materialising a heavy aggregate for a report, staging a transformed extract, capturing a quick working set during an investigation. It is a poor fit whenever the new table will be written to by an application, joined on heavily, or relied upon for integrity — in those cases write the real `CREATE TABLE` with its keys, defaults and constraints, then load it with `INSERT ... SELECT`.