Examples

-- 1) UPSERT via ON CONFLICT
CREATE TABLE customers (
  id    bigserial PRIMARY KEY,
  email text NOT NULL UNIQUE,
  name  text NOT NULL,
  updated_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO customers (email, name) VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO UPDATE
  SET name = EXCLUDED.name,
      updated_at = now()
RETURNING id, name;

-- 2) Window queries — top 3 orders per customer
SELECT *
FROM (
  SELECT id, customer_id, total_cents,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total_cents DESC) AS rk
  FROM orders
) ranked
WHERE rk <= 3;

-- Running total per customer
SELECT id, customer_id, created_at, total_cents,
       SUM(total_cents) OVER (PARTITION BY customer_id ORDER BY created_at) AS running
FROM orders;

-- 7-day moving average of revenue
SELECT day, revenue,
       AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7
FROM daily_revenue;

-- 3) JSONB column queries + GIN index
CREATE TABLE events (
  id         bigserial PRIMARY KEY,
  type       text NOT NULL,
  payload    jsonb NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX ix_events_payload_gin ON events USING gin (payload jsonb_path_ops);

INSERT INTO events (type, payload) VALUES
  ('order.paid',    '{"order_id": "o1", "amount": 4995}'),
  ('order.shipped', '{"order_id": "o1", "tracking": "AU-9F3"}');

-- Lookup by path
SELECT id, type, payload->>'order_id' AS oid
FROM events
WHERE payload @> '{"order_id":"o1"}';

-- Modify JSONB
UPDATE events SET payload = payload || '{"version": 2}'
WHERE id = 1;

UPDATE events SET payload = jsonb_set(payload, '{amount}', '6000')
WHERE type = 'order.paid';

-- 4) Materialised view — cached aggregate
CREATE MATERIALIZED VIEW customer_lifetime_value AS
SELECT customer_id,
       SUM(total_cents) AS total_cents,
       COUNT(*) AS order_count,
       MAX(created_at) AS last_order_at
FROM orders
WHERE status IN ('paid', 'shipped')
GROUP BY customer_id;

CREATE UNIQUE INDEX ix_clv_customer ON customer_lifetime_value (customer_id);

-- Refresh (CONCURRENTLY needs a unique index)
REFRESH MATERIALIZED VIEW CONCURRENTLY customer_lifetime_value;

-- 5) Bulk load with COPY (much faster than INSERT-per-row)
COPY orders (customer_id, total_cents, status, created_at)
FROM '/tmp/orders.csv' WITH (FORMAT csv, HEADER true);

-- Or from stdin via psql:
-- \copy orders FROM 'orders.csv' WITH CSV HEADER

-- ===== Patterns to internalise =====
-- - ON CONFLICT for upserts; never SELECT-then-INSERT for the same purpose
-- - Window functions replace many self-joins
-- - GIN index on jsonb_path_ops for @> queries; smaller than the default GIN
-- - Materialised view + CONCURRENTLY refresh for cached aggregates
-- - COPY for bulk; orders of magnitude faster than per-row INSERT

-- ===== Pitfalls =====
-- - ON CONFLICT without a constraint to target -> error
-- - REFRESH MATERIALIZED VIEW (without CONCURRENTLY) locks reads
-- - Forgetting to ANALYZE after bulk load -> bad plans for a while
-- - JSONB queries without an index -> sequential scans
-- - Window function results ordered randomly when ORDER BY is missing

-- Common everyday Postgres recipes — see the lesson body.