跳转至

PostgreSQL 高级用法

PostgreSQL 是最强大的开源关系型数据库，2025 年已超越 MySQL 成为开发者最常用的数据库。它不仅支持标准 SQL，还支持 JSON、全文搜索、地理信息等高级功能，是生信数据管理的理想选择。

核心知识点

知识点说明
数据库类型关系型数据库（RDBMS），支持扩展功能
最新版本PostgreSQL 17（2024.09 发布）
许可证PostgreSQL License（类似 MIT，非常自由）
核心优势ACID 事务、丰富数据类型、可扩展、标准兼容
v17 亮点增量备份、JSON_TABLE()、MERGE 增强、vacuum 优化
特色功能JSON/JSONB、全文搜索、CTE、窗口函数、分区表
市场地位2025 年超越 MySQL 成为开发者最常用数据库（55%）

安装配置

方法一：系统包管理器

# Ubuntu/Debian
sudo apt update
sudo apt install postgresql postgresql-contrib  # 安装 PostgreSQL

# 启动服务
sudo systemctl start postgresql     # 启动
sudo systemctl enable postgresql    # 开机自启

# 切换到 postgres 用户
sudo -u postgres psql               # 进入 PostgreSQL 命令行

方法二：Docker（推荐开发环境）

# Docker 运行 PostgreSQL 17
docker run -d \
  --name postgres \
  -p 5432:5432 \                    # 端口映射
  -e POSTGRES_PASSWORD=mypassword \  # 设置密码
  -e POSTGRES_DB=bioinfo \           # 创建默认数据库
  -v pgdata:/var/lib/postgresql/data \  # 数据持久化
  postgres:17                        # 使用 PG17 镜像

# 连接
psql -h localhost -U postgres -d bioinfo  # 连接数据库

方法三：Conda

conda install -c conda-forge postgresql  # 安装
initdb -D ./pgdata                       # 初始化数据目录
pg_ctl -D ./pgdata start                 # 启动

基本使用

1. 数据库和用户管理

-- 创建数据库
CREATE DATABASE bioinfo_db;                    -- 创建数据库

-- 创建用户
CREATE USER bioinfo_user WITH PASSWORD 'pass123';  -- 创建用户

-- 授权
GRANT ALL PRIVILEGES ON DATABASE bioinfo_db TO bioinfo_user;  -- 授予全部权限

-- 切换数据库
\c bioinfo_db                                  -- 连接到 bioinfo_db

-- 常用 psql 命令
\l                                             -- 列出所有数据库
\dt                                            -- 列出当前数据库的表
\d table_name                                  -- 查看表结构
\q                                             -- 退出

2. 创建表和基本操作

-- 创建样本表
CREATE TABLE samples (
    id SERIAL PRIMARY KEY,                     -- 自增主键
    sample_id VARCHAR(50) UNIQUE NOT NULL,     -- 样本 ID，唯一
    patient_age INTEGER CHECK (patient_age > 0),  -- 年龄，约束 > 0
    diagnosis VARCHAR(20) NOT NULL,            -- 诊断
    bmi DECIMAL(5,2),                          -- BMI
    sequencing_date DATE,                      -- 测序日期
    metadata JSONB,                            -- JSON 元数据（灵活存储）
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- 创建时间
);

-- 插入数据
INSERT INTO samples (sample_id, patient_age, diagnosis, bmi, metadata)
VALUES
    ('T2D_001', 55, 'T2D', 28.5, '{"platform": "NovaSeq", "reads": 50000000}'),
    ('T2D_002', 62, 'T2D', 30.1, '{"platform": "NovaSeq", "reads": 45000000}'),
    ('HC_001', 48, 'Healthy', 22.0, '{"platform": "HiSeq", "reads": 40000000}');

-- 查询
SELECT sample_id, diagnosis, bmi
FROM samples
WHERE diagnosis = 'T2D' AND bmi > 25
ORDER BY bmi DESC;                             -- 查询 BMI > 25 的 T2D 样本

高级用法

1. JSONB 操作（灵活存储非结构化数据）

-- JSONB 字段查询
SELECT sample_id, metadata->>'platform' AS platform  -- 提取 JSON 字段
FROM samples
WHERE metadata->>'platform' = 'NovaSeq';              -- JSON 条件过滤

-- JSONB 包含查询
SELECT * FROM samples
WHERE metadata @> '{"platform": "NovaSeq"}';          -- 包含指定键值对

-- 更新 JSONB 字段
UPDATE samples
SET metadata = metadata || '{"qc_passed": true}'      -- 合并新字段
WHERE sample_id = 'T2D_001';

-- JSONB 索引（加速查询）
CREATE INDEX idx_metadata ON samples USING GIN (metadata);  -- GIN 索引

2. 窗口函数（Window Functions）

-- 计算每个诊断组内的 BMI 排名
SELECT
    sample_id,
    diagnosis,
    bmi,
    RANK() OVER (PARTITION BY diagnosis ORDER BY bmi DESC) AS bmi_rank,  -- 组内排名
    AVG(bmi) OVER (PARTITION BY diagnosis) AS group_avg_bmi              -- 组平均值
