PostgreSQL 基础入门
2026/3/20大约 17 分钟
PostgreSQL 基础入门
一、PostgreSQL 简介
1.1 什么是 PostgreSQL
PostgreSQL（常被称为 Postgres）是一个功能强大的开源对象关系型数据库管理系统（ORDBMS）。它起源于 1986 年加州大学伯克利分校的 POSTGRES 项目，至今已有超过 35 年的活跃开发历史。
PostgreSQL 以其可靠性、功能健壮性和性能而著称，被广泛认为是最先进的开源数据库系统。它完全符合 ACID 原则，支持绝大多数 SQL:2011 标准，并提供了许多先进特性。
1.2 PostgreSQL 的核心特点
| 特性 | 描述 |
|---|---|
| 开源免费 | 完全开源，BSD 许可证，无商业使用限制 |
| 标准兼容 | 最符合 SQL 标准的开源数据库 |
| 丰富数据类型 | 支持 JSON、数组、范围、几何、网络地址等高级类型 |
| 高度可扩展 | 支持自定义数据类型、函数、操作符、索引类型 |
| MVCC | 多版本并发控制，读写互不阻塞 |
| 强大索引 | B-Tree、Hash、GiST、SP-GiST、GIN、BRIN 六种索引 |
| 全文搜索 | 内置全文搜索引擎，支持多语言 |
| 地理空间 | 通过 PostGIS 扩展支持地理信息处理 |
| 高可靠性 | 支持 WAL、流复制、逻辑复制 |
| 活跃社区 | 全球活跃的开发者社区，持续更新 |
1.3 PostgreSQL 版本演进
本文档基于 PostgreSQL 14/15 版本编写，这是目前生产环境中使用最广泛、最稳定的版本。
1.4 PostgreSQL vs MySQL
| 特性 | PostgreSQL | MySQL (InnoDB) |
|---|---|---|
| 开源协议 | BSD（无限制） | GPL（有限制） |
| SQL 标准 | 最完整支持 | 部分支持 |
| 事务 | 完整 ACID | 完整 ACID |
| 复杂查询 | 优秀 | 良好 |
| JSON 支持 | 原生 JSONB | JSON 类型 |
| 全文搜索 | 内置 | 需要引擎支持 |
| 地理空间 | PostGIS（强大） | 基础支持 |
| 扩展性 | 极强 | 一般 |
| 读性能 | 优秀 | 优秀 |
| 写性能 | 良好 | 优秀 |
| 学习曲线 | 稍陡峭 | 相对平缓 |
| 适用场景 | 复杂业务、数据分析 | Web 应用、高并发写入 |
1.5 适用场景
PostgreSQL 特别适合以下场景：
二、PostgreSQL 安装与配置
2.1 Linux 环境安装
CentOS/RHEL 安装
# 1. 安装 PostgreSQL 官方 yum 源
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# 2. 安装 PostgreSQL 15
sudo yum install -y postgresql15-server postgresql15-contrib
# 3. 初始化数据库
sudo /usr/pgsql-15/bin/postgresql-15-setup initdb
# 4. 启动服务
sudo systemctl start postgresql-15
sudo systemctl enable postgresql-15
# 5. 验证安装
sudo -u postgres psql -c "SELECT version();"
Ubuntu/Debian 安装
# 1. 添加官方 APT 源
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
# 2. 更新并安装
sudo apt-get update
sudo apt-get install -y postgresql-15 postgresql-contrib-15
# 3. 服务管理
sudo systemctl start postgresql
sudo systemctl enable postgresql
# 4. 验证安装
sudo -u postgres psql -c "SELECT version();"
2.2 Docker 安装（推荐开发环境）
# 拉取 PostgreSQL 15 镜像
docker pull postgres:15
# 创建数据目录
mkdir -p /data/postgresql
# 启动容器
docker run -d \
--name postgres \
-p 5432:5432 \
-e POSTGRES_PASSWORD=your_password \
-e POSTGRES_USER=postgres \
-e POSTGRES_DB=postgres \
-e PGDATA=/var/lib/postgresql/data/pgdata \
-v /data/postgresql:/var/lib/postgresql/data \
postgres:15
# 连接到数据库
docker exec -it postgres psql -U postgres
Docker Compose 方式
# docker-compose.yml
version: "3.8"
services:
postgres:
image: postgres:15
container_name: postgres
restart: always
environment:
POSTGRES_USER: postgres
POSTGRES_PASSWORD: your_password
POSTGRES_DB: mydb
PGDATA: /var/lib/postgresql/data/pgdata
volumes:
- ./postgres-data:/var/lib/postgresql/data
- ./init.sql:/docker-entrypoint-initdb.d/init.sql
ports:
- "5432:5432"
healthcheck:
test: ["CMD-SHELL", "pg_isready -U postgres"]
interval: 10s
timeout: 5s
retries: 5
2.3 Windows 安装
- 下载 Windows 安装包：https://www.postgresql.org/download/windows/
- 运行安装程序，按向导完成安装
- 设置 postgres 用户密码
- 可选安装 pgAdmin 4 图形化管理工具
- 将 PostgreSQL 的 bin 目录添加到 PATH 环境变量
2.4 配置文件详解
PostgreSQL 主要有两个配置文件：
postgresql.conf：主配置文件pg_hba.conf：客户端认证配置
postgresql.conf 核心配置
# =========================
# 连接配置
# =========================
# 监听地址，'*' 表示所有地址
listen_addresses = '*'
# 监听端口
port = 5432
# 最大连接数
max_connections = 200
# 超级用户保留连接数
superuser_reserved_connections = 3
# =========================
# 内存配置
# =========================
# 共享缓冲区，建议为物理内存的 25%
shared_buffers = 2GB
# 工作内存，每个连接使用
work_mem = 64MB
# 维护操作内存
maintenance_work_mem = 512MB
# 有效缓存大小，建议为物理内存的 50-75%
effective_cache_size = 6GB
# =========================
# WAL 配置
# =========================
# WAL 级别：minimal, replica, logical
wal_level = replica
# WAL 缓冲区大小
wal_buffers = 64MB
# 检查点完成目标
checkpoint_completion_target = 0.9
# 最大 WAL 大小
max_wal_size = 4GB
min_wal_size = 1GB
# =========================
# 查询优化配置
# =========================
# 随机页面成本
random_page_cost = 1.1 # SSD 建议 1.1，HDD 建议 4.0
# 有效 IO 并发数
effective_io_concurrency = 200 # SSD 建议 200，HDD 建议 2
# 并行查询
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4
# =========================
# 日志配置
# =========================
# 日志目录
log_directory = 'log'
# 日志文件名格式
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
# 日志轮转
log_rotation_age = 1d
log_rotation_size = 100MB
# 记录慢查询
log_min_duration_statement = 1000 # 毫秒
# 记录语句类型
log_statement = 'ddl' # none, ddl, mod, all
# 记录连接
log_connections = on
log_disconnections = on
# =========================
# 自动清理（VACUUM）配置
# =========================
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.05
pg_hba.conf 认证配置
# TYPE DATABASE USER ADDRESS METHOD
# 本地 socket 连接
local all all peer
# IPv4 本地连接
host all all 127.0.0.1/32 scram-sha-256
# IPv4 局域网连接
host all all 192.168.1.0/24 scram-sha-256
# IPv4 任意地址（生产环境慎用）
host all all 0.0.0.0/0 scram-sha-256
# IPv6 本地连接
host all all ::1/128 scram-sha-256
# 复制连接
host replication all 192.168.1.0/24 scram-sha-256
认证方法说明：
| 方法 | 说明 |
|---|---|
| trust | 无条件允许连接（仅开发环境） |
| reject | 拒绝连接 |
| scram-sha-256 | 加密密码认证（推荐） |
| md5 | MD5 密码认证（已不推荐） |
| password | 明文密码（不安全） |
| peer | 操作系统用户名认证（本地） |
| ident | Ident 服务器认证 |
| ldap | LDAP 认证 |
| cert | SSL 证书认证 |
2.5 首次配置
# 切换到 postgres 用户
sudo -u postgres psql
# 修改 postgres 用户密码
ALTER USER postgres WITH PASSWORD 'your_secure_password';
# 创建新用户
CREATE USER app_user WITH PASSWORD 'user_password';
# 创建数据库
CREATE DATABASE myapp OWNER app_user;
# 授予权限
GRANT ALL PRIVILEGES ON DATABASE myapp TO app_user;
# 退出
\q
# 重新加载配置
sudo systemctl reload postgresql-15
三、连接与基本操作
3.1 命令行客户端 psql
# 基本连接
psql -h localhost -p 5432 -U postgres -d mydb
# 连接参数
# -h: 主机地址
# -p: 端口
# -U: 用户名
# -d: 数据库名
# -W: 强制输入密码
# 使用连接字符串
psql "postgresql://user:password@localhost:5432/mydb"
# 设置环境变量
export PGHOST=localhost
export PGPORT=5432
export PGUSER=postgres
export PGPASSWORD=password
export PGDATABASE=mydb
psql
3.2 psql 常用命令
-- 帮助命令
\? -- 显示 psql 命令帮助
\h -- 显示 SQL 命令帮助
\h SELECT -- 显示特定命令帮助
-- 连接命令
\c mydb -- 切换数据库
\c mydb username -- 以指定用户切换数据库
\conninfo -- 显示当前连接信息
-- 列表命令
\l -- 列出所有数据库
\l+ -- 列出数据库详细信息
\du -- 列出所有用户/角色
\du+ -- 列出用户详细信息
\dn -- 列出所有 schema
\dt -- 列出当前 schema 的表
\dt *.* -- 列出所有 schema 的表
\dt+ users -- 显示表详细信息
\di -- 列出索引
\dv -- 列出视图
\df -- 列出函数
\ds -- 列出序列
-- 查看定义
\d users -- 显示表结构
\d+ users -- 显示表详细结构（含注释）
\sf function_name -- 显示函数定义
\sv view_name -- 显示视图定义
-- 执行命令
\i /path/to/file.sql -- 执行 SQL 文件
\o /path/to/output.txt -- 输出到文件
\copy -- 复制数据
-- 格式化输出
\x -- 切换扩展显示模式
\a -- 切换对齐模式
\t -- 切换只显示数据模式
\timing -- 切换显示执行时间
-- 编辑
\e -- 使用外部编辑器编辑
\ef function_name -- 编辑函数
-- 退出
\q -- 退出 psql
3.3 数据库管理
-- 创建数据库
CREATE DATABASE myapp
WITH
OWNER = app_user
ENCODING = 'UTF8'
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
TABLESPACE = pg_default
CONNECTION LIMIT = -1;
-- 添加注释
COMMENT ON DATABASE myapp IS '应用程序主数据库';
-- 修改数据库
ALTER DATABASE myapp SET timezone TO 'Asia/Shanghai';
ALTER DATABASE myapp RENAME TO myapp_new;
ALTER DATABASE myapp OWNER TO new_owner;
-- 删除数据库
DROP DATABASE IF EXISTS myapp;
-- 查看数据库大小
SELECT pg_database.datname,
pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database
ORDER BY pg_database_size(pg_database.datname) DESC;
3.4 Schema 管理
Schema 是数据库中的命名空间，用于组织数据库对象。
-- 创建 schema
CREATE SCHEMA IF NOT EXISTS app;
CREATE SCHEMA auth AUTHORIZATION app_user;
-- 设置搜索路径
SET search_path TO app, public;
-- 或永久设置
ALTER DATABASE myapp SET search_path TO app, public;
-- 查看当前搜索路径
SHOW search_path;
-- 在指定 schema 中创建表
CREATE TABLE app.users (
id SERIAL PRIMARY KEY,
username VARCHAR(50)
);
-- 授权
GRANT USAGE ON SCHEMA app TO app_user;
GRANT ALL ON ALL TABLES IN SCHEMA app TO app_user;
-- 删除 schema
DROP SCHEMA app CASCADE; -- 级联删除所有对象
3.5 用户与权限管理
-- 创建用户（角色）
CREATE USER app_user WITH
LOGIN
PASSWORD 'secure_password'
VALID UNTIL '2025-12-31'
CONNECTION LIMIT 10;
-- 创建角色（无登录权限）
CREATE ROLE readonly;
-- 授予角色
GRANT readonly TO app_user;
-- 权限管理
-- 数据库级别
GRANT CONNECT ON DATABASE myapp TO app_user;
GRANT CREATE ON DATABASE myapp TO app_user;
-- Schema 级别
GRANT USAGE ON SCHEMA public TO app_user;
GRANT CREATE ON SCHEMA public TO app_user;
-- 表级别
GRANT SELECT, INSERT, UPDATE, DELETE ON users TO app_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_user;
-- 序列级别
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;
-- 设置默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
-- 撤销权限
REVOKE INSERT ON users FROM app_user;
-- 修改用户
ALTER USER app_user WITH PASSWORD 'new_password';
ALTER USER app_user VALID UNTIL 'infinity';
ALTER USER app_user CONNECTION LIMIT 20;
-- 删除用户
DROP USER IF EXISTS app_user;
四、SQL 基础操作
4.1 创建表
-- 创建表
CREATE TABLE users (
-- 主键，自增
id SERIAL PRIMARY KEY,
-- 字符串类型
username VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL,
-- 整数类型
age SMALLINT CHECK (age >= 0 AND age <= 150),
status SMALLINT DEFAULT 1,
-- 布尔类型
is_active BOOLEAN DEFAULT true,
-- 时间类型
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
-- 约束
CONSTRAINT users_username_check CHECK (LENGTH(username) >= 3)
);
-- 添加注释
COMMENT ON TABLE users IS '用户表';
COMMENT ON COLUMN users.id IS '用户ID';
COMMENT ON COLUMN users.username IS '用户名';
-- 创建索引
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created_at ON users(created_at DESC);
-- 创建带外键的表
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
order_no VARCHAR(32) NOT NULL UNIQUE,
total_amount NUMERIC(12, 2) NOT NULL,
status VARCHAR(20) DEFAULT 'pending',
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
4.2 修改表
-- 添加列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users ADD COLUMN nickname VARCHAR(50) DEFAULT '';
-- 修改列类型
ALTER TABLE users ALTER COLUMN phone TYPE VARCHAR(15);
-- 修改列默认值
ALTER TABLE users ALTER COLUMN status SET DEFAULT 0;
ALTER TABLE users ALTER COLUMN status DROP DEFAULT;
-- 设置非空约束
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;
ALTER TABLE users ALTER COLUMN phone DROP NOT NULL;
-- 重命名列
ALTER TABLE users RENAME COLUMN phone TO mobile;
-- 删除列
ALTER TABLE users DROP COLUMN IF EXISTS nickname;
-- 添加约束
ALTER TABLE users ADD CONSTRAINT users_mobile_unique UNIQUE (mobile);
ALTER TABLE users ADD CONSTRAINT users_age_check CHECK (age >= 0);
-- 删除约束
ALTER TABLE users DROP CONSTRAINT users_mobile_unique;
-- 重命名表
ALTER TABLE users RENAME TO user_info;
-- 修改表所有者
ALTER TABLE users OWNER TO new_owner;
-- 删除表
DROP TABLE IF EXISTS users CASCADE;
-- 清空表
TRUNCATE TABLE users RESTART IDENTITY CASCADE;
4.3 CRUD 操作
INSERT
-- 单条插入
INSERT INTO users (username, email, password_hash)
VALUES ('john', 'john@example.com', 'hash_value');
-- 返回插入的数据
INSERT INTO users (username, email, password_hash)
VALUES ('alice', 'alice@example.com', 'hash_value')
RETURNING id, username, created_at;
-- 批量插入
INSERT INTO users (username, email, password_hash)
VALUES
('bob', 'bob@example.com', 'hash1'),
('charlie', 'charlie@example.com', 'hash2'),
('david', 'david@example.com', 'hash3');
-- 插入或更新（UPSERT）- PostgreSQL 9.5+
INSERT INTO users (username, email, password_hash)
VALUES ('john', 'john@example.com', 'new_hash')
ON CONFLICT (email)
DO UPDATE SET
password_hash = EXCLUDED.password_hash,
updated_at = CURRENT_TIMESTAMP;
-- 插入或忽略
INSERT INTO users (username, email, password_hash)
VALUES ('john', 'john@example.com', 'hash')
ON CONFLICT (email) DO NOTHING;
-- 从查询结果插入
INSERT INTO users_backup (username, email, password_hash)
SELECT username, email, password_hash
FROM users
WHERE created_at < '2024-01-01';
SELECT
-- 基本查询
SELECT * FROM users;
SELECT id, username, email FROM users;
-- 条件查询
SELECT * FROM users WHERE status = 1;
SELECT * FROM users WHERE username LIKE 'j%';
SELECT * FROM users WHERE id IN (1, 2, 3);
SELECT * FROM users WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';
SELECT * FROM users WHERE email IS NOT NULL;
-- 排序
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY status ASC, created_at DESC;
-- 分页（推荐）
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;
-- 分页（替代写法）
SELECT * FROM users ORDER BY id FETCH FIRST 10 ROWS ONLY;
-- 去重
SELECT DISTINCT status FROM users;
SELECT DISTINCT ON (status) * FROM users ORDER BY status, created_at DESC;
-- 聚合查询
SELECT COUNT(*) as total FROM users;
SELECT status, COUNT(*) as count FROM users GROUP BY status;
SELECT status, COUNT(*) as count
FROM users
GROUP BY status
HAVING COUNT(*) > 10;
-- 别名
SELECT
u.username AS name,
u.email AS mail,
COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username, u.email;
UPDATE
-- 单条更新
UPDATE users
SET email = 'new_email@example.com'
WHERE id = 1;
-- 多字段更新
UPDATE users SET
email = 'new_email@example.com',
status = 0,
updated_at = CURRENT_TIMESTAMP
WHERE id = 1;
-- 返回更新后的数据
UPDATE users
SET status = 0
WHERE id = 1
RETURNING *;
-- 批量更新
UPDATE users SET status = 0 WHERE id IN (1, 2, 3);
-- 使用子查询更新
UPDATE orders
SET status = 'cancelled'
WHERE user_id IN (
SELECT id FROM users WHERE status = 0
);
-- 从另一表更新
UPDATE orders o
SET status = 'vip'
FROM users u
WHERE o.user_id = u.id AND u.is_vip = true;
DELETE
-- 条件删除
DELETE FROM users WHERE id = 1;
-- 批量删除
DELETE FROM users WHERE status = 0;
-- 返回删除的数据
DELETE FROM users WHERE id = 1 RETURNING *;
-- 使用子查询删除
DELETE FROM orders
WHERE user_id IN (
SELECT id FROM users WHERE created_at < '2020-01-01'
);
-- 删除所有数据
DELETE FROM users;
-- 清空表（更快，重置序列）
TRUNCATE TABLE users RESTART IDENTITY;
4.4 连接查询
-- 内连接
SELECT u.username, o.order_no, o.total_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- 左连接
SELECT u.username, o.order_no, o.total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- 右连接
SELECT u.username, o.order_no, o.total_amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;
-- 全连接
SELECT u.username, o.order_no, o.total_amount
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;
-- 交叉连接
SELECT u.username, p.name
FROM users u
CROSS JOIN products p;
-- 自连接
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
-- 多表连接
SELECT
u.username,
o.order_no,
oi.quantity,
p.name AS product_name
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;
4.5 子查询
-- WHERE 子查询
SELECT * FROM users
WHERE id IN (SELECT DISTINCT user_id FROM orders);
-- 标量子查询
SELECT
username,
(SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count
FROM users;
-- FROM 子查询（派生表）
SELECT avg_amount, COUNT(*)
FROM (
SELECT user_id, AVG(total_amount) AS avg_amount
FROM orders
GROUP BY user_id
) AS user_avg
WHERE avg_amount > 100
GROUP BY avg_amount;
-- EXISTS 子查询
SELECT * FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id
);
-- NOT EXISTS
SELECT * FROM users u
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id
);
-- LATERAL 子查询（PostgreSQL 特有）
SELECT u.username, latest_order.*
FROM users u
LEFT JOIN LATERAL (
SELECT order_no, total_amount, created_at
FROM orders
WHERE user_id = u.id
ORDER BY created_at DESC
LIMIT 3
) AS latest_order ON true;
五、常用函数
5.1 字符串函数
-- 连接字符串
SELECT CONCAT('Hello', ' ', 'World');
SELECT 'Hello' || ' ' || 'World';
SELECT CONCAT_WS(',', 'a', 'b', 'c'); -- a,b,c
-- 长度
SELECT LENGTH('Hello'); -- 字节长度
SELECT CHAR_LENGTH('你好'); -- 字符长度
-- 大小写转换
SELECT UPPER('hello'), LOWER('HELLO');
SELECT INITCAP('hello world'); -- Hello World
-- 截取
SELECT SUBSTRING('Hello World' FROM 1 FOR 5); -- Hello
SELECT LEFT('Hello', 2), RIGHT('Hello', 2);
-- 查找替换
SELECT POSITION('World' IN 'Hello World'); -- 7
SELECT REPLACE('Hello', 'l', 'L');
-- 去除空格
SELECT TRIM(' hello ');
SELECT LTRIM(' hello'), RTRIM('hello ');
SELECT TRIM(BOTH 'x' FROM 'xxhelloxx');
-- 填充
SELECT LPAD('123', 5, '0'); -- 00123
SELECT RPAD('123', 5, '0'); -- 12300
-- 分割
SELECT SPLIT_PART('a,b,c', ',', 2); -- b
SELECT STRING_TO_ARRAY('a,b,c', ','); -- {a,b,c}
-- 正则表达式
SELECT 'hello123' ~ '[0-9]+'; -- true
SELECT REGEXP_REPLACE('hello123', '[0-9]+', 'XXX');
SELECT REGEXP_MATCHES('hello123world456', '[0-9]+', 'g');
5.2 数值函数
-- 基本运算
SELECT ABS(-10); -- 10
SELECT CEIL(4.1), FLOOR(4.9); -- 5, 4
SELECT ROUND(4.567, 2); -- 4.57
SELECT TRUNC(4.567, 2); -- 4.56
-- 取模
SELECT MOD(10, 3); -- 1
SELECT 10 % 3; -- 1
-- 幂运算
SELECT POWER(2, 10); -- 1024
SELECT SQRT(16); -- 4
SELECT CBRT(27); -- 3
-- 随机数
SELECT RANDOM(); -- 0-1 之间
SELECT FLOOR(RANDOM() * 100 + 1); -- 1-100 之间
-- 符号
SELECT SIGN(-10), SIGN(10); -- -1, 1
-- 数值格式化
SELECT TO_CHAR(1234567.89, '9,999,999.99'); -- 1,234,567.89
5.3 日期时间函数
-- 当前时间
SELECT CURRENT_DATE; -- 2024-01-15
SELECT CURRENT_TIME; -- 10:30:00+08
SELECT CURRENT_TIMESTAMP; -- 2024-01-15 10:30:00+08
SELECT NOW(); -- 同上
SELECT LOCALTIME; -- 不带时区
SELECT LOCALTIMESTAMP; -- 不带时区
-- 提取日期部分
SELECT EXTRACT(YEAR FROM NOW());
SELECT EXTRACT(MONTH FROM NOW());
SELECT EXTRACT(DAY FROM NOW());
SELECT EXTRACT(HOUR FROM NOW());
SELECT EXTRACT(DOW FROM NOW()); -- 星期几 (0-6)
SELECT EXTRACT(EPOCH FROM NOW()); -- Unix 时间戳
-- 日期格式化
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS');
SELECT TO_CHAR(NOW(), 'YYYY年MM月DD日');
-- 日期计算
SELECT NOW() + INTERVAL '1 day';
SELECT NOW() - INTERVAL '1 month';
SELECT NOW() + '1 year'::INTERVAL;
SELECT DATE '2024-12-31' - DATE '2024-01-01'; -- 天数差
-- 日期截断
SELECT DATE_TRUNC('month', NOW()); -- 月初
SELECT DATE_TRUNC('year', NOW()); -- 年初
SELECT DATE_TRUNC('hour', NOW()); -- 整点
-- 字符串转日期
SELECT TO_DATE('2024-01-15', 'YYYY-MM-DD');
SELECT TO_TIMESTAMP('2024-01-15 10:30:00', 'YYYY-MM-DD HH24:MI:SS');
-- 年龄计算
SELECT AGE(TIMESTAMP '2000-01-01');
SELECT AGE(TIMESTAMP '2024-01-01', TIMESTAMP '2000-01-01');
5.4 聚合函数
-- 基本聚合
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT status) FROM users;
SELECT SUM(total_amount) FROM orders;
SELECT AVG(total_amount) FROM orders;
SELECT MAX(total_amount), MIN(total_amount) FROM orders;
-- 数组聚合
SELECT ARRAY_AGG(username) FROM users;
SELECT ARRAY_AGG(DISTINCT status ORDER BY status) FROM users;
-- 字符串聚合
SELECT STRING_AGG(username, ', ') FROM users;
SELECT STRING_AGG(username, ', ' ORDER BY username) FROM users;
-- JSON 聚合
SELECT JSON_AGG(username) FROM users;
SELECT JSON_AGG(ROW_TO_JSON(users)) FROM users;
-- 布尔聚合
SELECT BOOL_AND(is_active) FROM users; -- 所有为 true 才返回 true
SELECT BOOL_OR(is_active) FROM users; -- 任一为 true 就返回 true
-- 统计聚合
SELECT
STDDEV(total_amount) AS std_dev,
VARIANCE(total_amount) AS variance
FROM orders;
5.5 条件函数
-- CASE WHEN
SELECT
username,
CASE status
WHEN 0 THEN '禁用'
WHEN 1 THEN '正常'
WHEN 2 THEN '待审核'
ELSE '未知'
END AS status_text
FROM users;
SELECT
username,
CASE
WHEN age < 18 THEN '未成年'
WHEN age < 60 THEN '成年'
ELSE '老年'
END AS age_group
FROM users;
-- COALESCE（返回第一个非 NULL 值）
SELECT COALESCE(nickname, username, 'Anonymous') FROM users;
-- NULLIF（相等返回 NULL）
SELECT NULLIF(status, 0) FROM users;
-- GREATEST / LEAST
SELECT GREATEST(1, 2, 3); -- 3
SELECT LEAST(1, 2, 3); -- 1
六、客户端工具
6.1 图形化工具
| 工具 | 类型 | 特点 |
|---|---|---|
| pgAdmin 4 | 官方工具 | 功能全面，免费 |
| DBeaver | 通用工具 | 支持多种数据库，免费 |
| DataGrip | JetBrains | 功能强大，付费 |
| Navicat | 商业工具 | 界面美观，付费 |
| TablePlus | 轻量级 | 界面简洁，部分付费 |
| Postico | Mac 专用 | 简洁易用，部分付费 |
6.2 编程语言连接
Java (JDBC)
import java.sql.*;
public class PostgreSQLExample {
public static void main(String[] args) {
String url = "jdbc:postgresql://localhost:5432/mydb";
String user = "postgres";
String password = "password";
try (Connection conn = DriverManager.getConnection(url, user, password)) {
// 查询
PreparedStatement pstmt = conn.prepareStatement(
"SELECT * FROM users WHERE status = ?"
);
pstmt.setInt(1, 1);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
System.out.println(rs.getString("username"));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
Python (psycopg2)
import psycopg2
from psycopg2 import pool
# 创建连接池
connection_pool = psycopg2.pool.SimpleConnectionPool(
minconn=1,
maxconn=10,
host="localhost",
port="5432",
database="mydb",
user="postgres",
password="password"
)
# 获取连接
conn = connection_pool.getconn()
try:
with conn.cursor() as cur:
# 查询
cur.execute("SELECT * FROM users WHERE status = %s", (1,))
rows = cur.fetchall()
for row in rows:
print(row)
# 插入
cur.execute(
"INSERT INTO users (username, email) VALUES (%s, %s) RETURNING id",
("john", "john@example.com")
)
user_id = cur.fetchone()[0]
conn.commit()
finally:
connection_pool.putconn(conn)
Node.js (pg)
const { Pool } = require("pg");
const pool = new Pool({
host: "localhost",
port: 5432,
database: "mydb",
user: "postgres",
password: "password",
max: 20,
idleTimeoutMillis: 30000,
});
// 查询
async function getUsers() {
const client = await pool.connect();
try {
const result = await client.query("SELECT * FROM users WHERE status = $1", [
1,
]);
return result.rows;
} finally {
client.release();
}
}
// 事务
async function createOrder(userId, items) {
const client = await pool.connect();
try {
await client.query("BEGIN");
const orderResult = await client.query(
"INSERT INTO orders (user_id) VALUES ($1) RETURNING id",
[userId]
);
const orderId = orderResult.rows[0].id;
for (const item of items) {
await client.query(
"INSERT INTO order_items (order_id, product_id, quantity) VALUES ($1, $2, $3)",
[orderId, item.productId, item.quantity]
);
}
await client.query("COMMIT");
return orderId;
} catch (e) {
await client.query("ROLLBACK");
throw e;
} finally {
client.release();
}
}
Go (pgx)
package main
import (
"context"
"fmt"
"log"
"github.com/jackc/pgx/v5/pgxpool"
)
func main() {
ctx := context.Background()
// 创建连接池
pool, err := pgxpool.New(ctx, "postgres://postgres:password@localhost:5432/mydb")
if err != nil {
log.Fatal(err)
}
defer pool.Close()
// 查询
rows, err := pool.Query(ctx, "SELECT id, username FROM users WHERE status = $1", 1)
if err != nil {
log.Fatal(err)
}
defer rows.Close()
for rows.Next() {
var id int
var username string
if err := rows.Scan(&id, &username); err != nil {
log.Fatal(err)
}
fmt.Printf("ID: %d, Username: %s\n", id, username)
}
}
七、最佳实践
7.1 命名规范
-- 表名：小写，下划线分隔，复数形式
users, order_items, product_categories
-- 列名：小写，下划线分隔
user_id, created_at, is_active
-- 主键：id 或 表名_id
id, user_id
-- 外键：关联表名_id
user_id, product_id
-- 索引：idx_表名_列名
idx_users_email, idx_orders_user_id_created_at
-- 唯一索引：uk_表名_列名
uk_users_email
-- 约束：chk_表名_描述
chk_users_age_positive
-- 序列：表名_列名_seq（自动生成）
users_id_seq
7.2 表设计原则
-- 1. 必须有主键
CREATE TABLE users (
id SERIAL PRIMARY KEY, -- 或 BIGSERIAL
...
);
-- 2. 使用合适的数据类型
-- 金额用 NUMERIC
total_amount NUMERIC(12, 2)
-- 状态用 SMALLINT
status SMALLINT DEFAULT 1
-- 时间用 TIMESTAMPTZ
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
-- 3. 添加注释
COMMENT ON TABLE users IS '用户表';
COMMENT ON COLUMN users.status IS '状态：0-禁用，1-启用';
-- 4. 设置合理的默认值
is_active BOOLEAN DEFAULT true
status SMALLINT DEFAULT 1
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
-- 5. 适当使用约束
email VARCHAR(100) UNIQUE NOT NULL
age SMALLINT CHECK (age >= 0 AND age <= 150)
7.3 查询优化建议
-- 1. 只查询需要的列
SELECT id, username FROM users; -- 好
SELECT * FROM users; -- 避免
-- 2. 使用 LIMIT 限制结果集
SELECT * FROM users ORDER BY id LIMIT 100;
-- 3. 避免 SELECT DISTINCT，考虑使用 GROUP BY 或 EXISTS
-- 4. 使用参数化查询，避免 SQL 注入
-- 好
PREPARE stmt AS SELECT * FROM users WHERE id = $1;
EXECUTE stmt(1);
-- 5. 批量操作使用事务
BEGIN;
INSERT INTO users ...;
INSERT INTO users ...;
COMMIT;
-- 6. 使用 EXPLAIN ANALYZE 分析查询
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';