PostgreSQL Advanced Features

-- Numeric types
INTEGER              -- 4-byte integer
BIGINT               -- 8-byte integer  
SERIAL               -- Auto-incrementing integer
NUMERIC(10,2)        -- Exact decimal
REAL / FLOAT         -- Floating point

-- String types
VARCHAR(255)         -- Variable length
TEXT                 -- Unlimited length
CHAR(10)            -- Fixed length

-- Date/Time types
DATE                -- Date only
TIME                -- Time only
TIMESTAMP           -- Date and time
TIMESTAMPTZ         -- With timezone
INTERVAL            -- Time duration

-- Boolean
BOOLEAN             -- TRUE/FALSE/NULL

-- DETAILED_TAB:
-- Type casting examples
SELECT 
  '123'::INTEGER,                    -- Cast to integer
  '2024-01-01'::DATE,               -- Cast to date
  1::BOOLEAN,                        -- 1 becomes TRUE
  CAST('123.45' AS NUMERIC),        -- SQL standard casting
  '1 hour 30 minutes'::INTERVAL     -- Interval type

-- Create table with arrays
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT,
  tags TEXT[],           -- Array of text
  prices INTEGER[]       -- Array of integers
);

-- Insert arrays
INSERT INTO products (name, tags, prices) VALUES
  ('Laptop', ARRAY['electronics', 'computers'], ARRAY[999, 1299]),
  ('Book', '{"education", "programming"}', '{15, 20, 25}');

-- Query arrays
SELECT * FROM products 
WHERE 'electronics' = ANY(tags);     -- Contains value

SELECT * FROM products
WHERE tags @> ARRAY['computers'];    -- Contains all

-- DETAILED_TAB:
-- Array operations
SELECT 
  array_length(tags, 1),              -- Array length
  tags[1],                            -- First element (1-indexed!)
  array_append(tags, 'new'),          -- Add element
  array_remove(tags, 'old'),          -- Remove element
  unnest(tags)                        -- Expand to rows
FROM products;

-- Array aggregation
SELECT 
  category,
  array_agg(DISTINCT tag) AS all_tags
FROM product_tags
GROUP BY category;

-- Create table with JSONB
CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  data JSONB NOT NULL
);

-- Insert JSON data
INSERT INTO events (data) VALUES
  ('{"user": "john", "action": "login", "timestamp": "2024-01-01"}'),
  ('{"user": "jane", "action": "purchase", "items": ["book", "pen"]}');

-- Query JSON fields
SELECT data->>'user' AS username       -- Get as text
FROM events
WHERE data->>'action' = 'login';

SELECT data->'items'->0                -- Get first array item
FROM events
WHERE data ? 'items';                  -- Has key

-- DETAILED_TAB:
-- Advanced JSONB operations
-- Update JSON fields
UPDATE events
SET data = jsonb_set(data, '{status}', '"completed"')
WHERE id = 1;

-- Query nested JSON
SELECT * FROM events
WHERE data @> '{"user": "john"}';      -- Contains

-- JSON aggregation
SELECT jsonb_agg(data) AS all_events
FROM events
WHERE data->>'action' = 'purchase';

-- Create index on JSON field
CREATE INDEX idx_user ON events((data->>'user'));

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Create table with UUID
CREATE TABLE users (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  email CITEXT UNIQUE,              -- Case-insensitive text
  ip_address INET,                  -- IP address
  mac_address MACADDR,              -- MAC address
  price MONEY,                       -- Currency
  location POINT                     -- Geometric point
);

-- Insert with special types
INSERT INTO users VALUES
  (uuid_generate_v4(), 
   'John@Example.com',              -- CITEXT matches any case
   '192.168.1.1',
   '08:00:27:1e:4f:3a',
   '$19.99',
   point(40.7128, -74.0060));       -- NYC coordinates

-- DETAILED_TAB:
-- Range types
CREATE TABLE bookings (
  id SERIAL PRIMARY KEY,
  room_id INTEGER,
  during TSTZRANGE                  -- Timestamp range
);

-- Insert range
INSERT INTO bookings (room_id, during) VALUES
  (101, '[2024-01-01 14:00, 2024-01-01 16:00)');

-- Check overlap
SELECT * FROM bookings
WHERE during && '[2024-01-01 15:00, 2024-01-01 17:00)';

-- ROW_NUMBER - Sequential numbers
SELECT 
  name,
  salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) as rank
FROM employees;

-- RANK - Same rank for ties, gaps in sequence
SELECT
  name,
  score,
  RANK() OVER (ORDER BY score DESC) as rank
