How do you create an empty table with the same structure as an existing one, keeping its column defaults?

## Two statements, two meanings of "copy" Asked to clone a table, most people reach for `CREATE TABLE ... AS SELECT`. That is often the wrong tool, because CTAS is defined over a query result. It gives you the columns the select list produced, with the types the engine inferred, and none of the definition around them. If the intent was "give me another table shaped like this one", the `LIKE` table element is the construct that actually says that. ```sql -- shape of a result set: no defaults, no keys, no indexes CREATE TABLE staging_orders AS SELECT * FROM orders WITH NO DATA; -- shape of the table definition, defaults included CREATE TABLE staging_orders (LIKE orders INCLUDING DEFAULTS); ``` ## The LIKE table element `LIKE source_table` appears *inside* the parenthesised table-element list, in the same position a column definition would occupy. That is why it is written `CREATE TABLE new (LIKE old)` and not `CREATE TABLE new LIKE old` in standard-shaped SQL. It expands, at creation time, into copies of the source table's column definitions: names, data types, and not-null declarations. It is a one-time expansion, not a link — later changes to the source do not propagate. Because it sits among the table elements, you can mix it with your own: ```sql CREATE TABLE staging_orders ( LIKE orders INCLUDING DEFAULTS, loaded_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, batch_id BIGINT NOT NULL ); ``` That is the idiomatic way to build a staging table: everything the target has, plus the load bookkeeping the target does not want. ## The INCLUDING options By default `LIKE` is conservative — columns, types and nullability only. PostgreSQL's options let you opt in to more: - `INCLUDING DEFAULTS` — copy the `DEFAULT` clauses - `INCLUDING CONSTRAINTS` — copy `CHECK` constraints - `INCLUDING INDEXES` — copy indexes and the unique/primary-key constraints backing them - `INCLUDING ALL` — everything the engine supports, with `EXCLUDING <option>` to carve pieces back out A staging table usually wants `INCLUDING DEFAULTS` and deliberately *not* `INCLUDING INDEXES`, because indexes slow the load down and can be added after it. A table that must behave like the original for testing purposes wants `INCLUDING ALL`. Note what `LIKE` does not do even at `INCLUDING ALL`: it does not create foreign keys pointing out to other tables, and it obviously does not make other tables point at the new one. Referential structure has to be declared explicitly. ## No rows, ever Neither `LIKE` form copies data. That is a feature — the two concerns are separated cleanly. If you want the structure *and* the rows, create with `LIKE` and then load: ```sql CREATE TABLE orders_archive (LIKE orders INCLUDING ALL); INSERT INTO orders_archive SELECT * FROM orders WHERE created_at < DATE '2025-01-01'; ``` That two-statement form is strictly better than a single CTAS whenever the destination will be queried or written to seriously, because the destination ends up with the same keys, defaults and constraints as the source rather than a bare pile of columns. ## Dialect spellings This is one of the places where engines genuinely diverge, so check yours: - **PostgreSQL** uses the parenthesised table element with the `INCLUDING`/`EXCLUDING` options described above. - **MySQL** spells it `CREATE TABLE new_table LIKE orders;` — no parentheses, and it copies column attributes and indexes as a unit rather than offering per-part options. - **Microsoft SQL Server** has no `LIKE` clause; the traditional structure-only idiom is `SELECT * INTO new_table FROM orders WHERE 1 = 0`, which produces columns and types but no keys, indexes or defaults. - Engines without any of these need the definition written out, which is another argument for keeping the real DDL in version control. ## Choosing between them Ask what you want to preserve. If the answer is "the shape of this query's output", CTAS is correct and `WITH NO DATA` or `WHERE 1 = 0` gets you the empty version. If the answer is "another table that behaves like this one", `LIKE` with the appropriate `INCLUDING` options is correct, and it is the only one of the two that can bring defaults and constraints along.