PostgreSQL 语法
PostgreSQL 语法全解析（SQL 标准 + PG 特色）
PostgreSQL 完全兼容 ANSI SQL 标准，同时提供大量 高级特性（JSON、数组、窗口函数、CTE、扩展等）。
本教程从 基础到高级，配 示例 + 解释 + 最佳实践。
一、基础语法（CRUD）
1. 创建数据库
CREATE DATABASE mydb
WITH OWNER = postgres
ENCODING = 'UTF8'
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
TEMPLATE = template0;2. 创建表
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY, -- 自增主键
username VARCHAR(50) UNIQUE NOT NULL, -- 唯一非空
email CITEXT, -- 不区分大小写的文本（需扩展）
age SMALLINT CHECK (age >= 0), -- 约束：年龄 ≥ 0
tags TEXT[], -- 数组类型
profile JSONB, -- JSONB（推荐）
is_active BOOLEAN DEFAULT true, -- 布尔默认 true
created_at TIMESTAMPTZ DEFAULT now() -- 时间戳带时区
);推荐类型：
SERIAL/BIGSERIAL：自增 IDJSONB>JSON：支持索引、操作符CITEXT：忽略大小写（需CREATE EXTENSION citext;）
二、数据操作（DML）
1. 插入（INSERT）
-- 单行
INSERT INTO users (username, email, age, tags, profile)
VALUES ('alice', 'Alice@example.com', 25, '{"dev","admin"}',
'{"name": "Alice", "level": 5}');
-- 多行
INSERT INTO users (username, email, age)
VALUES
('bob', 'bob@ex.com', 30),
('carol', 'Carol@ex.com', 28);
-- 返回插入数据
INSERT INTO users (username) VALUES ('dave') RETURNING id, username;2. 查询（SELECT）
-- 基础
SELECT * FROM users WHERE age > 25 ORDER BY created_at DESC LIMIT 10;
-- 条件：数组、JSON、正则
SELECT * FROM users
WHERE tags @> '{dev}'::TEXT[] -- 包含 "dev"
AND profile ->> 'level' = '5' -- JSON 字段等于 5
AND username ~* '^a.*'; -- 正则：以 a 开头（忽略大小写）
-- 别名 + 计算列
SELECT username,
age + 1 AS next_year_age,
UPPER(username) AS shout_name
FROM users;3. 更新（UPDATE）
UPDATE users
SET profile = profile || '{"last_login": "2025-11-03"}'::jsonb,
age = age + 1
WHERE username = 'alice'
RETURNING *;4. 删除（DELETE）
DELETE FROM users WHERE is_active = false RETURNING id;三、高级查询语法
1. 公共表表达式（CTE）+ 递归
-- 查找用户及其推荐人（树形结构）
WITH RECURSIVE user_tree AS (
SELECT id, username, recommended_by
FROM users WHERE username = 'alice'
UNION ALL
SELECT u.id, u.username, u.recommended_by
FROM users u
JOIN user_tree ut ON u.recommended_by = ut.id
)
SELECT * FROM user_tree;2. 窗口函数（Window Functions）
-- 排名、累计、移动平均
SELECT
username,
age,
ROW_NUMBER() OVER (PARTITION BY age ORDER BY created_at) AS row_num,
SUM(age) OVER (PARTITION BY is_active) AS sum_age_in_group,
AVG(age)::DECIMAL(5,2) OVER (ORDER BY created_at ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM users;3. 分页（Keyset Pagination 推荐）
-- 传统 OFFSET（慢）
SELECT * FROM users ORDER BY id DESC LIMIT 10 OFFSET 1000 10;
-- Keyset（高效）
SELECT * FROM users
WHERE id < 12345
ORDER BY id DESC
LIMIT 10;四、JSON / JSONB 高级操作
-- 插入
INSERT INTO users (username, profile) VALUES ('eve', '{"settings": {"theme": "dark"}}');
-- 查询字段
SELECT username, profile->>'theme' AS theme FROM users;
-- 嵌套路径
SELECT profile#>('{settings,theme}') FROM users;
-- 更新字段
UPDATE users SET profile = jsonb_set(profile, '{settings,theme}', '"light"'::jsonb)
WHERE username = 'eve';
-- 索引加速
CREATE INDEX idx_users_profile ON users USING GIN (profile);五、数组操作
-- 查询包含某个标签
SELECT * FROM users WHERE tags @> ARRAY['dev', 'admin'];
-- 添加元素
UPDATE users SET tags = tags || 'newtag' WHERE id = 1;
-- 删除元素
UPDATE users SET tags = array_remove(tags, 'oldtag') WHERE id = 1;
-- 索引
CREATE INDEX idx_users_tags ON users USING GIN (tags);六、事务与锁
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
-- 检查
DO $$
BEGIN
IF (SELECT balance FROM accounts WHERE user_id = 1) < 0 THEN
RAISE EXCEPTION '余额不足';
END IF;
END $$;
COMMIT; -- 或 ROLLBACK;七、存储过程与函数（PL/pgSQL）
CREATE OR REPLACE FUNCTION add_user(
p_username TEXT,
p_email TEXT
) RETURNS BIGINT AS $$
DECLARE
new_id BIGINT;
BEGIN
INSERT INTO users (username, email)
VALUES (p_username, p_email)
RETURNING id INTO new_id;
RETURN new_id;
END;
$$ LANGUAGE plpgsql;
-- 调用
SELECT add_user('frank', 'frank@example.com');八、视图与物化视图
-- 普通视图
CREATE VIEW active_users AS
SELECT id, username, email FROM users WHERE is_active = true;
-- 物化视图（缓存结果）
CREATE MATERIALIZED VIEW user_stats AS
SELECT age, COUNT(*) as cnt FROM users GROUP BY age;
-- 刷新
REFRESH MATERIALIZED VIEW user_stats;九、索引类型
| 类型 | 用途 | 示例 |
|---|---|---|
B树 | 默认，=, <, > | CREATE INDEX ON users(username); |
GIN | JSONB、数组、全文搜索 | CREATE INDEX ON users USING GIN(profile); |
GiST | 几何、全文搜索 | PostGIS 使用 |
BRIN | 大表按块索引 | CREATE INDEX ON logs(created_at) USING BRIN; |
表达式索引 | 函数结果 | CREATE INDEX ON users(LOWER(username)); |
十、模式（Schema）管理
CREATE SCHEMA analytics;
CREATE TABLE analytics.user_log (
user_id BIGINT,
action TEXT,
ts TIMESTAMPTZ DEFAULT now()
);
-- 使用
SELECT * FROM analytics.user_log;十一、扩展（Extensions）
-- 启用扩展
CREATE EXTENSION IF NOT EXISTS citext; -- 不区分大小写文本
CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; -- UUID 生成
CREATE EXTENSION IF NOT EXISTS postgis; -- 地理信息
CREATE EXTENSION IF NOT EXISTS timescaledb; -- 时序数据库
-- 使用 UUID
SELECT uuid_generate_v4();十二、常用系统函数
| 函数 | 用途 |
|---|---|
now() / CURRENT_TIMESTAMP | 当前时间 |
COALESCE(col, 'default') | 空值处理 |
NULLIF(val1, val2) | 防除零 |
generate_series(1,10) | 生成序列 |
pg_size_pretty(pg_relation_size('table')) | 表大小 |
十三、性能优化 SQL 技巧
-- 1. EXPLAIN ANALYZE 查看执行计划
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE username = 'alice';
-- 2. 避免 SELECT *
SELECT id, username FROM users ...
-- 3. 使用 EXISTS 而非 IN（大数据量）
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
-- 4. 批量插入
INSERT INTO logs (...) VALUES (...), (...), (...);十四、备份与恢复语法
-- 逻辑备份（SQL）
pg_dump -U postgres -d mydb > mydb.sql
-- 自定义格式备份
pg_dump -Fc -U postgres mydb > mydb.backup
-- 恢复
pg_restore -U postgres -d mydb mydb.backup十五、快速参考表
| 命令 | 语法 |
|---|---|
| 创建用户 | CREATE USER name WITH PASSWORD 'xxx'; |
| 授权 | GRANT ALL ON DATABASE db TO user; |
| 查看表结构 | \d tablename（psql） |
| 查看索引 | \di |
| 终止会话 | SELECT pg_terminate_backend(pid); |
| 清理死元组 | VACUUM ANALYZE tablename; |
学习资源
- 官方文档：https://www.postgresql.org/docs/
- 在线练习：https://www.postgresqltutorial.com
- SQL 格式化：https://sqlformat.org
小结：PostgreSQL 语法核心
-- 记住这 6 个关键词，你就能写 90% 的 SQL
SELECT ... FROM ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...
LIMIT ...现在就动手！
打开 psql 或 pgAdmin，创建一个表，插入 3 条数据，用 JSONB 存用户偏好，再用 CTE + 窗口函数 分析年龄分布。
需要我为你生成：
- 100 条测试数据 SQL？回复
测试数据 - 复杂查询实战案例？回复
查询案例 - 性能调优 checklist？回复
调优
随时告诉我！