PostgreSQL#

1
-- 创建地址复合类型
2
CREATE TYPE address AS (
3
    street VARCHAR(100),
4
    city VARCHAR(50),
5
    state VARCHAR(50),
6
    postal_code VARCHAR(20)
7
);
8

9
-- 创建用户表使用复合类型
10
CREATE TABLE users (
11
    id SERIAL PRIMARY KEY,
12
    name VARCHAR(100),
13
    home_address address,
14
    work_address address
15
);
16

17
-- 插入数据
18
INSERT INTO users (name, home_address)
19
VALUES ('张三', ('中山路123号', '北京', '北京', '100000'));
20

21
-- 查询复合类型字段
22
SELECT name, (home_address).city FROM users;

1
-- 创建基础表
2
-- 员工表
3
CREATE TABLE employees (
4
    id SERIAL PRIMARY KEY,
5
    name VARCHAR(100),
6
    email VARCHAR(100)
7
);
8

9
-- 创建继承表
10
-- 公司管理表
11
CREATE TABLE managers (
12
    department VARCHAR(50),
13
    level INTEGER
14
) INHERITS (employees);
15
-- 公司开发表
16
CREATE TABLE developers (
17
    programming_language VARCHAR(50),
18
    years_experience INTEGER
19
) INHERITS (employees);
20

21
-- 插入数据
22
INSERT INTO employees (name, email)
23
VALUES ('通用职员', '[email protected]');
24

25
INSERT INTO managers (name, email, department, level)
26
VALUES ('陈经理', '[email protected]', '技术部', 3);
27

28
INSERT INTO developers (name, email, programming_language, years_experience)
29
VALUES ('王程序员', '[email protected]', 'Python', 5);
30

31
-- 查询所有员工
32
SELECT * FROM employees;
33
-- 查询特定类型员工
34
SELECT * FROM ONLY employees;

1
-- 创建包含数组的表
2
CREATE TABLE products (
3
    id SERIAL PRIMARY KEY,
4
    name VARCHAR(100),
5
    tags TEXT[],
6
    price_history DECIMAL(10,2)[]
7
);
8

9
-- 插入数组数据
10
INSERT INTO products (name, tags, price_history)
11
VALUES ('智能手机', ARRAY['电子产品', '通讯', '生活用品'],
12
        ARRAY[2999.00, 2899.00, 2799.00]);
13

14
-- 查询包含特定标签的产品
15
SELECT * FROM products WHERE '电子产品' = ANY(tags);
16

17
-- 获取数组长度
18
SELECT name, array_length(tags, 1) FROM products;

1
-- 创建包含JSONB的表
2
CREATE TABLE documents (
3
    id SERIAL PRIMARY KEY,
4
    title VARCHAR(200),
5
    content JSONB,
6
    metadata JSONB,
7
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
8
);
9

10
-- 插入JSON文档
11
INSERT INTO documents (title, content, metadata)
12
VALUES (
13
    '用户配置文件',
14
    '{
15
        "user_id": 1001,
16
        "preferences": {
17
            "theme": "dark",
18
            "language": "zh-CN",
19
            "notifications": {
20
                "email": true,
21
                "sms": false,
22
                "push": true
23
            }
24
        },
25
        "profile": {
26
            "name": "张三",
27
            "age": 28,
28
            "skills": ["Python", "JavaScript", "SQL"]
29
        }
30
    }'::jsonb,
31
    '{
32
        "version": "1.0",
33
        "author": "admin",
34
        "tags": ["用户", "配置", "个人资料"]
35
    }'::jsonb
36
);

1
-- 基本JSON路径查询
2
SELECT title, content->'user_id' as user_id
3
FROM documents;
4

5
-- 获取嵌套JSON值
6
SELECT title,
7
       content->'preferences'->'theme' as theme,
8
       content->'preferences'->'notifications'->'email' as email_notif
9
FROM documents;
10

11
-- JSON数组查询
12
SELECT title, content->'profile'->'skills' as skills
13
FROM documents;
14

15
-- 检查JSON字段是否存在
16
SELECT title FROM documents
17
WHERE content ? 'preferences';
18

19
-- 检查JSON数组是否包含特定值
20
SELECT title FROM documents
21
WHERE content->'profile'->'skills' @> '["Python"]';
22

23
-- JSONB索引（提升查询性能）
24
CREATE INDEX idx_documents_content_gin ON documents USING gin (content);
25

26
-- 创建部分索引
27
CREATE INDEX idx_documents_user_id ON documents USING gin ((content->'user_id'))
28
WHERE content ? 'user_id';

1
-- 更新JSON字段
2
UPDATE documents
3
SET content = jsonb_set(
4
    content,
5
    '{preferences,theme}',
6
    '"light"'
7
)
8
WHERE id = 1;
9

10
-- 添加新的JSON字段
11
UPDATE documents
12
SET content = jsonb_insert(
13
    content,
14
    '{profile,location}',
15
    '{"city": "北京", "country": "中国"}'::jsonb
16
)
17
WHERE id = 1;
18

19
-- 删除JSON字段
20
UPDATE documents
21
SET content = content - 'temp_field'
22
WHERE content ? 'temp_field';
23

24
-- 追加到JSON数组
25
UPDATE documents
26
SET content = jsonb_set(
27
    content,
28
    '{profile,skills}',
29
    content->'profile'->'skills' || '["Docker"]'
30
)
31
WHERE id = 1;

1
-- 启用向量扩展
2
CREATE EXTENSION IF NOT EXISTS vector;
3

