How does SELECT * behave in a view and in INSERT ... SELECT when the base table gains a column?

## Two places the star outlives the statement Most discussion of `SELECT *` is about a query you run once. The dangerous cases are the two where the star is *stored*: a view definition and a data-moving `INSERT ... SELECT`. In both, the star was written against a table shape that will not stay that way. ## Views: the star is frozen, so the view drifts ```sql CREATE VIEW active_orders AS SELECT * FROM orders WHERE status = 'ACTIVE'; ALTER TABLE orders ADD COLUMN priority integer; -- SELECT * FROM active_orders still returns the original columns ``` PostgreSQL and MySQL both document that the star in a view definition is expanded when the view is created, and the expanded list is what the view stores. The new `priority` column therefore does not appear in `active_orders`, and never will until the view is re-created (`CREATE OR REPLACE VIEW`, or drop and create). Engines are not uniform here — some re-resolve the definition when the view is recompiled, in which case the view's columns *can* change under you — so the safe statement is: never rely on it either way. Either behaviour is bad news: - If the view is frozen, it quietly diverges from the table and anyone reading the view believes they are seeing the whole row. - If the view re-expands, the view's own contract changes without anyone editing it, breaking positional consumers downstream. Dropping or renaming a base column is worse: depending on the engine the view is either rejected at definition-drop time or left invalid and fails when someone next queries it — typically in a report, at a bad hour. ## INSERT ... SELECT *: positional matching, no names ```sql -- brittle: nothing names a column on either side INSERT INTO orders_archive SELECT * FROM orders WHERE created_at < DATE '2024-01-01'; -- durable: both sides named INSERT INTO orders_archive (id, customer_id, total, created_at) SELECT id, customer_id, total, created_at FROM orders WHERE created_at < DATE '2024-01-01'; ``` With no target column list, the source's columns are matched to the target's columns by position, and the degrees must match. Two things can then happen when the schema moves: **The loud failure.** The target gains a column and now has more columns than the source supplies, or the source gains one and supplies too many. The statement errors on the count. Annoying, but safe — you find out immediately. **The quiet failure.** The counts still match but the *order* differs — someone added a column to one table and a different column to the other, or rebuilt a table with columns in a new order. If the types are compatible, values are written into the wrong columns and no error is raised. Two `varchar` columns swapping content, or two integer identifier columns swapping, is a data-corruption bug that surfaces days later and cannot be distinguished from application logic at first glance. That asymmetry — sometimes it errors, sometimes it corrupts — is the reason interviewers treat the target column list as non-optional rather than as a style preference. ## The same shape in other stored places Anything that persists a star has the problem: a materialised query definition, a `CREATE TABLE ... AS SELECT *` snapshot whose shape is now historical, an ETL step copying between environments where the two schemas drifted independently. The rule generalises: if the SQL text will outlive the current schema, name the columns. ## Writing it defensively - **Views**: enumerate columns in the view body, and give the view an explicit column list where the engine supports it, so the view's contract is written down in one place. Re-create the view deliberately when it should expose a new column — that makes the exposure a reviewed decision instead of an accident. - **INSERT**: always supply the target column list, and always project named columns in the `SELECT`. Both halves matter: a target list with `SELECT *` still fails when the source changes. - **Schema changes**: adding a column at the end feels safe; it is safe only for statements that name their columns. ## Interview framing The good answer distinguishes the two mechanisms rather than lumping them together as "the star is fragile". A view freezes (or re-expands) at definition time; an insert matches positionally at execution time. Naming both, and naming the silent-corruption case as the one that actually costs money, is what separates a memorised rule from understanding.