INSERT: Single Rows, Bulk & INSERT..SELECT

-- 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.