PostgreSQL 常用语法速查手册

PostgreSQL 是一款功能强大的开源关系型数据库，因其可靠性、扩展性以及对 SQL 标准的良好支持而广受欢迎。本文整理了日常开发中最常用的 PostgreSQL 语法，帮助快速上手或随时查阅。

---

一、数据库操作

-- 创建数据库
CREATE DATABASE database_name;

-- 删除数据库
DROP DATABASE database_name;

-- 切换/连接数据库
\c database_name;   -- psql 命令行中使用

---

二、表操作

创建表

CREATE TABLE users (
    id SERIAL PRIMARY KEY,           -- 自增整数，PostgreSQL 专用
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT NOW()
);

SERIAL 是 PostgreSQL 特有的类型，实际创建了一个整数列和一个自增序列。也可使用 BIGSERIAL 或标准 SQL 的 GENERATED AS IDENTITY。

修改表

-- 添加列
ALTER TABLE users ADD COLUMN age INT;

-- 删除列
ALTER TABLE users DROP COLUMN age;

-- 修改列数据类型
ALTER TABLE users ALTER COLUMN age TYPE SMALLINT;

-- 重命名列
ALTER TABLE users RENAME COLUMN age TO user_age;

-- 添加约束
ALTER TABLE users ADD CONSTRAINT age_check CHECK (user_age >= 0);

删除表

DROP TABLE IF EXISTS users CASCADE;  -- CASCADE 会删除依赖对象（如外键、视图）

---

三、数据增删改（CRUD）

插入数据

-- 标准插入
INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com');

-- 批量插入
INSERT INTO users (username, email) VALUES 
    ('bob', 'bob@example.com'),
    ('carol', 'carol@example.com');

-- 带返回值的插入（常用）
INSERT INTO users (username, email) VALUES ('dave', 'dave@example.com')
RETURNING id, created_at;

查询数据

-- 基础查询
SELECT * FROM users;
SELECT id, username FROM users WHERE id = 1;

-- 条件查询
SELECT * FROM users WHERE username LIKE 'a%';      -- 通配符
SELECT * FROM users WHERE email IN ('a@x.com', 'b@x.com');
SELECT * FROM users WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

-- 排序与分页
SELECT * FROM users ORDER BY created_at DESC LIMIT 10 OFFSET 20;

PostgreSQL 也支持标准 SQL 的 FETCH FIRST 10 ROWS ONLY 语法。

更新数据

UPDATE users SET age = 30 WHERE username = 'alice';
UPDATE users SET last_login = NOW() WHERE id = 1 RETURNING *;

删除数据

DELETE FROM users WHERE id = 10;
DELETE FROM users WHERE created_at < '2023-01-01' RETURNING id;

---

四、常用查询与函数

聚合函数

SELECT 
    COUNT(*) AS total_users,
    AVG(age) AS avg_age,
    MAX(created_at) AS latest
FROM users;

分组与过滤

SELECT age, COUNT(*) 
FROM users 
GROUP BY age 
HAVING COUNT(*) > 1;   -- HAVING 用于分组后的条件过滤

字符串函数

SELECT 
    CONCAT(first_name, ' ', last_name) AS full_name,
    UPPER(email),
    LENGTH(username),
    SUBSTRING(email FROM '@')  -- 提取 @ 后的部分
FROM users;

日期时间函数

SELECT 
    NOW(),
    CURRENT_DATE,
    EXTRACT(YEAR FROM created_at) AS year,
    DATE_TRUNC('month', created_at) AS month_start
FROM users;

条件表达式

SELECT 
    username,
    CASE 
        WHEN age < 18 THEN 'minor'
        WHEN age < 65 THEN 'adult'
        ELSE 'senior'
    END AS age_group
FROM users;

-- 更简洁的 NULLIF 和 COALESCE
SELECT COALESCE(phone, '无电话') AS contact_phone FROM users;
SELECT NULLIF(score, 0) FROM results;  -- 如果 score=0 则返回 NULL

---

五、表连接（JOIN）

