-- 28_hstore_misc_attrs.sql
-- Banking Pattern Pack — PostgreSQL
-- Purpose: hstore for legacy misc attributes.
-- 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;

-- hstore for legacy attributes
CREATE EXTENSION IF NOT EXISTS hstore;
CREATE TABLE IF NOT EXISTS bank.legacy_attrs (
  account_id BIGINT PRIMARY KEY REFERENCES bank.accounts(account_id),
  attrs hstore
);
INSERT INTO bank.legacy_attrs (account_id, attrs) VALUES (1, 'channel=>mobile, segment=>retail')
ON CONFLICT (account_id) DO UPDATE SET attrs = EXCLUDED.attrs;
