Data Types & Schema Design

-- timestamptz stores as UTC, displays in session timezone
SET timezone = 'America/New_York';
SELECT now();  -- shows Eastern time

SET timezone = 'Asia/Kolkata';
SELECT now();  -- same instant, displayed as IST

-- Date arithmetic with intervals
SELECT now() + interval '30 days' AS expires_at;
SELECT age(now(), created_at) FROM users;  -- human-readable duration

-- Create table with jsonb
CREATE TABLE events (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  payload jsonb NOT NULL,
  created_at timestamptz DEFAULT now()
);

-- GIN index for containment queries
CREATE INDEX idx_events_payload ON events USING GIN (payload);

-- Query: find events where payload contains {"type": "purchase"}
SELECT * FROM events WHERE payload @> '{"type": "purchase"}';

-- Extract nested value
SELECT payload -> 'user' ->> 'email' AS email FROM events;

-- jsonb_path_query (SQL/JSON path — PG 12+)
SELECT * FROM events
WHERE jsonb_path_exists(payload, '$.items[*] ? (@.price > 100)');

-- Table with array column
CREATE TABLE articles (
  id serial PRIMARY KEY,
  title text NOT NULL,
  tags text[] DEFAULT '{}'
);

-- Insert with array
INSERT INTO articles (title, tags)
VALUES ('MVCC Explained', ARRAY['postgresql', 'internals', 'mvcc']);

-- Query: articles with 'postgresql' tag
SELECT * FROM articles WHERE 'postgresql' = ANY(tags);

-- Query: articles with ALL of these tags
SELECT * FROM articles WHERE tags @> ARRAY['postgresql', 'internals'];

-- GIN index for fast array queries
CREATE INDEX idx_articles_tags ON articles USING GIN (tags);

-- Prevent overlapping room bookings
CREATE TABLE bookings (
  id serial PRIMARY KEY,
  room_id integer NOT NULL,
  during tstzrange NOT NULL,
  EXCLUDE USING GIST (room_id WITH =, during WITH &&)
);

-- This succeeds
INSERT INTO bookings (room_id, during)
VALUES (1, tstzrange('2024-03-01 10:00', '2024-03-01 12:00'));

-- This fails — overlaps with existing booking
INSERT INTO bookings (room_id, during)
VALUES (1, tstzrange('2024-03-01 11:00', '2024-03-01 13:00'));
-- ERROR: conflicting key value violates exclusion constraint

-- Built-in UUID generation (PG 13+)
CREATE TABLE users (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  email text UNIQUE NOT NULL
);

-- For UUIDv7 (time-ordered), use pg_uuidv7 extension
-- or generate in application layer

-- Create enum type
CREATE TYPE order_status AS ENUM (
  'pending', 'confirmed', 'shipped', 'delivered', 'cancelled'
);

-- Use in table
CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status order_status NOT NULL DEFAULT 'pending'
);

-- Enums are sorted by definition order, stored as 4 bytes
-- Adding values: only append (cannot reorder or remove)
ALTER TYPE order_status ADD VALUE 'refunded' AFTER 'cancelled';

-- Email domain with validation
CREATE DOMAIN email_address AS text
  CHECK (value ~ '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$');

-- Use like any type
CREATE TABLE contacts (
  id serial PRIMARY KEY,
  email email_address NOT NULL
);

-- Positive money domain
CREATE DOMAIN positive_amount AS numeric(12, 2)
  CHECK (value > 0);

CREATE TABLE order_items (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id bigint NOT NULL
    REFERENCES orders(id) ON DELETE CASCADE,
  product_id bigint NOT NULL
    REFERENCES products(id) ON DELETE RESTRICT,
  quantity integer NOT NULL CHECK (quantity > 0)
);

-- ON DELETE actions:
-- RESTRICT  — block delete if children exist (safest)
-- CASCADE   — delete children too (use for owned entities)
-- SET NULL  — set FK to NULL (use for optional relationships)
-- NO ACTION — like RESTRICT but deferrable

-- CRITICAL: always index FK columns on the child table
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

-- Parent table (no data stored here directly)
CREATE TABLE events (
  id bigint GENERATED ALWAYS AS IDENTITY,
  event_type text NOT NULL,
  payload jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);

-- Create monthly partitions
CREATE TABLE events_2024_01 PARTITION OF events
  FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE events_2024_02 PARTITION OF events
  FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- Queries automatically prune irrelevant partitions
SELECT * FROM events
WHERE created_at >= '2024-01-15' AND created_at < '2024-01-20';
-- Only scans events_2024_01