阿里云RDS PostgreSQL对接使用流程与SQL语法深度解析

-- 查看当前白名单配置（通过RDS控制台操作，无法直接通过SQL查询）
-- 控制台路径：实例详情 → 白名单与安全组

-- 通过SQL创建数据库（需使用高权限账号连接后执行）
CREATE DATABASE myappdb WITH ENCODING='UTF8' LC_COLLATE='en_US.UTF-8' LC_CTYPE='en_US.UTF-8' TEMPLATE=template0;

-- 创建用户
CREATE USER appuser WITH PASSWORD 'YourStrongPassword123!';

-- 授予数据库权限
GRANT ALL PRIVILEGES ON DATABASE myappdb TO appuser;

-- 授予schema权限（连接至目标数据库后执行）
\c myappdb
GRANT ALL ON SCHEMA public TO appuser;

# 内网连接地址示例（从控制台获取）
# 内网地址：pgm-xxxxxxx.pg.rds.aliyuncs.com
# 内网端口：5432

# 外网连接地址示例（开通外网后获取）
# 外网地址：pgm-xxxxxxx.pg.rds.aliyuncs.com
# 外网端口：5432

# 内网连接
psql -h pgm-xxxxxxx.pg.rds.aliyuncs.com -p 5432 -U appuser -d myappdb

# 外网连接
psql -h pgm-xxxxxxx.pg.rds.aliyuncs.com -p 5432 -U appuser -d myappdb

