INSERT: Single Rows, Bulk & INSERT..SELECT

# INSERT: Single Rows, Bulk Rows & INSERT..SELECT ```sql -- One row (seeds, tests, fixtures) INSERT INTO dim_customers (customer_id, name, tier) VALUES (1, 'Acme', 'pro'); -- Bulk rows (small dimensions) INSERT INTO dim_status (status_id, label) VALUES (1, 'new'), (2, 'paid'), (3, 'refunded'); -- The pipeline workhorse: load a whole table from a query INSERT INTO mart_daily_revenue (day, revenue_cents) SELECT date_trunc('day', ordered_at), SUM(amount_cents) FROM stg_orders GROUP BY 1; ``` ## Rules 1. **Always name columns.** `INSERT INTO t VALUES (...)` breaks the day someone adds a column. Name them; order then doesn't matter. 2. **`INSERT..SELECT` is how loads move data** — staging to mart, mart to snapshot. Pair it with a watermark (`WHERE ordered_at > :last_seen`) for incremental loads. 3. **`INSERT..SELECT` + `ON CONFLICT DO NOTHING`** makes reloads idempotent: re-running yesterday's partition changes nothing. 4. **Match types explicitly** (`::timestamptz`, `::numeric`) — implicit casts across engines are a top source of silent truncation.