FROM scores;

-- DENSE_RANK - Same rank for ties, no gaps
SELECT
  name,
  score, 
  DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank
FROM scores;

-- DETAILED_TAB:
-- Ranking within groups (PARTITION BY)
SELECT
  department,
  name,
  salary,
  ROW_NUMBER() OVER (
    PARTITION BY department 
    ORDER BY salary DESC
  ) as dept_rank,
  RANK() OVER (
    ORDER BY salary DESC
  ) as company_rank
FROM employees;

-- NTILE - Divide into buckets
SELECT
  name,
  salary,
  NTILE(4) OVER (ORDER BY salary) as quartile
FROM employees;

-- Running total
SELECT
  date,
  amount,
  SUM(amount) OVER (ORDER BY date) as running_total
FROM sales;

-- Moving average (3-row window)
SELECT
  date,
  price,
  AVG(price) OVER (
    ORDER BY date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) as moving_avg_3
FROM stock_prices;

-- Cumulative percentage
SELECT
  product,
  revenue,
  SUM(revenue) OVER (ORDER BY revenue DESC) as cumulative,
  100.0 * SUM(revenue) OVER (ORDER BY revenue DESC) / 
    SUM(revenue) OVER () as cumulative_pct
FROM product_sales;

-- DETAILED_TAB:
-- Complex window frames
SELECT
  date,
  sales,
  -- Previous row
  LAG(sales, 1) OVER (ORDER BY date) as prev_sales,
  -- Next row
  LEAD(sales, 1) OVER (ORDER BY date) as next_sales,
  -- First value in window
  FIRST_VALUE(sales) OVER (
    PARTITION BY EXTRACT(YEAR FROM date)
    ORDER BY date
  ) as year_first,
  -- Difference from previous
  sales - LAG(sales, 1) OVER (ORDER BY date) as growth
FROM daily_sales;

-- Simple CTE
WITH high_value_customers AS (
  SELECT customer_id, SUM(amount) as total
  FROM orders
  GROUP BY customer_id
  HAVING SUM(amount) > 1000
)
SELECT c.name, hvc.total
FROM customers c
JOIN high_value_customers hvc ON c.id = hvc.customer_id;

-- Multiple CTEs
WITH 
  active_users AS (
    SELECT * FROM users WHERE status = 'active'
  ),
  recent_orders AS (
    SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '30 days'
  )
SELECT u.name, COUNT(o.id) as order_count
FROM active_users u
LEFT JOIN recent_orders o ON u.id = o.user_id
GROUP BY u.name;

-- DETAILED_TAB:
-- CTE with INSERT/UPDATE/DELETE
WITH deleted AS (
  DELETE FROM old_records
  WHERE created_at < NOW() - INTERVAL '1 year'
  RETURNING *
)
INSERT INTO archive_table
SELECT * FROM deleted;

-- Materialized CTE (PostgreSQL 12+)
WITH MATERIALIZED expensive_calculation AS (
  -- This runs once and results are stored
  SELECT complex_function(data) as result
  FROM large_table
)
SELECT * FROM expensive_calculation
WHERE result > 100;

