PostgreSQL: 综合项目：电商数据库系统从零搭建

CREATE TABLE users (
    id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email         TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    role          TEXT NOT NULL DEFAULT 'customer'
                    CHECK (role IN ('customer','vendor','admin')),
    tenant_id     BIGINT DEFAULT 1,
    created_at    TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE user_addresses (
    id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id      BIGINT NOT NULL REFERENCES users(id),
    address_line TEXT NOT NULL,
    city         TEXT NOT NULL,
    country      TEXT NOT NULL,
    is_default   BOOLEAN DEFAULT false
);

-- Enable RLS on users table
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Tenant isolation: users can only see same tenant
CREATE POLICY tenant_isolation ON users
    USING (tenant_id = current_setting('app.tenant_id')::bigint);

-- Admin can see all
CREATE POLICY admin_all_access ON users
    USING (role = 'admin');

-- Set tenant context per session
SET app.tenant_id = '1';
SELECT * FROM users;

CREATE TABLE

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Register with bcrypt hash
INSERT INTO users (email, password_hash, role, tenant_id)
VALUES (
    'alice@example.com',
    crypt('SecurePass123', gen_salt('bf')),
    'customer',
    1
);

-- Verify login
SELECT id, role FROM users
WHERE email = 'alice@example.com'
  AND password_hash = crypt('SecurePass123', password_hash);

INSERT 0 1

CREATE TABLE categories (
    id          SERIAL PRIMARY KEY,
    name        TEXT NOT NULL,
    name_ar     TEXT,
    parent_id   INT REFERENCES categories(id),
    sort_order  INT DEFAULT 0
);

INSERT INTO categories (name, name_ar, parent_id, sort_order) VALUES
    ('Electronics', 'إلكترونيات', NULL, 1),
    ('Phones', 'هواتف', 1, 1),
    ('Laptops', 'حاسبات', 1, 2),
    ('Clothing', 'ملابس', NULL, 2);

-- Recursive query: category tree
WITH RECURSIVE cat_tree AS (
    SELECT id, name, name_ar, parent_id, 0 AS level
    FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.name_ar, c.parent_id, ct.level + 1
    FROM categories c JOIN cat_tree ct ON c.parent_id = ct.id
)
SELECT repeat('  ', level) || name AS tree, name_ar
FROM cat_tree ORDER BY level, sort_order;

INSERT 0 1

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE products (
    id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name          TEXT NOT NULL,
    name_ar       TEXT,
    category_id   INT NOT NULL REFERENCES categories(id),
    price         NUMERIC(12,2) NOT NULL,
    attributes    JSONB DEFAULT '{}',
    search_vector TSVECTOR GENERATED ALWAYS AS (
        setweight(to_tsvector('simple', coalesce(name, '')), 'A') ||
        setweight(to_tsvector('simple', coalesce(name_ar, '')), 'B')
    ) STORED,
    embedding     vector(1536)
);

CREATE TABLE

-- Insert with dynamic attributes
INSERT INTO products (name, name_ar, category_id, price, attributes) VALUES
    ('iPhone 15 Pro', 'آيفون 15 برو', 2, 1199.00,
     '{"color": "titanium", "storage": "256GB", "5g": true}'::jsonb),
    ('MacBook Air M3', 'ماك بوك إير', 3, 1299.00,
     '{"color": "midnight", "ram": "16GB", "screen": "15 inch"}'::jsonb);

-- Find 5G phones under 1200 USD
SELECT name, price, attributes->>'storage' AS storage
FROM products
WHERE attributes @> '{"5g": true}'::jsonb
  AND price < 1200;

-- GIN index for JSONB containment queries
CREATE INDEX idx_products_attrs ON products USING GIN (attributes);

INSERT 0 1

-- GIN index for full-text search
CREATE INDEX idx_products_search ON products USING GIN (search_vector);

-- Search in English or Arabic
SELECT name, name_ar, ts_rank(search_vector, q) AS rank
FROM products, plainto_tsquery('simple', 'iphone') q
WHERE search_vector @@ q
ORDER BY rank DESC;

-- Arabic search
SELECT name, name_ar
FROM products, plainto_tsquery('simple', 'آيفون') q
WHERE search_vector @@ q;

CREATE TABLE

-- HNSW index for vector search
CREATE INDEX idx_products_embedding ON products
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

-- Find similar products
SELECT p2.name, p2.price,
       p2.embedding <=> p1.embedding AS distance
FROM products p1
CROSS JOIN LATERAL (
    SELECT * FROM products
    WHERE id != p1.id
    ORDER BY embedding <=> p1.embedding
    LIMIT 3
) p2
WHERE p1.name = 'iPhone 15 Pro';

CREATE TABLE

CREATE TABLE orders (
    id           BIGINT GENERATED ALWAYS AS IDENTITY,
    user_id      BIGINT NOT NULL REFERENCES users(id),
    order_date   DATE NOT NULL DEFAULT current_date,
    total_amount NUMERIC(12,2) DEFAULT 0,
    status       TEXT DEFAULT 'pending'
                  CHECK (status IN ('pending','paid','shipped','completed','cancelled')),
    created_at   TIMESTAMPTZ DEFAULT now(),
    PRIMARY KEY (id, order_date)
) PARTITION BY RANGE (order_date);

CREATE TABLE orders_2024_01 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE orders_2024_02 PARTITION OF orders
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
CREATE TABLE orders_2024_03 PARTITION OF orders
    FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

CREATE TABLE order_items (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id    BIGINT NOT NULL,
    order_date  DATE NOT NULL,
    product_id  BIGINT NOT NULL REFERENCES products(id),
    quantity    INT NOT NULL CHECK (quantity > 0),
    unit_price  NUMERIC(12,2) NOT NULL,
    FOREIGN KEY (order_id, order_date) REFERENCES orders(id, order_date)
);

CREATE INDEX idx_orders_user ON orders (user_id, order_date);
CREATE INDEX idx_order_items_order ON order_items (order_id, order_date);

CREATE TABLE

-- Create order
WITH new_order AS (
    INSERT INTO orders (user_id, order_date, status)
    VALUES (1, '2024-01-15', 'pending')
    RETURNING id, order_date
)
INSERT INTO order_items (order_id, order_date, product_id, quantity, unit_price)
SELECT new_order.id, new_order.order_date, p.id, 2, p.price
FROM new_order, products p
WHERE p.name = 'iPhone 15 Pro';

-- Update order total
UPDATE orders o SET total_amount = (
    SELECT SUM(quantity * unit_price)
    FROM order_items oi
    WHERE oi.order_id = o.id AND oi.order_date = o.order_date
)
WHERE o.id = 1 AND o.order_date = '2024-01-15';

  result  
----------
   42.50
(1 row)

CREATE TABLE cart_items (
    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id    BIGINT NOT NULL REFERENCES users(id),
    product_id BIGINT NOT NULL REFERENCES products(id),
    quantity   INT NOT NULL DEFAULT 1 CHECK (quantity > 0),
    added_at   TIMESTAMPTZ DEFAULT now(),
    UNIQUE (user_id, product_id)
);

-- Add or update cart item (UPSERT)
INSERT INTO cart_items (user_id, product_id, quantity)
VALUES (1, 1, 1)
ON CONFLICT (user_id, product_id)
DO UPDATE SET quantity = cart_items.quantity + EXCLUDED.quantity;

-- View cart with product details
SELECT p.name, p.price, ci.quantity,
       p.price * ci.quantity AS line_total
FROM cart_items ci
JOIN products p ON ci.product_id = p.id
WHERE ci.user_id = 1;

INSERT 0 1

CREATE TYPE payment_method AS ENUM ('credit_card', 'paypal', 'bank_transfer', 'cod');
CREATE TYPE payment_status AS ENUM ('pending', 'completed', 'failed', 'refunded');

CREATE TABLE payments (
    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id   BIGINT NOT NULL,
    order_date DATE NOT NULL,
    method     payment_method NOT NULL,
    amount     NUMERIC(12,2) NOT NULL,
    status     payment_status DEFAULT 'pending',
    paid_at    TIMESTAMPTZ,
    FOREIGN KEY (order_id, order_date) REFERENCES orders(id, order_date)
);

-- Record successful payment
INSERT INTO payments (order_id, order_date, method, amount, status, paid_at)
VALUES (1, '2024-01-15', 'credit_card', 2398.00, 'completed', now());

-- Update order status after payment
UPDATE orders SET status = 'paid'
WHERE id = 1 AND order_date = '2024-01-15';

INSERT 0 1

CREATE TABLE shipments (
    id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id      BIGINT NOT NULL,
    order_date    DATE NOT NULL,
    carrier       TEXT NOT NULL,
    tracking_code TEXT NOT NULL UNIQUE,
    status        TEXT DEFAULT 'created'
                   CHECK (status IN ('created','in_transit','delivered','failed')),
    FOREIGN KEY (order_id, order_date) REFERENCES orders(id, order_date)
);

CREATE TABLE shipment_events (
    id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    shipment_id  BIGINT NOT NULL REFERENCES shipments(id),
    event_type   TEXT NOT NULL,
    metadata     JSONB DEFAULT '{}',
    event_time   TIMESTAMPTZ DEFAULT now()
);

-- Track shipment with events
INSERT INTO shipments (order_id, order_date, carrier, tracking_code)
VALUES (1, '2024-01-15', 'DHL Express', 'DHL123456789');

INSERT INTO shipment_events (shipment_id, event_type, metadata) VALUES
    (1, 'picked_up',     '{"location": "Dubai Warehouse"}'::jsonb),
    (1, 'in_transit',    '{"location": "Bahrain Hub", "eta": "2024-01-18"}'::jsonb),
    (1, 'out_for_delivery', '{"location": "Riyadh"}'::jsonb);

-- Shipment timeline
SELECT se.event_time, se.event_type, se.metadata->>'location' AS location
FROM shipment_events se
WHERE se.shipment_id = 1
ORDER BY se.event_time;

INSERT 0 1

CREATE TABLE reviews (
    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id    BIGINT NOT NULL REFERENCES users(id),
    product_id BIGINT NOT NULL REFERENCES products(id),
    rating     INT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    comment    TEXT,
    created_at TIMESTAMPTZ DEFAULT now(),
    UNIQUE (user_id, product_id)
);

INSERT INTO reviews (user_id, product_id, rating, comment) VALUES
    (1, 1, 5, 'Excellent phone, fast delivery'),
    (2, 1, 4, 'Good but expensive'),
    (3, 2, 5, 'Best laptop ever');

-- Product rating summary with window function
SELECT p.name,
       COUNT(r.id) AS review_count,
       AVG(r.rating)::numeric(3,2) AS avg_rating,
       COUNT(r.id) FILTER (WHERE r.rating = 5) AS five_star,
       COUNT(r.id) FILTER (WHERE r.rating = 4) AS four_star
FROM products p
LEFT JOIN reviews r ON r.product_id = p.id
GROUP BY p.id, p.name;

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

CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT order_date,
       COUNT(DISTINCT user_id) AS unique_buyers,
       COUNT(*) AS order_count,
       SUM(total_amount) AS daily_revenue,
       AVG(total_amount)::numeric(12,2) AS avg_order_value
FROM orders
WHERE status = 'completed'
GROUP BY order_date
ORDER BY order_date;

CREATE UNIQUE INDEX idx_mv_daily_sales_date ON mv_daily_sales (order_date);

-- Refresh daily (can be scheduled with pg_cron)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_sales;

-- Top revenue days
SELECT order_date, daily_revenue, order_count
FROM mv_daily_sales
ORDER BY daily_revenue DESC
LIMIT 10;

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

CREATE MATERIALIZED VIEW mv_category_sales AS
SELECT c.name AS category,
       p.name AS product_name,
       SUM(oi.quantity) AS total_sold,
       SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.id
JOIN categories c ON p.category_id = c.id
JOIN orders o ON oi.order_id = o.id AND oi.order_date = o.order_date
WHERE o.status = 'completed'
GROUP BY c.name, p.name
ORDER BY revenue DESC;

REFRESH MATERIALIZED VIEW mv_category_sales;

  result  
----------
   42.50
(1 row)

CREATE OR REPLACE PROCEDURE create_order(
    p_user_id    BIGINT,
    p_items      JSONB
)
LANGUAGE plpgsql AS 
DECLARE
    v_order_id   BIGINT;
    v_order_date DATE := current_date;
    v_item       JSONB;
BEGIN
    INSERT INTO orders (user_id, order_date, status)
    VALUES (p_user_id, v_order_date, 'pending')
    RETURNING id INTO v_order_id;

    FOR v_item IN SELECT * FROM jsonb_array_elements(p_items)
    LOOP
        INSERT INTO order_items (order_id, order_date, product_id, quantity, unit_price)
        VALUES (v_order_id, v_order_date,
                (v_item->>'product_id')::bigint,
                (v_item->>'quantity')::int,
                (SELECT price FROM products WHERE id = (v_item->>'product_id')::bigint));
    END LOOP;

    UPDATE orders SET total_amount = (
        SELECT SUM(quantity * unit_price) FROM order_items
        WHERE order_id = v_order_id AND order_date = v_order_date
    ) WHERE id = v_order_id AND order_date = v_order_date;

    COMMIT;
END;
;

-- Call procedure
CALL create_order(1, '[
    {"product_id": 1, "quantity": 1},
    {"product_id": 2, "quantity": 2}
]'::jsonb);

  result  
----------
   42.50
(1 row)

CREATE OR REPLACE FUNCTION maintain_order_partitions()
RETURNS VOID AS 
DECLARE
    v_next_month DATE;
    v_part_name  TEXT;
BEGIN
    v_next_month := date_trunc('month', current_date + interval '1 month')::date;
    v_part_name  := 'orders_' || to_char(v_next_month, 'YYYY_MM');

    IF NOT EXISTS (
        SELECT 1 FROM pg_class WHERE relname = v_part_name
    ) THEN
        EXECUTE format(
            'CREATE TABLE %I PARTITION OF orders
             FOR VALUES FROM (%L) TO (%L)',
            v_part_name,
            v_next_month,
            (v_next_month + interval '1 month')::date
        );
    END IF;

    -- Detach partitions older than 2 years
    FOR v_part_name IN
        SELECT relname FROM pg_class
        WHERE relname LIKE 'orders_20__%'
          AND relkind = 'r'
          AND relname < 'orders_' || to_char(current_date - interval '2 years', 'YYYY_MM')
    LOOP
        EXECUTE format('ALTER TABLE orders DETACH PARTITION %I', v_part_name);
    END LOOP;
END;
 LANGUAGE plpgsql;

CREATE TABLE

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE SERVER legacy_pg FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host '10.0.1.50', port '5432', dbname 'legacy_shop');