-- 内连接
SELECT u.username, o.order_id 
FROM users u 
INNER JOIN orders o ON u.id = o.user_id;

-- 左外连接（保留左表所有行）
SELECT u.username, o.order_id 
FROM users u 
LEFT JOIN orders o ON u.id = o.user_id;

-- 右外连接 / 全外连接类似
-- 自连接（同一张表连接）
SELECT a.username AS employee, b.username AS manager
FROM users a
LEFT JOIN users b ON a.manager_id = b.id;

---

六、索引

-- 创建 B-tree 索引（默认）
CREATE INDEX idx_users_email ON users (email);

-- 唯一索引
CREATE UNIQUE INDEX idx_users_username ON users (username);

-- 复合索引
CREATE INDEX idx_users_age_created ON users (age, created_at);

-- 部分索引（仅对满足条件的行建立索引）
CREATE INDEX idx_active_users ON users (last_login) WHERE is_active = true;

-- 删除索引
DROP INDEX idx_users_email;

PostgreSQL 还支持哈希索引、GIN（全文搜索）、GiST（地理数据）等多种索引类型。

---

七、视图

-- 创建普通视图
CREATE VIEW active_users AS
SELECT id, username, email 
FROM users 
WHERE is_active = true;

-- 创建物化视图（会实际存储数据，可刷新）
CREATE MATERIALIZED VIEW user_summary AS
SELECT DATE(created_at) AS day, COUNT(*) AS new_users
FROM users
GROUP BY day;

-- 刷新物化视图
REFRESH MATERIALIZED VIEW user_summary;

---

八、事务

BEGIN;                          -- 开始事务
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
    -- 检查无误后
COMMIT;                         -- 提交
-- 或 ROLLBACK; 回滚

-- 设置保存点
BEGIN;
    INSERT INTO logs (msg) VALUES ('开始处理');
    SAVEPOINT before_critical;
    DELETE FROM sensitive WHERE id = 999;
    -- 发现错误，回滚到保存点
    ROLLBACK TO SAVEPOINT before_critical;
COMMIT;

---

九、常用数据类型速查

类型	说明	示例
INT / SMALLINT / BIGINT	整数	age INT
SERIAL / BIGSERIAL	自增整数	id SERIAL PRIMARY KEY
NUMERIC(p,s)	精确小数	price NUMERIC(10,2)
VARCHAR(n)	可变长度字符串	name VARCHAR(100)
TEXT	无长度限制字符串	content TEXT
BOOLEAN	布尔值	is_active BOOLEAN
DATE / TIME / TIMESTAMP	日期时间	created_at TIMESTAMP
JSON / JSONB	JSON 数据	metadata JSONB
UUID	通用唯一标识符	id UUID DEFAULT gen_random_uuid()
ARRAY	数组	tags TEXT[]

---

十、实用小技巧

1. 查看执行计划

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE email = 'x@y.com';

2. UPSERT（有则更新，无则插入）

INSERT INTO users (id, username, email) 
VALUES (1, 'alice', 'new@mail.com')
ON CONFLICT (id) DO UPDATE 
SET username = EXCLUDED.username, email = EXCLUDED.email;

3. 递归查询（WITH RECURSIVE）

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
    JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree;

4. 随机取一行

SELECT * FROM users ORDER BY RANDOM() LIMIT 1;

5. 跨数据库查询（使用 dblink 或 foreign data wrapper）

-- 需先安装 dblink 扩展
CREATE EXTENSION dblink;
SELECT * FROM dblink('dbname=otherdb', 'SELECT id, name FROM products') 
AS t(id INT, name TEXT);

---

总结

以上涵盖了 PostgreSQL 日常开发中最常用的语法。PostgreSQL 功能远不止这些，它还支持窗口函数、全文搜索、地理空间数据（PostGIS）、存储过程（PL/pgSQL）等高级特性。

建议将本文作为速查手册使用。在实际开发中，多利用 \? 和 \h 在 psql 命令行中获取即时帮助，遇到具体问题查阅官方文档即可。