-- Organizational hierarchy
WITH RECURSIVE org_chart AS (
  -- Anchor: start with CEO
  SELECT id, name, manager_id, 0 as level
  FROM employees
  WHERE manager_id IS NULL
  
  UNION ALL
  
  -- Recursive: find subordinates
  SELECT e.id, e.name, e.manager_id, oc.level + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart
ORDER BY level, name;

-- DETAILED_TAB:
-- Generate series with recursive CTE
WITH RECURSIVE dates AS (
  SELECT DATE '2024-01-01' as date
  UNION ALL
  SELECT date + INTERVAL '1 day'
  FROM dates
  WHERE date < '2024-01-31'
)
SELECT date FROM dates;

-- Find all related records (graph traversal)
WITH RECURSIVE related AS (
  -- Start node
  SELECT id, parent_id, name, ARRAY[id] as path
  FROM categories
  WHERE id = 5
  
  UNION ALL
  
  -- Find connections
  SELECT c.id, c.parent_id, c.name, r.path || c.id
  FROM categories c
  JOIN related r ON c.parent_id = r.id
  WHERE NOT c.id = ANY(r.path)  -- Avoid cycles
)
SELECT * FROM related;

-- B-tree index (default)
CREATE INDEX idx_email ON users(email);

-- Unique index
CREATE UNIQUE INDEX idx_username ON users(username);

-- Multicolumn index
CREATE INDEX idx_name ON users(last_name, first_name);

-- Partial index (filtered)
CREATE INDEX idx_active ON orders(user_id)
WHERE status = 'active';

-- Expression index
CREATE INDEX idx_lower_email ON users(LOWER(email));

-- DETAILED_TAB:
-- GIN index for full-text search
CREATE INDEX idx_search ON articles USING gin(to_tsvector('english', content));

-- GiST index for geometric data
CREATE INDEX idx_location ON stores USING gist(location);

-- BRIN index for large sorted tables
CREATE INDEX idx_created ON logs USING brin(created_at);

-- Hash index for equality checks only
CREATE INDEX idx_status ON orders USING hash(status);

-- Concurrent index creation (no table lock)
CREATE INDEX CONCURRENTLY idx_user_email ON users(email);

-- Explain query plan
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';

-- Explain with execution stats
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;

-- Verbose explain with buffers
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM large_table WHERE status = 'active';

-- Check index usage
SELECT 
  schemaname,
  tablename,
  indexname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan;

-- DETAILED_TAB:
-- Find missing indexes
SELECT
  schemaname,
  tablename,
  attname,
  n_distinct,
  correlation
FROM pg_stats
WHERE schemaname = 'public'
  AND n_distinct > 100
  AND correlation < 0.1
ORDER BY n_distinct DESC;

-- Query cache hit ratio
SELECT 
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as cache_hit_ratio
FROM pg_statio_user_tables;

-- Slow query log (pg_stat_statements)
SELECT 
  query,
  mean_exec_time,
  calls,
  total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Vacuum and analyze
VACUUM ANALYZE users;            -- Clean up and update stats
VACUUM FULL users;               -- Full cleanup (locks table)

-- Table partitioning
CREATE TABLE orders_2024 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

-- Materialized views
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT 
  DATE_TRUNC('month', created_at) as month,
  SUM(amount) as total
FROM orders
GROUP BY 1;

-- Refresh materialized view
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;

-- DETAILED_TAB:
-- Parallel queries (PostgreSQL 9.6+)
SET max_parallel_workers_per_gather = 4;
SET parallel_setup_cost = 100;

-- Force index usage
SET enable_seqscan = OFF;  -- Force index (testing only!)

-- Work memory for sorts
SET work_mem = '256MB';    -- For current session

-- Prepared statements
PREPARE get_user (INT) AS
  SELECT * FROM users WHERE id = $1;
EXECUTE get_user(123);

-- Connection pooling config
ALTER SYSTEM SET max_connections = 200;
ALTER SYSTEM SET shared_buffers = '4GB';
SELECT pg_reload_conf();    -- Apply changes

-- Create text search column
ALTER TABLE articles ADD COLUMN search_vector tsvector;

-- Update search vector
UPDATE articles
SET search_vector = to_tsvector('english', 
  title || ' ' || content);

-- Create GIN index
CREATE INDEX idx_search ON articles USING gin(search_vector);

-- Search with tsquery
SELECT title, ts_rank(search_vector, query) as rank
FROM articles,
  to_tsquery('english', 'postgresql & performance') query
WHERE search_vector @@ query
ORDER BY rank DESC;

-- DETAILED_TAB:
-- Auto-update search vector with trigger
CREATE OR REPLACE FUNCTION update_search_vector()
RETURNS trigger AS $$
BEGIN
  NEW.search_vector := to_tsvector('english',
    COALESCE(NEW.title, '') || ' ' || 
    COALESCE(NEW.content, ''));
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trig_update_search
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW
EXECUTE FUNCTION update_search_vector();

-- Advanced search queries
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english',
  'postgresql & (index | performance) & !slow');

-- Highlight search results
SELECT ts_headline('english', content, query)
FROM articles, to_tsquery('english', 'database') query
WHERE search_vector @@ query;

-- Basic transaction
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Rollback on error
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 123;
-- Error occurs here
ROLLBACK;

-- Savepoints
BEGIN;
UPDATE users SET credits = 100;
SAVEPOINT before_risky;
DELETE FROM logs;  -- Risky operation
ROLLBACK TO before_risky;  -- Undo just the DELETE
COMMIT;

-- DETAILED_TAB:
-- Isolation levels
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;    -- Default
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Advisory locks
SELECT pg_advisory_lock(12345);        -- Get lock
-- Do work
SELECT pg_advisory_unlock(12345);      -- Release

-- Row-level locking
SELECT * FROM orders
WHERE id = 123
FOR UPDATE;                            -- Lock for update

SELECT * FROM inventory
WHERE product_id = 456  
FOR SHARE;                             -- Shared lock