# 连接后输入密码即可进入交互式SQL环境

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class RDSConnection {
    public static void main(String[] args) {
        String url = "jdbc:postgresql://pgm-xxxxxxx.pg.rds.aliyuncs.com:5432/myappdb";
        String user = "appuser";
        String password = "YourStrongPassword123!";
        
        try (Connection conn = DriverManager.getConnection(url, user, password);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT version()")) {
            if (rs.next()) {
                System.out.println("PostgreSQL version: " + rs.getString(1));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

import psycopg2

conn = psycopg2.connect(
    host="pgm-xxxxxxx.pg.rds.aliyuncs.com",
    port=5432,
    database="myappdb",
    user="appuser",
    password="YourStrongPassword123!"
)

cur = conn.cursor()
cur.execute("SELECT version()")
version = cur.fetchone()
print(f"PostgreSQL version: {version[0]}")

cur.close()
conn.close()

-- 数值类型
CREATE TABLE numeric_types (
    id SERIAL PRIMARY KEY,
    small_val SMALLINT,
    int_val INTEGER,
    big_val BIGINT,
    decimal_val DECIMAL(10,2),
    float_val REAL,
    double_val DOUBLE PRECISION
);

-- 字符串类型
CREATE TABLE string_types (
    id SERIAL PRIMARY KEY,
    char_fixed CHAR(10),
    varchar_var VARCHAR(255),
    text_unlimited TEXT
);

-- 日期时间类型
CREATE TABLE datetime_types (
    id SERIAL PRIMARY KEY,
    date_val DATE,
    time_val TIME,
    timestamp_val TIMESTAMP,
    timestamptz_val TIMESTAMPTZ,
    interval_val INTERVAL
);

-- 布尔类型
CREATE TABLE boolean_types (
    id SERIAL PRIMARY KEY,
    is_active BOOLEAN DEFAULT TRUE
);

-- 数组类型
CREATE TABLE array_types (
    id SERIAL PRIMARY KEY,
    int_array INTEGER[],
    text_array TEXT[]
);

-- JSON/JSONB类型（重点）
CREATE TABLE json_types (
    id SERIAL PRIMARY KEY,
    json_data JSON,
    jsonb_data JSONB
);

-- 插入数据（INSERT）
INSERT INTO users (username, email, age, created_at) 
VALUES ('alice', 'alice@example.com', 28, NOW());

INSERT INTO users (username, email, age, created_at) 
VALUES 
    ('bob', 'bob@example.com', 32, NOW()),
    ('charlie', 'charlie@example.com', 24, NOW())
RETURNING id;

-- 查询数据（SELECT）
SELECT * FROM users;
SELECT id, username, email FROM users WHERE age > 25 ORDER BY age DESC;
SELECT COUNT(*) FROM users WHERE age BETWEEN 20 AND 30;

-- 更新数据（UPDATE）
UPDATE users SET email = 'alice.new@example.com' WHERE username = 'alice'
RETURNING *;

-- 删除数据（DELETE）
DELETE FROM users WHERE username = 'charlie' RETURNING id;

-- 创建示例销售数据表
CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    product VARCHAR(50),
    category VARCHAR(50),
    sale_date DATE,
    amount DECIMAL(10,2)
);

INSERT INTO sales (product, category, sale_date, amount) VALUES
('Laptop', 'Electronics', '2026-01-15', 1200.00),
('Mouse', 'Electronics', '2026-01-15', 25.00),
('Keyboard', 'Electronics', '2026-01-16', 45.00),
('Desk', 'Furniture', '2026-01-16', 350.00),
('Chair', 'Furniture', '2026-01-17', 200.00),
('Monitor', 'Electronics', '2026-01-17', 300.00);

-- ROW_NUMBER：为每行分配唯一序号
SELECT 
    product,
    category,
    amount,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY amount DESC) AS rank_in_category
FROM sales;

-- RANK与DENSE_RANK：排名函数
SELECT 
    product,
    category,
    amount,
    RANK() OVER (ORDER BY amount DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rank
FROM sales;

-- 累计聚合（运行总和）
SELECT 
    sale_date,
    product,
    amount,
    SUM(amount) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM sales
ORDER BY sale_date;

-- LAG与LEAD：访问前后行数据
SELECT 
    sale_date,
    product,
    amount,
    LAG(amount, 1) OVER (ORDER BY sale_date) AS previous_amount,
    LEAD(amount, 1) OVER (ORDER BY sale_date) AS next_amount
FROM sales;

-- 基础CTE
WITH high_value_products AS (
    SELECT product, SUM(amount) AS total_sales
    FROM sales
    GROUP BY product
    HAVING SUM(amount) > 100
)
SELECT * FROM high_value_products ORDER BY total_sales DESC;

-- 递归CTE：查询树形结构（如组织架构）
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    manager_id INTEGER
);

INSERT INTO employees (name, manager_id) VALUES
('CEO', NULL),
('VP Sales', 1),
('VP Engineering', 1),
('Sales Rep 1', 2),
('Sales Rep 2', 2),
('Engineer 1', 3);

WITH RECURSIVE org_tree AS (
    -- 基础查询：根节点
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 递归查询：子节点
    SELECT e.id, e.name, e.manager_id, ot.level + 1
    FROM employees e
    INNER JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree ORDER BY level, id;

-- 创建包含JSONB列的表
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER,
    order_data JSONB,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 插入JSONB数据
INSERT INTO orders (customer_id, order_data) VALUES
(1, '{"items": [{"name": "Laptop", "qty": 1, "price": 1200}, {"name": "Mouse", "qty": 2, "price": 25}], "total": 1250, "status": "shipped"}'),
(2, '{"items": [{"name": "Desk", "qty": 1, "price": 350}], "total": 350, "status": "pending"}');

-- JSONB查询：-> 返回JSON对象，->> 返回文本
SELECT 
    id,
    order_data->>'status' AS status,
    order_data->'items' AS items
FROM orders;

-- JSONB条件查询
SELECT * FROM orders WHERE order_data->>'status' = 'shipped';
SELECT * FROM orders WHERE order_data @> '{"status": "shipped"}';

-- JSONB包含查询
SELECT * FROM orders WHERE order_data @> '{"items": [{"name": "Laptop"}]}';

-- JSONB键值提取
SELECT 
    id,
    jsonb_array_length(order_data->'items') AS item_count,
    (order_data->>'total')::DECIMAL AS total_amount
FROM orders;

-- JSONB聚合
SELECT 
    order_data->>'status' AS status,
    COUNT(*) AS order_count,
    SUM((order_data->>'total')::DECIMAL) AS total_revenue
FROM orders
GROUP BY order_data->>'status';

-- 创建GIN索引加速JSONB查询
CREATE INDEX idx_orders_status ON orders USING gin (order_data);

-- 创建函数：计算订单总价
CREATE OR REPLACE FUNCTION calculate_order_total(order_id INTEGER)
RETURNS DECIMAL AS $$
DECLARE
    total DECIMAL := 0;
    item_record RECORD;
BEGIN
    FOR item_record IN 
        SELECT (item->>'price')::DECIMAL * (item->>'qty')::INTEGER AS item_total
        FROM orders,
        jsonb_array_elements(order_data->'items') AS item
        WHERE orders.id = order_id
    LOOP
        total := total + item_record.item_total;
    END LOOP;
    
    RETURN total;
END;
$$ LANGUAGE plpgsql;

-- 调用函数
SELECT calculate_order_total(1);

-- 创建存储过程（PostgreSQL 11+）
CREATE OR REPLACE PROCEDURE update_order_status(
    order_id INTEGER,
    new_status VARCHAR
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE orders 
    SET order_data = jsonb_set(order_data, '{status}', to_jsonb(new_status))
    WHERE id = order_id;
    
    COMMIT;
END;
$$;

-- 调用存储过程
CALL update_order_status(1, 'delivered');

-- 创建带异常处理的函数
CREATE OR REPLACE FUNCTION safe_divide(numerator DECIMAL, denominator DECIMAL)
RETURNS DECIMAL AS $$
BEGIN
    IF denominator = 0 THEN
        RAISE EXCEPTION 'Division by zero is not allowed';
    END IF;
    RETURN numerator / denominator;
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'Error occurred: %', SQLERRM;
        RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 创建测试表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    category VARCHAR(50),
    price DECIMAL(10,2),
    tags TEXT[],
    description TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 1. B-tree索引（默认索引类型，适用于等值和范围查询）
CREATE INDEX idx_products_price ON products USING btree (price);
CREATE INDEX idx_products_category_price ON products USING btree (category, price);

-- 查询使用复合索引
SELECT * FROM products WHERE category = 'Electronics' AND price BETWEEN 100 AND 500;

-- 2. 部分索引（Partial Index）：只索引满足条件的行
CREATE INDEX idx_products_high_value ON products (price) WHERE price > 1000;

-- 3. 表达式索引（Expression Index）：基于函数或表达式
CREATE INDEX idx_products_lower_name ON products (LOWER(name));
SELECT * FROM products WHERE LOWER(name) = 'laptop';

-- 4. GIN索引（适用于数组、全文检索、JSONB）
CREATE INDEX idx_products_tags ON products USING gin (tags);
SELECT * FROM products WHERE tags @> ARRAY['electronics', 'sale'];

-- 5. 全文检索索引
CREATE INDEX idx_products_description_gin ON products USING gin (to_tsvector('english', description));
SELECT * FROM products 
WHERE to_tsvector('english', description) @@ to_tsquery('english', 'fast & reliable');

-- 6. 查看查询执行计划
EXPLAIN ANALYZE SELECT * FROM products WHERE price < 500;

-- 7. 索引维护：重建索引
REINDEX INDEX idx_products_price;
REINDEX TABLE products;

# 使用pg_dump进行逻辑备份（从本地导出）
pg_dump -h pgm-xxxxxxx.pg.rds.aliyuncs.com -p 5432 -U appuser -d myappdb -F c -f myappdb_backup.dump

# 使用pg_restore恢复逻辑备份
pg_restore -h pgm-xxxxxxx.pg.rds.aliyuncs.com -p 5432 -U appuser -d myappdb myappdb_backup.dump

-- 查看当前参数设置
SHOW shared_buffers;
SHOW max_connections;
SHOW work_mem;

-- 查看所有参数
SELECT name, setting, unit, context FROM pg_settings WHERE name LIKE '%buffer%' OR name LIKE '%mem%';

-- 在会话级别临时修改参数（部分参数支持）
SET work_mem = '64MB';
SET statement_timeout = '30s';

-- 创建PostGIS扩展
CREATE EXTENSION postgis;

-- 创建包含地理空间数据的表
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    geom GEOMETRY(Point, 4326)
);

-- 插入空间数据
INSERT INTO locations (name, geom) VALUES
('Beijing', ST_SetSRID(ST_MakePoint(116.4, 39.9), 4326)),
('Shanghai', ST_SetSRID(ST_MakePoint(121.5, 31.2), 4326));

-- 空间查询：计算距离
SELECT 
    a.name AS from_location,
    b.name AS to_location,
    ST_Distance(a.geom::geography, b.geom::geography) AS distance_meters
FROM locations a, locations b
WHERE a.id < b.id;

-- 创建Ganos时空扩展
CREATE EXTENSION ganos_spatial;
CREATE EXTENSION ganos_trajectory;

-- 创建rds_ai扩展
CREATE EXTENSION rds_ai;

-- 使用AI模型进行文本向量化（示例）
-- SELECT ai_embedding('text-embedding-v1', '需要向量化的文本内容');

-- 使用AI模型进行问答
-- SELECT ai_chat('qwen-turbo', '请介绍一下PostgreSQL');