4
-- 创建AI知识库表
5
CREATE TABLE ai_knowledge_base (
6
    id SERIAL PRIMARY KEY,
7
    title VARCHAR(500) NOT NULL,
8
    content TEXT NOT NULL,
9
    embedding vector(5),  -- OpenAI embedding维度
10
    category VARCHAR(100),
11
    source VARCHAR(200),
12
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
13
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
14
);
15

16
-- 创建向量索引
17
CREATE INDEX idx_ai_knowledge_embedding ON ai_knowledge_base
18
USING ivfflat (embedding vector_cosine_ops)
19
WITH (lists = 100);

1
-- 插入知识条目（示例embedding）
2
INSERT INTO ai_knowledge_base (title, content, embedding, category, source) VALUES
3
('什么是机器学习？',
4
 '机器学习是人工智能的一个分支，它使计算机系统能够从数据中学习和改进，而无需明确编程。机器学习算法通过训练数据构建数学模型，以做出预测或决策。',
5
 '[-0.015, 0.032, -0.018, 0.041, 0.009]',
6
 '人工智能', 'AI教科书'),
7
('PostgreSQL数据库优势',
8
 'PostgreSQL是世界上最先进的开源关系型数据库，支持复杂查询、事务、并发控制，具有丰富的扩展功能，包括JSON支持、全文搜索、向量搜索等。',
9
 '[0.025, -0.014, 0.038, -0.002, 0.017]',
10
 '数据库', '技术文档'),
11
('深度学习基础',
12
 '深度学习是机器学习的一个子领域，它使用多层神经网络来学习数据的复杂模式。深度学习在图像识别、自然语言处理等领域取得了突破性进展。',
13
 '[-0.008, 0.021, 0.015, -0.033, 0.012]',
14
 '人工智能', '深度学习教程');

1
-- 创建相似度搜索函数
2
CREATE OR REPLACE FUNCTION find_similar_knowledge(
3
    query_embedding vector(5),
4
    similarity_threshold FLOAT DEFAULT 0.7,
5
    limit_count INTEGER DEFAULT 5
6
)
7
RETURNS TABLE(id INTEGER, title VARCHAR, content TEXT, similarity_score FLOAT) AS $$
8
BEGIN
9
    RETURN QUERY
10
    SELECT kb.id, kb.title, kb.content,
11
           1 - (kb.embedding <=> query_embedding) as similarity_score -- 计算 kb 表中某条记录的 embedding 向量与你传入的 query_embedding 向量之间的距离
12
    FROM ai_knowledge_base kb
13
    WHERE 1 - (kb.embedding <=> query_embedding) > similarity_threshold -- 计算每一行的相似度分数，然后只保留那些大于你设定的阈值的行
14
    ORDER BY kb.embedding <=> query_embedding
15
    LIMIT limit_count;
16
END;
17
$$ LANGUAGE plpgsql;
18

19
-- 创建RAG（检索增强生成）查询函数
20
CREATE OR REPLACE FUNCTION retrieve_context_for_query(
21
    query_text TEXT,
22
    query_embedding vector(5)
23
)
24
RETURNS TABLE(context TEXT, source_info VARCHAR) AS $$
25
BEGIN
26
    RETURN QUERY
27
    SELECT kb.content, kb.title || ' - ' || kb.category as source_info
28
    FROM ai_knowledge_base kb
29
    WHERE 1 - (kb.embedding <=> query_embedding) > 0.6
30
    ORDER BY kb.embedding <=> query_embedding
31
    LIMIT 3;
32
END;
33
$$ LANGUAGE plpgsql;

1
-- 创建扩展
2
CREATE EXTENSION IF NOT EXISTS pg_cron;

1
SELECT cron.schedule(
2
    'documents-migration',  -- 任务名称
3
    '* * * * *',           -- Cron表达式: 每分钟执行一次
4
    $$
5
    INSERT INTO public.documents_archive (id, title, "content", metadata, created_at)
6
    SELECT id, title, "content", metadata, created_at
7
    FROM public.documents
8
    WHERE id NOT IN (SELECT id FROM public.documents_archive);
9
    $$
10
);

1
-- 查看所有定时任务
2
SELECT * FROM cron.job;
3

4
-- 查看任务执行历史
5
SELECT * FROM cron.job_run_details ORDER BY starttime DESC LIMIT 10;
6

7
-- 取消定时任务
8
SELECT cron.unschedule('cleanup-expired-data');
9

10
-- 更新定时任务
11
SELECT cron.schedule('cleanup-expired-data', '0 1 * * *', 'VACUUM ANALYZE;');
12

13
-- 创建任务执行报告
14
CREATE OR REPLACE FUNCTION generate_cron_report()
15
RETURNS TABLE(job_name VARCHAR, last_run TIMESTAMP, next_run TIMESTAMP, status VARCHAR, total_runs BIGINT) AS $$
16
BEGIN
17
    RETURN QUERY
18
    SELECT
19
        j.jobid::TEXT,
20
        MAX(r.starttime),
21
        j.schedule,
22
        CASE
23
            WHEN j.active THEN 'ACTIVE'
24
            ELSE 'INACTIVE'
25
        END,
26
        COUNT(r.runid)
27
    FROM cron.job j
28
    LEFT JOIN cron.job_run_details r ON j.jobid = r.jobid
29
    GROUP BY j.jobid, j.schedule, j.active;
30
END;
31
$$ LANGUAGE plpgsql;
32

33
-- 查看定时任务报告
34
SELECT * FROM generate_cron_report();