CREATE USER MAPPING FOR current_user SERVER legacy_pg
    OPTIONS (user 'migrate_user', password 'secure_pass');

IMPORT FOREIGN SCHEMA public LIMIT TO (old_users, old_products)
    FROM SERVER legacy_pg INTO legacy;

-- Migrate users with password re-hash
INSERT INTO users (email, password_hash, role, tenant_id, created_at)
SELECT email, crypt(raw_password, gen_salt('bf')), 'customer', 1, created_at
FROM legacy.old_users
ON CONFLICT (email) DO NOTHING;

INSERT 0 1

# Base backup
pg_basebackup -D /backup/base -Ft -z -P

# Archive WAL (postgresql.conf)
wal_level = replica
archive_mode = on
archive_command = 'cp %p /backup/wal/%f'

# Restore to point in time
pg_restore --target-time='2024-03-15 14:30:00' -d shop_db /backup/base

# 命令执行成功

-- Complete e-commerce database initialization script
-- Module 1: User system
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    role TEXT NOT NULL DEFAULT 'customer'
         CHECK (role IN ('customer','vendor','admin')),
    tenant_id BIGINT DEFAULT 1,
    created_at TIMESTAMPTZ DEFAULT now()
);
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON users
    USING (tenant_id = current_setting('app.tenant_id')::bigint);

