那些我很希望 MySQL 有，但 Postgres 已经有的功能

-- PostgreSQL: This works!
BEGIN;
ALTER TABLE users ADD COLUMN email_verified BOOLEAN DEFAULT FALSE;
SAVEPOINT after_column_add;
CREATE INDEX idx_users_email_verified ON users(email_verified);
-- Oops, index creation failed, but we can keep the column
ROLLBACK TO SAVEPOINT after_column_add;
COMMIT;

-- PostgreSQL: Create custom types
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE DOMAIN email AS TEXT CHECK (VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT,
    email email,  -- Custom domain with validation
    current_mood mood  -- Custom enum type
);

-- PostgreSQL: Arrays are first-class citizens
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    tags TEXT[],  -- Array of tags
    view_counts INTEGER[]  -- Array of daily view counts
);
-- Query with array operations
SELECT * FROM articles
WHERE 'postgresql' = ANY(tags)
AND array_length(view_counts, 1) > 7;

-- PostgreSQL: Recursive CTEs for hierarchical data
WITH RECURSIVE employee_hierarchy AS (
    -- Base case: top-level managers
    SELECT id, name, manager_id, 1 as level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    -- Recursive case: subordinates
    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy ORDER BY level, name;

-- PostgreSQL: Enable RLS and create policies
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
-- Policy for user-specific data
CREATE POLICY user_documents ON documents
    FOR ALL TO app_user
    USING (owner_id = current_setting('app.current_user_id')::INTEGER);
-- Policy for multi-tenant applications
CREATE POLICY tenant_isolation ON documents
    FOR ALL TO app_user
    USING (tenant_id = current_setting('app.tenant_id')::INTEGER);
-- Users can only see their own documents automatically
SELECT * FROM documents;  -- Only returns user's documents in their tenant

-- PostgreSQL: Index only active users
CREATE INDEX idx_active_users ON users (last_login)
WHERE status = 'active';
-- Index only pending orders
CREATE INDEX idx_pending_orders ON orders (created_at)
WHERE status = 'pending';

-- PostgreSQL with PostGIS: Advanced spatial queries
SELECT name,
       ST_Distance(location, ST_MakePoint(-73.935242, 40.730610)) as distance_km
FROM restaurants
WHERE ST_DWithin(location, ST_MakePoint(-73.935242, 40.730610), 1000)  -- Within 1km
ORDER BY location <-> ST_MakePoint(-73.935242, 40.730610)  -- KNN search
LIMIT 10;

-- PostgreSQL with pgvector: Store and search embeddings
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    content TEXT,
    embedding VECTOR(1536)  -- OpenAI embedding dimension
);
-- Find similar documents using cosine similarity
SELECT content, 1 - (embedding <=> '[0.1, 0.2, ...]'::vector) AS similarity
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 5;