PostgreSQL: PostgreSQL入门指南：什么是PostgreSQL及为何选择它

-- PostgreSQL: one database for multiple use cases

-- 1. Traditional relational query
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.name;

-- 2. JSONB flexible storage (no need for MongoDB)
INSERT INTO products (name, attributes)
VALUES ('Running Shoes', '{"color": "red", "size": 42, "tags": ["sport", "outdoor"]}'::jsonb);

-- 3. Full-text search (no need for Elasticsearch)
SELECT name FROM products
WHERE to_tsvector('english', name) @@ to_tsquery('english', 'running & shoes');

-- PostgreSQL: INSERT ON CONFLICT (more flexible)
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name, updated_at = NOW()
RETURNING id, name, updated_at;
-- RETURNING clause returns the affected row (MySQL has no equivalent)

INSERT 0 1

-- MySQL: ON DUPLICATE KEY UPDATE (less flexible)
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON DUPLICATE KEY UPDATE name = VALUES(name), updated_at = NOW();
-- No RETURNING clause, must run a separate SELECT to get the result

-- PostgreSQL: JSONB with GIN index (fast indexed queries)
SELECT name FROM products
WHERE attributes @> '{"color": "red"}'::jsonb;
-- @> is the "contains" operator, uses GIN index

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- MySQL: JSON function (no index support for this pattern)
SELECT name FROM products
WHERE JSON_CONTAINS(attributes, '"red"', '$.color');
-- No index, full table scan

-- PostgreSQL: built-in full-text search with ranking
SELECT name, ts_rank(to_tsvector('english', name || ' ' || description),
                     to_tsquery('english', 'red & shoes')) AS rank
FROM products
WHERE to_tsvector('english', name || ' ' || description)
      @@ to_tsquery('english', 'red & shoes')
ORDER BY rank DESC;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- MySQL: basic FULLTEXT search (no ranking flexibility)
SELECT name FROM products
WHERE MATCH(name, description) AGAINST('red shoes' IN BOOLEAN MODE);

-- Check available extensions
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE name IN ('uuid-ossp', 'pg_trgm', 'pgcrypto');

-- Install an extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Use the extension to generate UUIDs
SELECT uuid_generate_v4();
-- Output: a unique UUID like 550e8400-e29b-41d4-a716-446655440000

CREATE TABLE

-- PostgreSQL: FILTER clause for conditional aggregation (SQL standard)
SELECT
    store_id,
    COUNT(*) AS total_orders,
    COUNT(*) FILTER (WHERE status = 'completed') AS completed_orders,
    COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled_orders
FROM orders
GROUP BY store_id;

 count 
-------
     5
(1 row)

-- MySQL: must use SUM(IF()) or SUM(CASE) workaround
SELECT
    store_id,
    COUNT(*) AS total_orders,
    SUM(IF(status = 'completed', 1, 0)) AS completed_orders,
    SUM(IF(status = 'cancelled', 1, 0)) AS cancelled_orders
FROM orders
GROUP BY store_id;

-- Different products have different attributes
-- No need for separate tables or columns for each attribute type

INSERT INTO products (name, price, attributes) VALUES
    ('Running Shoes', 89.99, '{"color": "red", "size": 42, "weight_grams": 280}'::jsonb),
    ('Laptop', 1299.00, '{"cpu": "M3", "ram_gb": 16, "screen_inch": 14}'::jsonb),
    ('Coffee Beans', 24.50, '{"origin": "Colombia", "roast": "medium", "weight_kg": 1}'::jsonb);

-- Query: find all red products under $100
SELECT name, price, attributes
FROM products
WHERE price < 100
  AND attributes @> '{"color": "red"}'::jsonb;

     name      | price  |                 attributes
---------------+--------+--------------------------------------------
 Running Shoes |  89.99 | {"color": "red", "size": 42, "weight_grams": 280}

-- ============================================
-- Comprehensive example: PostgreSQL feature demo
-- Shows why one PG database can replace multiple tools
-- ============================================

-- 1. ACID transaction (no need for application-level consistency)
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
COMMIT;

-- 2. JSONB storage (no need for MongoDB)
INSERT INTO products (name, attributes)
VALUES ('Smart Watch', '{"color": "black", "battery_life_hours": 48, "waterproof": true}'::jsonb);

-- 3. Full-text search (no need for Elasticsearch)
SELECT name, ts_rank(
    to_tsvector('english', name),
    plainto_tsquery('english', 'smart watch')
) AS relevance
FROM products
WHERE to_tsvector('english', name) @@ plainto_tsquery('english', 'smart watch')
ORDER BY relevance DESC;

-- 4. Window function (no need for application-level ranking)
SELECT name, price,
    RANK() OVER (ORDER BY price DESC) AS price_rank
FROM products;

-- 5. UPSERT (no need for application-level conflict handling)
INSERT INTO products (name, price, attributes)
VALUES ('Smart Watch', 199.99, '{"color": "black", "battery_life_hours": 48}'::jsonb)
ON CONFLICT (name)
DO UPDATE SET price = EXCLUDED.price,
              attributes = EXCLUDED.attributes
RETURNING id, name, price;

 -- UPSERT RETURNING output:
 id |    name     | price
----+-------------+--------
  1 | Smart Watch | 199.99