-- 19_partitioning_list_region.sql
-- Banking Pattern Pack — PostgreSQL
-- Purpose: List partitioning by branch region.
-- Context: Retail bank example with customers, accounts, transactions, cards, loans.
-- Date: 2025-08-18
-- Notes:
--   * Safe to read; run on a development DB.
--   * Requires: CREATE privileges; some files may need superuser for extensions.
--   * Search path set to 'bank' schema for clarity.
SET search_path = bank, public;

-- List partitioning by region for branches
CREATE TABLE IF NOT EXISTS bank.branch_logs (
  id BIGINT GENERATED ALWAYS AS IDENTITY,
  region TEXT NOT NULL,
  message TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
) PARTITION BY LIST (region);

CREATE TABLE IF NOT EXISTS bank.branch_logs_west PARTITION OF bank.branch_logs FOR VALUES IN ('west');
CREATE TABLE IF NOT EXISTS bank.branch_logs_north PARTITION OF bank.branch_logs FOR VALUES IN ('north');