FROM samples;

-- 累计分布
SELECT
    sample_id,
    bmi,
    PERCENT_RANK() OVER (ORDER BY bmi) AS percentile  -- 百分位排名
FROM samples;

3. CTE（公用表表达式）

-- 用 CTE 组织复杂查询
WITH high_bmi AS (                     -- 定义 CTE
    SELECT sample_id, bmi, diagnosis
    FROM samples
    WHERE bmi > 25
),
bmi_stats AS (                         -- 第二个 CTE
    SELECT diagnosis,
           COUNT(*) AS count,
           AVG(bmi) AS avg_bmi
    FROM high_bmi
    GROUP BY diagnosis
)
SELECT * FROM bmi_stats                -- 使用 CTE
ORDER BY avg_bmi DESC;

4. 全文搜索

-- 添加全文搜索支持
ALTER TABLE samples ADD COLUMN search_vector tsvector;  -- 添加搜索向量列

-- 更新搜索向量
UPDATE samples
SET search_vector = to_tsvector('english',
    COALESCE(sample_id, '') || ' ' || COALESCE(diagnosis, ''));

-- 全文搜索查询
SELECT sample_id, diagnosis
FROM samples
WHERE search_vector @@ to_tsquery('english', 'T2D');  -- 搜索包含 T2D 的记录

-- 创建 GIN 索引加速搜索
CREATE INDEX idx_search ON samples USING GIN (search_vector);

5. 分区表（大数据优化）

-- 按范围分区（比如按年份分区）
CREATE TABLE measurements (
    id SERIAL,
    sample_id VARCHAR(50),
    measurement_date DATE NOT NULL,
    value DECIMAL(10,4)
) PARTITION BY RANGE (measurement_date);  -- 按日期范围分区

-- 创建分区
CREATE TABLE measurements_2024 PARTITION OF measurements
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');  -- 2024 年数据

CREATE TABLE measurements_2025 PARTITION OF measurements
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');  -- 2025 年数据

6. Python 操作（psycopg2）

import psycopg2  # 导入 PostgreSQL Python 驱动

# 连接数据库
conn = psycopg2.connect(
    host="localhost",
    database="bioinfo_db",
    user="bioinfo_user",
    password="pass123"
)
cursor = conn.cursor()  # 创建游标

# 查询
cursor.execute("""
    SELECT sample_id, bmi FROM samples
    WHERE diagnosis = %s AND bmi > %s
""", ("T2D", 25))  # 参数化查询（防 SQL 注入）

for row in cursor.fetchall():  # 获取所有结果
    print(f"样本: {row[0]}, BMI: {row[1]}")

# 插入
cursor.execute("""
    INSERT INTO samples (sample_id, diagnosis, bmi)
    VALUES (%s, %s, %s)
""", ("T2D_004", "T2D", 27.5))
conn.commit()  # 提交事务

# 关闭
cursor.close()
conn.close()

常见报错与解决

报错信息原因解决方法
connection refusedPostgreSQL 未启动或端口错sudo systemctl start postgresql
FATAL: password authentication failed密码错误检查密码，或修改 pg_hba.conf
relation does not exist表不存在检查表名和当前 schema
duplicate key violates unique constraint唯一键冲突检查数据或用 ON CONFLICT 处理
permission denied for table无权限GRANT SELECT ON table TO user;
deadlock detected事务死锁优化事务顺序，减少锁定时间

速查表

-- ===== PostgreSQL 速查表 =====

-- psql 命令
-- \l          列出数据库
-- \c dbname   连接数据库
-- \dt         列出表
-- \d table    表结构
-- \di         列出索引
-- \q          退出

-- 常用 SQL
CREATE DATABASE db;                   -- 创建数据库
CREATE TABLE t (id SERIAL PRIMARY KEY, name TEXT);  -- 创建表
INSERT INTO t (name) VALUES ('test'); -- 插入
SELECT * FROM t WHERE name = 'test'; -- 查询
UPDATE t SET name = 'new' WHERE id = 1;  -- 更新
DELETE FROM t WHERE id = 1;           -- 删除

-- JSONB
col->>'key'                           -- 提取文本值
col @> '{"k":"v"}'                    -- 包含查询
col || '{"new_key": "val"}'           -- 合并

-- 窗口函数
ROW_NUMBER() OVER (ORDER BY col)      -- 行号
RANK() OVER (PARTITION BY g ORDER BY v)  -- 分组排名
LAG(col) OVER (ORDER BY col)          -- 前一行值

-- 索引
CREATE INDEX idx ON t (col);          -- B-tree 索引
CREATE INDEX idx ON t USING GIN (jsonb_col);  -- JSONB 索引
CREATE INDEX idx ON t USING GIN (vector_col);  -- 全文索引

-- 备份恢复
-- pg_dump dbname > backup.sql        备份
-- psql dbname < backup.sql           恢复
-- pg_basebackup -D backup_dir        物理备份

-- Python: pip install psycopg2-binary