CREATE TABLE user_addresses (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    address_line TEXT NOT NULL, city TEXT NOT NULL, country TEXT NOT NULL,
    is_default BOOLEAN DEFAULT false
);

-- Module 2: Categories & Products
CREATE TABLE categories (
    id SERIAL PRIMARY KEY, name TEXT NOT NULL,
    name_ar TEXT, parent_id INT REFERENCES categories(id),
    sort_order INT DEFAULT 0
);
CREATE TABLE products (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL, name_ar TEXT,
    category_id INT NOT NULL REFERENCES categories(id),
    price NUMERIC(12,2) NOT NULL,
    attributes JSONB DEFAULT '{}',
    search_vector TSVECTOR GENERATED ALWAYS AS (
        setweight(to_tsvector('simple', coalesce(name,'')), 'A') ||
        setweight(to_tsvector('simple', coalesce(name_ar,'')), 'B')
    ) STORED,
    embedding vector(1536)
);
CREATE INDEX idx_products_attrs ON products USING GIN (attributes);
CREATE INDEX idx_products_search ON products USING GIN (search_vector);
CREATE INDEX idx_products_embedding ON products
    USING hnsw (embedding vector_cosine_ops) WITH (m=16, ef_construction=64);

-- Module 3: Orders (monthly partitioned)
CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    order_date DATE NOT NULL DEFAULT current_date,
    total_amount NUMERIC(12,2) DEFAULT 0,
    status TEXT DEFAULT 'pending'
           CHECK (status IN ('pending','paid','shipped','completed','cancelled')),
    created_at TIMESTAMPTZ DEFAULT now(),
    PRIMARY KEY (id, order_date)
) PARTITION BY RANGE (order_date);
CREATE TABLE orders_2024_q1 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

CREATE TABLE order_items (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id BIGINT NOT NULL, order_date DATE NOT NULL,
    product_id BIGINT NOT NULL REFERENCES products(id),
    quantity INT NOT NULL CHECK (quantity > 0),
    unit_price NUMERIC(12,2) NOT NULL,
    FOREIGN KEY (order_id, order_date) REFERENCES orders(id, order_date)
);

-- Module 4: Cart
CREATE TABLE cart_items (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    product_id BIGINT NOT NULL REFERENCES products(id),
    quantity INT NOT NULL DEFAULT 1, added_at TIMESTAMPTZ DEFAULT now(),
    UNIQUE (user_id, product_id)
);

-- Module 5: Payments
CREATE TYPE payment_method AS ENUM ('credit_card','paypal','bank_transfer','cod');
CREATE TYPE payment_status AS ENUM ('pending','completed','failed','refunded');
CREATE TABLE payments (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id BIGINT NOT NULL, order_date DATE NOT NULL,
    method payment_method NOT NULL, amount NUMERIC(12,2) NOT NULL,
    status payment_status DEFAULT 'pending', paid_at TIMESTAMPTZ,
    FOREIGN KEY (order_id, order_date) REFERENCES orders(id, order_date)
);

-- Module 6: Shipping
CREATE TABLE shipments (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id BIGINT NOT NULL, order_date DATE NOT NULL,
    carrier TEXT NOT NULL, tracking_code TEXT NOT NULL UNIQUE,
    status TEXT DEFAULT 'created'
           CHECK (status IN ('created','in_transit','delivered','failed')),
    FOREIGN KEY (order_id, order_date) REFERENCES orders(id, order_date)
);
CREATE TABLE shipment_events (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    shipment_id BIGINT NOT NULL REFERENCES shipments(id),
    event_type TEXT NOT NULL, metadata JSONB DEFAULT '{}',
    event_time TIMESTAMPTZ DEFAULT now()
);

-- Module 7: Reviews
CREATE TABLE reviews (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    product_id BIGINT NOT NULL REFERENCES products(id),
    rating INT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    comment TEXT, created_at TIMESTAMPTZ DEFAULT now(),
    UNIQUE (user_id, product_id)
);

-- Module 8: Reporting materialized views
CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT order_date, COUNT(DISTINCT user_id) AS unique_buyers,
       COUNT(*) AS order_count, SUM(total_amount) AS daily_revenue
FROM orders WHERE status = 'completed'
GROUP BY order_date;
CREATE UNIQUE INDEX idx_mv_daily ON mv_daily_sales (order_date);