主题
第 2 章 psql 与基础 SQL
学习目标：能熟练使用 psql 命令行（包括 30+ 个元命令）；理解
database / schema / table三层结构和search_path的工作原理；能写出建库、建表、增删改查的完整一条龙；用一个「图书管理系统」练手，掌握 PG 特色的INSERT ... RETURNING、ILIKE、FETCH FIRST n ROWS ONLY。
2.1 psql：PG 的瑞士军刀
2.1.1 什么是 psql？
psql 是 PostgreSQL 官方自带的命令行客户端。你可以把它理解成 PG 世界的 mysql 命令，但是功能强一个数量级 —— 它既是 SQL 终端，也是脚本执行器、库管理器、交互式调试器。
┌──────────────┐ TCP ┌─────────────────┐
│ psql CLI │ ◀──▶ │ PostgreSQL │
│ （客户端） │ │ Backend 进程 │
└──────────────┘ └─────────────────┘
│ │
▼ ▼
.psqlrc 配置 系统目录 pg_class
历史命令、超时 pg_namespace 等2.1.2 连接：5 个常用参数
bash
# 标准写法
psql -h 127.0.0.1 -p 5432 -U postgres -d learn_pg
# 等价（带 -W 强制提示输入密码）
psql -h 127.0.0.1 -p 5432 -U postgres -d learn_pg -W
# 用连接串（DSN）
psql "host=127.0.0.1 port=5432 dbname=learn_pg user=postgres"
# 用 URL（方便从配置文件读）
psql "postgresql://postgres@127.0.0.1:5432/learn_pg"| 参数 | 含义 | 默认值 |
|---|---|---|
-h | host | UNIX socket（Linux 通常 /var/run/postgresql） |
-p | port | 5432 |
-U | user | 当前 OS 用户名 |
-d | database | 与 -U 同名 |
-W | 强制密码提示 | （否则用 ~/.pgpass 或环境变量） |
📌 与 MySQL 的区别：MySQL 默认用户是
root，PG 默认用户是postgres；MySQL 用-p强制输密码，PG 是-W（小心-p在 PG 里是端口）。
2.1.3 进入后的提示符
learn_pg=# ← 当前数据库名 + (#超级用户 / >普通用户)
learn_pg-# ← 上一行没结束（缺分号、引号没闭合）
learn_pg(# ← 在括号里2.1.4 第一个会话：从 PING 到再见
sql
$ psql -h 127.0.0.1 -U postgres -d learn_pg
psql (17.0)
Type "help" for help.
learn_pg=# SELECT 1 + 1 AS answer;
answer
--------
2
(1 row)
learn_pg=# SELECT now(), current_database(), version();
now | current_database | version
-------------------------------+------------------+------------------------------------------------------
2026-04-17 11:22:33.456789+08 | learn_pg | PostgreSQL 17.0 on x86_64-pc-linux-gnu, ...
(1 row)
learn_pg=# \q\q 是 psql 的「元命令」（meta command），下面会大讲特讲。
2.2 元命令大全（重点中的重点）
2.2.1 元命令是什么？
psql 中以反斜杠 \ 开头的命令叫元命令。它们不会发到服务端，而是 psql 客户端自己处理（查 catalog 后渲染表格）。元命令不需要分号结尾。
┌────────────┐ ┌────────────────┐
\d users → │ psql 解析 │ → 内部转换 → SELECT ... FROM pg_class ... → │ Backend 执行 │
└────────────┘ └────────────────┘
↓
psql 把结果排版成 \d 的好看格式想看
\d内部到底跑了什么 SQL？加-E启动：psql -E -h 127.0.0.1 -U postgres -d learn_pg，然后每次执行元命令都会先打印对应的 SQL —— 这是学 PG 内部表的最快方法。
2.2.2 必背的 30+ 元命令
① 帮助类
| 命令 | 作用 |
|---|---|
\? | 列出所有元命令（最常用！） |
\h | 列出所有 SQL 语法 |
\h SELECT | 查 SELECT 的完整语法 |
\? variables | 查 psql 变量帮助 |
② 库 / 用户 / 模式
| 命令 | 作用 | 等价 SQL |
|---|---|---|
\l 或 \list | 列出所有数据库 | SELECT datname FROM pg_database; |
\c db_name | 切换数据库 | （断开 + 重连） |
\du | 列出所有角色（用户） | SELECT * FROM pg_roles; |
\dn | 列出所有 schema | SELECT * FROM pg_namespace; |
③ 对象类（最常用）
| 命令 | 作用 |
|---|---|
\d | 列出当前 schema 下所有对象（表/视图/序列） |
\dt | 只列表 |
\dt+ | 列表 + 行数估算 + 大小 |
\dt schema.* | 列指定 schema 下的表 |
\d table_name | 看表结构（列、索引、约束、触发器） |
\d+ table_name | 看表结构 + 列存储参数 + 描述 |
\dv | 列出视图 |
\df | 列出函数 |
\df+ funcname | 看函数定义 |
\di | 列出索引 |
\ds | 列出序列 |
\dT | 列出自定义类型 |
\dx | 列出已安装扩展 |
④ 输入 / 输出
| 命令 | 作用 |
|---|---|
\i file.sql | 执行外部 SQL 文件 |
\o /tmp/out.txt | 把后续查询结果输出到文件 |
\o | 取消输出重定向 |
\copy users TO 'users.csv' CSV HEADER | 客户端侧导出 CSV |
\copy users FROM 'users.csv' CSV HEADER | 客户端侧导入 CSV |
\e | 用 $EDITOR 打开编辑器写 SQL |
\! | 执行 shell 命令（如 \! ls） |
⑤ 显示控制
| 命令 | 作用 |
|---|---|
\x | 切换扩展显示模式（每行一列，长结果必备） |
\x auto | 结果太宽时自动开扩展显示 |
\timing | 切换是否显示每个 SQL 的耗时（调优必开） |
\pset format aligned | 默认对齐输出 |
\pset format csv | CSV 输出 |
\pset format json | JSON 输出（PG 12+） |
\pset null '<NULL>' | NULL 显示成 <NULL> 而不是空白 |
\pset border 2 | 加粗边框 |
⑥ 历史 / 调试 / 其他
| 命令 | 作用 |
|---|---|
\q | 退出 |
\g | 重新执行上一条 SQL |
\s | 显示历史命令 |
\set ECHO_HIDDEN on | 显示元命令背后跑的 SQL（同 -E） |
\conninfo | 当前连接信息 |
\password user | 修改密码 |
\watch 2 | 每 2 秒重复执行上条 SQL（监控类查询神器） |
2.2.3 实战速查：5 个最常用组合
sql
-- ① 看完整对象列表
learn_pg=# \dt+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+------------+-------+----------+-------------+---------------+---------+-------------
public | ch2_authors | table | postgres | permanent | heap | 16 kB |
public | ch2_books | table | postgres | permanent | heap | 32 kB |
public | ch2_categories | table | postgres | permanent | heap | 16 kB |
(3 rows)
-- ② 看某张表结构
learn_pg=# \d ch2_books
Table "public.ch2_books"
Column | Type | Collation | Nullable | Default
--------------+-----------------------------+-----------+----------+-----------------------------------
id | bigint | | not null | nextval('ch2_books_id_seq'::regclass)
title | text | | not null |
author_id | bigint | | |
category_id | bigint | | |
isbn | varchar(20) | | |
published_at | date | | |
stock | integer | | | 0
price | numeric(10,2) | | |
created_at | timestamp with time zone | | | now()
Indexes:
"ch2_books_pkey" PRIMARY KEY, btree (id)
"ch2_books_isbn_key" UNIQUE CONSTRAINT, btree (isbn)
"idx_ch2_books_title" btree (title)
Foreign-key constraints:
"ch2_books_author_id_fkey" FOREIGN KEY (author_id) REFERENCES ch2_authors(id)
"ch2_books_category_id_fkey" FOREIGN KEY (category_id) REFERENCES ch2_categories(id)
-- ③ 计时 + 扩展显示，配合长查询
learn_pg=# \timing on
Timing is on.
learn_pg=# \x auto
Expanded display is used automatically.
learn_pg=# SELECT * FROM ch2_books LIMIT 1;
-[ RECORD 1 ]+--------------------------
id | 1
title | 三体
author_id | 1
category_id | 1
isbn | 9787229030933
published_at | 2008-01-01
stock | 12
price | 38.50
created_at | 2026-04-17 10:00:00+08
Time: 1.234 ms
-- ④ 把元命令背后的 SQL 显出来
learn_pg=# \set ECHO_HIDDEN on
learn_pg=# \dn
********* QUERY **********
SELECT n.nspname AS "Name", pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
ORDER BY 1;
**************************
List of schemas
Name | Owner
----------+----------
public | pg_database_owner
(1 row)
-- ⑤ 监控：每 2 秒看一次活跃连接
learn_pg=# SELECT pid, state, query FROM pg_stat_activity WHERE state != 'idle';
learn_pg=# \watch 2📌 与 MySQL 的区别：MySQL 客户端的
SHOW TABLES;DESC users;STATUS;在 PG 里都换成了反斜杠元命令。背 5 个：\l \dt \d \du \dn，基本日常够用。
2.3 三层结构：Database / Schema / Table
2.3.1 一个图书馆的类比
╔══════════════════════════════════════════════════════════════╗
║ PostgreSQL 实例 ║
║ （= 一座大学城） ║
║ ║
║ ┌───────────┐ ┌───────────┐ ┌───────────────┐ ║
║ │ database │ │ database │ │ database │ ║
║ │ learn_pg │ │ ecommerce │ │ analytics │ ← 数据库 ║
║ │ (= 图书馆 A)│ │(= 图书馆 B)│ │(= 图书馆 C) │ (= 图书馆) ║
║ └─────┬─────┘ └───────────┘ └───────────────┘ ║
║ │ ║
║ │ database 之间不能直接 JOIN（要用 FDW） ║
║ ▼ ║
║ ┌──────────────────────────────────────────┐ ║
║ │ database learn_pg │ ║
║ │ ┌────────────┐ ┌──────────┐ ┌────────┐ │ ║
║ │ │ schema │ │ schema │ │schema │ │ ← 模式 │
║ │ │ public │ │ stats │ │ archive│ │ (= 图书馆楼层) │
║ │ └─────┬──────┘ └──────────┘ └────────┘ │ ║
║ │ │ │ ║
║ │ ▼ │ ║
║ │ ┌──────────────────────────────────┐ │ ║
║ │ │ schema public │ │ ║
║ │ │ ┌─────┐ ┌──────┐ ┌──────────┐ │ │ ║
║ │ │ │books│ │authors│ │categories│ │ ← 表（= 书架） │
║ │ │ └─────┘ └──────┘ └──────────┘ │ │ ║
║ │ └──────────────────────────────────┘ │ ║
║ └──────────────────────────────────────────┘ ║
╚══════════════════════════════════════════════════════════════╝- database（图书馆）：物理上隔离，连接时只能选一个，库之间默认不能直接 JOIN。
- schema（楼层 / 命名空间）：同一个 database 内的逻辑分组，可以跨 schema JOIN。
- table（书架）：实际存数据的地方。
📌 与 MySQL 的对比：
- MySQL 没有真正的 schema：
CREATE SCHEMA xxx在 MySQL 里就是CREATE DATABASE xxx的同义词。- PG 的 schema 是「真·命名空间」：可以建
app1.users和app2.users两个不冲突的同名表，多租户 SaaS 项目常用 schema 做「软隔离」。
2.3.2 search_path：表名查找的「PATH 环境变量」
很多人写 SQL 不带 schema 前缀（SELECT * FROM ch2_books），那 PG 怎么知道找哪个 schema 的 books 表？
答案是 search_path。它就像 Linux 的 $PATH —— 按顺序找第一个匹配的：
sql
learn_pg=# SHOW search_path;
search_path
-----------------
"$user", public
(1 row)
-- 含义：先找名字等于当前用户的 schema（如果存在），找不到再找 public实操：
sql
learn_pg=# CREATE SCHEMA stats;
CREATE SCHEMA
learn_pg=# CREATE TABLE stats.daily(d DATE, pv BIGINT);
CREATE TABLE
learn_pg=# SELECT * FROM daily;
ERROR: relation "daily" does not exist
LINE 1: SELECT * FROM daily;
^
-- 怎么查 stats.daily？两个办法：
-- 办法 ①：写全名
learn_pg=# SELECT * FROM stats.daily;
-- 办法 ②：把 stats 加进 search_path
learn_pg=# SET search_path = stats, public;
SET
learn_pg=# SELECT * FROM daily; -- 现在能找到了2.3.3 创建你的第一个数据库 + schema
sql
-- 在 postgres 默认库里执行（注意：CREATE DATABASE 不能在事务里用）
postgres=# CREATE DATABASE learn_pg
WITH OWNER = postgres
ENCODING = 'UTF8'
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
TEMPLATE = template0;
CREATE DATABASE
-- 切到新库
postgres=# \c learn_pg
You are now connected to database "learn_pg" as user "postgres".
-- 在 learn_pg 里建一个新 schema
learn_pg=# CREATE SCHEMA library AUTHORIZATION postgres;
CREATE SCHEMA
learn_pg=# \dn
List of schemas
Name | Owner
-----------+-----------
library | postgres
public | pg_database_owner
(2 rows)📌 与 MySQL 的区别：MySQL 的
CREATE DATABASE通常自动指定字符集（utf8mb4）；PG 的字符集在initdb时已经确定整个 cluster，单库通常都是UTF8，无需操心。
2.4 DDL：建表与改表
2.4.1 CREATE TABLE 标准写法
我们用一个图书管理系统贯穿 demo（同一份建表语句也放在 02_psql_basic/init.sql）：
sql
-- 类别表
CREATE TABLE ch2_categories (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, -- SQL 标准的自增
name VARCHAR(64) NOT NULL UNIQUE,
description TEXT
);
-- 作者表
CREATE TABLE ch2_authors (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR(64) NOT NULL,
country VARCHAR(32),
born_year INTEGER CHECK (born_year > 0)
);
-- 图书表（带外键）
CREATE TABLE ch2_books (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title TEXT NOT NULL,
author_id BIGINT REFERENCES ch2_authors(id),
category_id BIGINT REFERENCES ch2_categories(id),
isbn VARCHAR(20) UNIQUE,
published_at DATE,
stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
price NUMERIC(10, 2) CHECK (price >= 0),
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- 加一个普通索引方便按书名搜
CREATE INDEX idx_ch2_books_title ON ch2_books(title);几个 PG 特色点：
GENERATED ALWAYS AS IDENTITY：SQL 标准的自增写法，优于SERIAL。从 PG 10 起官方推荐用这种，因为SERIAL的本质是隐式建一个序列，权限管理麻烦。TIMESTAMPTZ：带时区时间戳。强烈推荐用这个而不是TIMESTAMP，能避免时区切换 bug。NUMERIC(10,2)：精确小数，对应金额场景，比FLOAT安全。CHECK：行级检查约束，可以写任意布尔表达式。
📌 与 MySQL 的对比：
MySQL PostgreSQL INT AUTO_INCREMENTBIGINT GENERATED ALWAYS AS IDENTITYVARCHAR(255)没限制TEXT推荐（VARCHAR 只用于长度真有限制时）DECIMAL(10,2)NUMERIC(10,2)DATETIMETIMESTAMPTZCHECK5.7 之前忽略CHECK严格执行
2.4.2 ALTER TABLE：修改表
sql
-- 加一列
ALTER TABLE ch2_books ADD COLUMN description TEXT;
-- 改类型
ALTER TABLE ch2_books ALTER COLUMN price TYPE NUMERIC(12, 2);
-- 加约束
ALTER TABLE ch2_books ADD CONSTRAINT ch2_books_price_positive CHECK (price > 0);
-- 重命名
ALTER TABLE ch2_books RENAME COLUMN description TO summary;
ALTER TABLE ch2_books RENAME TO ch2_book;
ALTER TABLE ch2_book RENAME TO ch2_books; -- 改回去
-- 删一列
ALTER TABLE ch2_books DROP COLUMN summary;🔥 PG 的杀手锏：DDL 是 事务性的，可以放进
BEGIN; ... ROLLBACK;：sqlBEGIN; ALTER TABLE ch2_books ADD COLUMN bad_col TEXT; -- 发现搞错了 ROLLBACK; -- bad_col 没有留下！MySQL 做不到。
2.4.3 DROP
sql
DROP TABLE IF EXISTS ch2_books CASCADE; -- CASCADE：级联删依赖（如外键、视图）
DROP SCHEMA IF EXISTS stats CASCADE;
DROP DATABASE IF EXISTS scratch;2.5 DML：增删改查
2.5.1 INSERT —— 含 PG 特色 RETURNING
sql
-- 单条插入
INSERT INTO ch2_authors (name, country, born_year)
VALUES ('刘慈欣', '中国', 1963);
-- 多条
INSERT INTO ch2_authors (name, country, born_year) VALUES
('村上春树', '日本', 1949),
('George Orwell', '英国', 1903),
('J.K. Rowling', '英国', 1965);
-- 🌟 PG 特色：INSERT ... RETURNING 把刚生成的字段拿回来
INSERT INTO ch2_categories (name, description)
VALUES ('科幻', '面向未来的想象力')
RETURNING id, name, description;
-- id | name | description
-- ----+--------+-------------------
-- 1 | 科幻 | 面向未来的想象力
-- (1 row)RETURNING 是 PG / Oracle 都有但 MySQL 8.0 之前没有的能力。它能省掉「插入后再 SELECT 一次拿主键」的来回开销 —— 在 web 框架里特别有用。
2.5.2 UPDATE
sql
UPDATE ch2_books SET stock = stock + 5 WHERE id = 1;
-- 也能 RETURNING
UPDATE ch2_books SET price = price * 0.9
WHERE category_id = 1
RETURNING id, title, price;2.5.3 DELETE
sql
DELETE FROM ch2_books WHERE stock = 0;
DELETE FROM ch2_books WHERE id = 99 RETURNING *; -- 删完返回被删的行2.5.4 SELECT —— 基础句法
sql
SELECT id, title, price
FROM ch2_books
WHERE category_id = 1 AND stock > 0
ORDER BY published_at DESC
LIMIT 10;| 子句 | 作用 |
|---|---|
SELECT | 选哪些列 |
FROM | 从哪张表（或 JOIN 多表） |
WHERE | 行过滤 |
GROUP BY | 聚合分组（第 5 章详讲） |
HAVING | 聚合后过滤 |
ORDER BY | 排序 |
LIMIT / OFFSET | 分页 |
2.6 WHERE 子句的 8 种武器
sql
-- 1. 等值
SELECT * FROM ch2_books WHERE id = 1;
-- 2. AND / OR / NOT
SELECT * FROM ch2_books WHERE stock > 0 AND price < 50;
SELECT * FROM ch2_books WHERE category_id = 1 OR category_id = 2;
SELECT * FROM ch2_books WHERE NOT (stock = 0);
-- 3. IN
SELECT * FROM ch2_books WHERE category_id IN (1, 2, 3);
-- 4. BETWEEN ... AND ...
SELECT * FROM ch2_books WHERE price BETWEEN 20 AND 50;
SELECT * FROM ch2_books WHERE published_at BETWEEN '2020-01-01' AND '2024-12-31';
-- 5. LIKE：模式匹配（区分大小写）
SELECT * FROM ch2_books WHERE title LIKE '三%'; -- 以"三"开头
SELECT * FROM ch2_books WHERE title LIKE '%体%'; -- 含"体"
SELECT * FROM ch2_books WHERE title LIKE '_体%'; -- 任意 1 字 + "体"
-- 6. 🌟 ILIKE：PG 特色，大小写不敏感的 LIKE
SELECT * FROM ch2_books WHERE title ILIKE '%harry%';
-- 同时匹配 Harry / harry / HARRY
-- 7. IS NULL / IS NOT NULL
SELECT * FROM ch2_books WHERE category_id IS NULL;
-- 8. 正则（PG 特色，基于 POSIX）
SELECT * FROM ch2_books WHERE title ~ '^三体[0-9]?$'; -- 区分大小写
SELECT * FROM ch2_books WHERE title ~* '^三体'; -- 不区分大小写
SELECT * FROM ch2_books WHERE title !~ 'test'; -- 不匹配📌 与 MySQL 的区别：MySQL 的
LIKE默认不区分大小写（依赖 collation），PG 的LIKE严格区分，ILIKE才不区分 —— 这点最容易踩坑。
2.6.1 NULL 的真相：SQL 三值逻辑
sql
-- 千万别用 = NULL！
SELECT * FROM ch2_books WHERE category_id = NULL; -- ❌ 永远返回空
SELECT * FROM ch2_books WHERE category_id IS NULL; -- ✅ 正确
-- 因为 SQL 里 NULL 不是值，是「未知」：
-- NULL = NULL → NULL（既不是 true 也不是 false）
-- NULL = 1 → NULL
-- NULL <> NULL → NULLPG 严格遵守 SQL 标准的「三值逻辑」（true / false / NULL），所以 WHERE col = NULL 永远没结果。MySQL 在某些 mode 下会容忍这种写法，PG 不会。
2.7 ORDER BY 与分页：LIMIT vs FETCH FIRST
sql
-- 写法 ①：MySQL/PG 都支持
SELECT * FROM ch2_books
ORDER BY price DESC, id ASC
LIMIT 10 OFFSET 20;
-- 写法 ②：标准 SQL（PG 也支持，MySQL 8.0+ 支持）
SELECT * FROM ch2_books
ORDER BY price DESC, id ASC
OFFSET 20 ROWS
FETCH FIRST 10 ROWS ONLY;两点 PG 细节：
- PG 中没有
ORDER BY的查询，行的顺序是不确定的（哪怕你看着像是按插入顺序），分页务必显式ORDER BY。 OFFSET大了性能差（PG 也要扫前面 N 行），千万级数据用「主键游标」代替（WHERE id > 上次最后一个 id）。
🌟 NULL 排序：PG 默认
ASC时 NULL 在最后、DESC时 NULL 在最前。可以显式ORDER BY price DESC NULLS LAST。MySQL 行为相反。
2.8 一个完整的「图书管理系统」实操
把上面所有知识串起来。下面整段记录可以复制到 psql 里直接跑。
sql
\timing on
\x auto
-- ========== ① 准备数据（已在 init.sql 里建表） ==========
\i /data/workspace/learnNote/postgre/02_psql_basic/init.sql
-- ========== ② 看看库里有什么 ==========
learn_pg=# \dt
List of relations
Schema | Name | Type | Owner
--------+------------+-------+----------
public | ch2_authors | table | postgres
public | ch2_books | table | postgres
public | ch2_categories | table | postgres
(3 rows)
-- ========== ③ 增 ==========
learn_pg=# INSERT INTO ch2_authors(name, country, born_year)
VALUES ('陈楸帆', '中国', 1981)
RETURNING id;
id
----
6
(1 row)
-- ========== ④ 查 ==========
-- 4.1 当下还有库存的科幻类书（按价格降序）
SELECT b.id, b.title, a.name AS author, b.price, b.stock
FROM ch2_books b
JOIN ch2_authors a ON a.id = b.author_id
JOIN ch2_categories c ON c.id = b.category_id
WHERE c.name = '科幻' AND b.stock > 0
ORDER BY b.price DESC
FETCH FIRST 5 ROWS ONLY;
-- 4.2 模糊搜书名（PG 大小写不敏感）
SELECT id, title FROM ch2_books WHERE title ILIKE '%harry%';
-- 4.3 缺货的书（NULL 处理）
SELECT id, title FROM ch2_books WHERE stock = 0 OR stock IS NULL;
-- ========== ⑤ 改 ==========
-- 全场科幻 9 折
UPDATE ch2_books
SET price = ROUND(price * 0.9, 2)
WHERE category_id = (SELECT id FROM ch2_categories WHERE name = '科幻')
RETURNING id, title, price;
-- ========== ⑥ 删 ==========
-- 把绝版且无库存的书删掉
DELETE FROM ch2_books
WHERE stock = 0 AND published_at < '2010-01-01'
RETURNING id, title;
-- ========== ⑦ 看活跃事务 ==========
SELECT pid, state, query
FROM pg_stat_activity
WHERE datname = 'learn_pg' AND state != 'idle';2.9 psql 输出格式控制
2.9.1 \x 扩展显示：长行救星
-- 普通模式：列太多撑爆屏幕
learn_pg=# SELECT * FROM ch2_books LIMIT 1;
id | title | author_id | category_id | isbn | published_at | stock | price | created_at
----+-------+-----------+-------------+---------------+--------------+-------+-------+-------------------------------
1 | 三体 | 1 | 1 | 9787229030933 | 2008-01-01 | 12 | 38.50 | 2026-04-17 10:00:00.123456+08
-- 开 \x 后，每列一行
learn_pg=# \x
Expanded display is on.
learn_pg=# SELECT * FROM ch2_books LIMIT 1;
-[ RECORD 1 ]+-------------------------------
id | 1
title | 三体
author_id | 1
category_id | 1
isbn | 9787229030933
published_at | 2008-01-01
stock | 12
price | 38.50
created_at | 2026-04-17 10:00:00.123456+082.9.2 输出 CSV / JSON
sql
-- CSV
learn_pg=# \pset format csv
learn_pg=# SELECT id, title, price FROM ch2_books LIMIT 3;
id,title,price
1,三体,34.65
2,Harry Potter,52.00
3,1984,28.00
-- JSON（PG 12+，按行的 JSON 数组）
learn_pg=# \pset format aligned -- 切回默认
learn_pg=# SELECT json_agg(t) FROM (SELECT id, title FROM ch2_books LIMIT 3) t;
json_agg
---------------------------------------------------------------------
[{"id":1,"title":"三体"},{"id":2,"title":"Harry Potter"},{"id":3,"title":"1984"}]2.9.3 \copy 导入导出（极快）
bash
# 客户端侧的 COPY，权限走 psql 用户，无需服务端文件系统权限
learn_pg=# \copy ch2_books TO '/tmp/books.csv' WITH CSV HEADER;
COPY 6
learn_pg=# \copy ch2_books FROM '/tmp/new_books.csv' WITH CSV HEADER;
COPY 100COPY 是 PG 最快的批量加载方式，比 INSERT 快 10-100 倍 —— 数据迁移、ETL 场景必用。
2.10 DCL 简介：GRANT / REVOKE（详解见第 13 章）
sql
-- 创建一个只读用户
CREATE ROLE reader LOGIN PASSWORD 'reader_pass';
-- 授读权限
GRANT CONNECT ON DATABASE learn_pg TO reader;
GRANT USAGE ON SCHEMA public TO reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reader;
-- 让以后新建的表也自动有 SELECT 权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO reader;
-- 收回
REVOKE INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM reader;📌 PG 的权限模型核心两点： ① 没有
USAGE ON SCHEMA的话，给SELECT ON TABLE是无效的； ②ALTER DEFAULT PRIVILEGES只对之后创建的对象生效。
第 13 章会展开行级安全（Row-Level Security）、pg_hba.conf、SSL 配置等。
2.11 底层一瞥：psql 元命令背后发生了什么
关键点：所有元命令都是「psql 客户端把它翻译成对系统目录（system catalog）的 SQL 查询」，然后渲染输出。所以掌握元命令本质上等于学了一遍 PG 的 catalog 模型。
2.12 本章小结
┌────────────────────────────────────────────────────────────┐
│ 本章核心要点 │
├────────────────────────────────────────────────────────────┤
│ │
│ ① psql 元命令必背 8 个： │
│ \? \l \c \dt \d <tbl> \du \dn \timing \x \i │
│ │
│ ② 三层结构：database → schema → table │
│ schema 是 PG 比 MySQL 多出来的「真·命名空间」 │
│ search_path 决定不写 schema 时找谁 │
│ │
│ ③ 建表 4 个最佳实践： │
│ • 用 GENERATED ALWAYS AS IDENTITY 而不是 SERIAL │
│ • 时间戳一律 TIMESTAMPTZ │
│ • 金额用 NUMERIC(p,s) │
│ • 主键 BIGINT，避免将来溢出 │
│ │
│ ④ INSERT/UPDATE/DELETE 都支持 RETURNING（PG 特色） │
│ │
│ ⑤ ILIKE 才是大小写不敏感（LIKE 严格区分），与 MySQL 反着来 │
│ │
│ ⑥ NULL 三值逻辑：用 IS NULL，永远别用 = NULL │
│ │
│ ⑦ 分页用 LIMIT/OFFSET 或 FETCH FIRST n ROWS ONLY， │
│ OFFSET 大时改用主键游标 │
│ │
│ ⑧ DDL 在 PG 里是事务性的，可以 ROLLBACK │
│ │
└────────────────────────────────────────────────────────────┘🎮 配套演示
用浏览器打开
./02_psql_basic/demo.html，跟着可视化动画再走一遍本章核心概念。配套代码在
./02_psql_basic/code/，每个脚本都可以独立python xxx.py运行，先跑init.sql准备数据。
2.13 面试高频题
Q1：psql 中 \d 和 SHOW TABLES 有什么区别？背后的原理是？
考察点：是否理解 psql 元命令是「客户端侧」还是「服务端侧」。
标准答案：
\d是 psql 客户端的元命令，不是 SQL。它不会发到服务端 —— psql 自己把它翻译成对系统目录（pg_class/pg_namespace/pg_attribute等）的 SQL 查询，再把结果按特定格式渲染。服务端不存在
\d这个命令：从 JDBC、psycopg、Go 的 pgx 驱动里发\d users会报语法错误。想验证：用
psql -E启动，或在 psql 里\set ECHO_HIDDEN on，每次执行元命令都会先打印对应 SQL。PG 没有 MySQL 风格的
SHOW TABLES，但有等价 SQL：sqlSELECT table_schema, table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema NOT IN ('pg_catalog','information_schema');
加分项：能列出几个常用 catalog 表 —— pg_class（所有 relation：表/视图/索引/序列）、pg_namespace（schema）、pg_attribute（列）、pg_index（索引），以及对外友好的 information_schema.*。
易错点：以为 \d 在所有客户端通用 —— 它只是 psql 的功能，DBeaver / Navicat 里查表结构是各家自己实现。
Q2：PostgreSQL 的 schema 是什么？和 MySQL 的「数据库」有什么区别？
考察点：对 PG 三层结构的理解，以及多租户场景下的方案选择。
标准答案：
- MySQL 把 schema 等同于 database：
CREATE SCHEMA x和CREATE DATABASE x是同义词。 - PG 是真正的三层结构：
database → schema → table。- 一个 PG 实例下可以有多个 database，database 之间是物理隔离，不能直接 JOIN（要用 FDW 跨库）；
- 一个 database 下可以有多个 schema，schema 是命名空间，可以无缝跨 schema JOIN 和外键引用；
- schema 下才是真正的表 / 视图 / 函数等对象。
search_path决定查表时找哪个 schema：默认是"$user", public，可以SET search_path临时改。- 典型用法：
- 多租户 SaaS：每个租户一个 schema（
tenant_001.users、tenant_002.users），共享同一份建表 DDL； - 大型项目按业务模块分 schema（
auth.* / order.* / payment.*）； - 临时实验放在自己的私有 schema 不污染 public。
- 多租户 SaaS：每个租户一个 schema（
加分项：
- 能说出 schema 隔离 vs database 隔离的取舍：schema 下事务、连接共享，性能好但隔离弱；database 隔离强但跨库查询要走 FDW（
postgres_fdw）。 - 能说出 schema 不能跨 database：所以多租户用 schema 时所有租户共用一个连接池，扩容到一定规模会改用「逻辑分库」。
易错点：把 PG 的 CREATE SCHEMA 当成 CREATE DATABASE 来用 —— PG 里这是两个完全不同的概念。
Q3：PG 的 LIKE 和 ILIKE 有什么区别？为什么 MySQL 的 LIKE 不区分大小写而 PG 区分？
考察点：对字符串匹配 + 字符集 / collation 的理解。
标准答案：
LIKE在 PG 中严格区分大小写：title LIKE 'harry%'不会匹配Harry Potter。ILIKE是 PG 特有的忽略大小写版本（I= Insensitive）。- MySQL 的
LIKE大小写敏感性取决于列的 collation：默认utf8mb4_general_ci或utf8mb4_0900_ai_ci，_ci= case insensitive，所以默认不区分；如果改成_bin则区分。 - 底层差异：PG 的字符串比较行为由数据库 collation + 列 collation 控制，但
LIKE操作符本身的实现路径是字符级二进制比较，与排序 collation 解耦；MySQL 把LIKE与 collation 紧密绑定。 - PG 还提供：
~/~*：POSIX 正则（区分 / 不区分大小写）~~/~~*：等价于LIKE/ILIKE的操作符形式- 全文检索
tsvector @@ tsquery（第 18 章详讲）
加分项：
提到为高效模糊搜索可以建
pg_trgm扩展上的 GIN/GIST 索引，让ILIKE '%xxx%'也能走索引（普通 B-Tree 索引在前缀通配符场景失效）；提到
LIKE 'abc%'（前缀匹配）能走 B-Tree 索引，但前提是列使用 C 或 POSIX collation 创建索引（text_pattern_ops），否则非英文 collation 下 LIKE 也走不了索引：sqlCREATE INDEX idx_ch2_books_title_pattern ON ch2_books(title text_pattern_ops);
易错点：从 MySQL 迁过来的同学常忘了 PG 的 LIKE 区分大小写，导致线上模糊搜全部失效。
Q4：PG 的 DDL 可以放在事务里吗？这给运维带来什么好处？
考察点：对「事务性 DDL」机制的理解 + 实际运维价值。
标准答案：
- PG 的 DDL 大部分是事务性的：
CREATE TABLE/ALTER TABLE/DROP TABLE/CREATE INDEX都可以放在BEGIN; ... ROLLBACK;里，回滚后表不会留下。 - MySQL 不支持：MySQL 的 DDL 是隐式提交的，发出
CREATE TABLE立刻提交当前事务，无法回滚。 - PG 的极少数例外：
CREATE DATABASE/DROP DATABASE：因为涉及到目录创建/删除，无法回滚；CREATE INDEX CONCURRENTLY：为了不阻塞写入，自身不在事务里；VACUUM/REINDEX CONCURRENTLY同理。
- 运维好处：
- 批量改表脚本可以打包：上线脚本里 10 条 DDL，中途出错
ROLLBACK全回退，不会留下半改的烂摊子； - 配合 schema migration 工具（Alembic / Flyway / Liquibase）的「单事务迁移」模式，迁移失败自动回滚到迁移前状态；
- 灰度发布更安全：可以 BEGIN 后跑全套 DDL + 数据校验，OK 才 COMMIT。
- 批量改表脚本可以打包：上线脚本里 10 条 DDL，中途出错
加分项：
- 能讲出 PG 实现事务性 DDL 的机制：所有 schema 信息存在系统目录表（
pg_class/pg_attribute等）里，这些 catalog 表本身也走 MVCC 与事务，所以 DDL 也能像普通 DML 一样回滚； - 能提到「长事务持有 ACCESS EXCLUSIVE 锁」的代价：DDL 默认拿独占锁，事务时间长会阻塞所有读写，所以生产上要么用
lock_timeout加保护，要么用CONCURRENTLY系列变体。
易错点：以为「PG 所有 DDL 都能回滚」 —— CREATE DATABASE 等少数 DDL 例外。
Q5：PG 的自增列 SERIAL 和 GENERATED ALWAYS AS IDENTITY 有什么区别？为什么官方推荐后者？
考察点：对 PG 自增机制底层实现的理解。
标准答案：
SERIAL是 PG 早期的语法糖。id SERIAL PRIMARY KEY实际等价于：sqlCREATE SEQUENCE table_id_seq; CREATE TABLE table ( id INTEGER NOT NULL DEFAULT nextval('table_id_seq') PRIMARY KEY ); ALTER SEQUENCE table_id_seq OWNED BY table.id;本质是「创建一个序列 + 列默认值用 nextval」。
GENERATED ALWAYS AS IDENTITY是 SQL 标准（SQL:2003）写法，PG 10 开始支持。它比SERIAL更好的原因：- 权限干净：
SERIAL创建的序列权限要单独管理，常因为忘记GRANT USAGE ON SEQUENCE导致权限错误；IDENTITY 把序列封装为表的一部分，权限随表走。 OVERRIDING行为更严格：ALWAYS模式下手动INSERT id会直接报错（除非加OVERRIDING SYSTEM VALUE），避免人为写入造成自增冲突。- 导出干净：
pg_dump导出 IDENTITY 列时不会产生独立的CREATE SEQUENCE语句，迁移更平滑。 - 类型修改方便：
SERIAL改为BIGSERIAL比较麻烦；IDENTITY 直接ALTER COLUMN id SET DATA TYPE BIGINT。
- 权限干净：
两种 IDENTITY 模式：
GENERATED ALWAYS：禁止手动指定值；GENERATED BY DEFAULT：允许手动指定值（与SERIAL行为一致）。
加分项：
- 能解释自增列**断号（gap）**问题：序列
nextval是事务外的（消费完不退还），ROLLBACK不会归还 ID，因此自增列不保证连续； - 能提到 PG 也支持
IDENTITY自定义起始值与步长：sqlid BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1000 INCREMENT BY 10)
易错点：把 SERIAL 当成「类型」 —— 它只是个语法糖，底层类型还是 INTEGER。
Q6：PG 中的 LIMIT/OFFSET 在大数据量分页时有什么问题？怎么解决？
考察点：对查询执行原理 + 真实性能问题的理解。
标准答案：
OFFSET N的本质是「扫描并丢弃前 N 行」，不是跳过它们。所以LIMIT 10 OFFSET 1000000会真实地读 1,000,010 行再返回 10 行，性能极差。- PG 与 MySQL 都有这个问题，本质是 SQL 标准的语义决定。
- 解决方案：
方案 ① 主键游标（推荐）：
sql-- 第一页 SELECT * FROM ch2_books ORDER BY id LIMIT 10; -- 后续每页用上一页最后一行的 id SELECT * FROM ch2_books WHERE id > 1234 ORDER BY id LIMIT 10;性能恒定 O(log N)，不随页数增加而劣化。
方案 ② 复合游标：当排序字段不唯一时（比如按
created_at），需要把(created_at, id)一起作为游标：sqlSELECT * FROM ch2_books WHERE (created_at, id) < ('2026-04-01 00:00:00+08', 9999) ORDER BY created_at DESC, id DESC LIMIT 10;方案 ③ 物理分页：极端深页用
TABLESAMPLE抽样，或在前端限制最大页数。
- 额外坑：
OFFSET+ORDER BY没用唯一字段时，相邻页可能出现重复或丢失（因为同created_at的行顺序不稳定）。
加分项：
- 能讲出
LIMIT/OFFSET在执行计划里对应「Limit + Sort + Seq Scan」（或 IndexScan），可以用EXPLAIN ANALYZE看到actual rows=1000010的真实读取量； - 能说出 PG 17 引入的「Incremental Sort」对
ORDER BY ... LIMIT类查询的优化场景。
易错点：以为 OFFSET 等价于「跳过」，其实是「扫到再丢掉」，所以「翻到第 1000 页」远比「翻到第 1 页」慢。
Q7：什么是 RETURNING？它解决了什么问题？
考察点：对 PG 特色 + 实际开发痛点的理解。
标准答案：
RETURNING让INSERT / UPDATE / DELETE直接返回受影响行的数据，无需再 SELECT 一次：sqlINSERT INTO orders(user_id, amount) VALUES (1, 99.5) RETURNING id, created_at;解决的问题：
- 省一次往返：传统写法要
INSERT后再SELECT LAST_INSERT_ID()/SELECT * WHERE id = ?，多一次 RTT； - 拿到默认值 / 触发器生成的值：
now()默认值、触发器写入的字段，只有RETURNING能立刻拿到； - 批量场景一次拿全：
INSERT ... SELECT ... RETURNING *能拿回所有插入的行，CTE +RETURNING还能做出复杂的「插入并删除并查询」的原子操作（参见下例）。
- 省一次往返：传统写法要
CTE + RETURNING 的高阶用法：
sqlWITH moved AS ( DELETE FROM events WHERE created_at < now() - interval '30 days' RETURNING * ) INSERT INTO events_archive SELECT * FROM moved;一条 SQL 完成「删 + 归档」，全部在一个事务里。
MySQL 对比：MySQL 8.0 之前完全没有 RETURNING；MariaDB 10.5 起支持但语法略不同；MySQL 真正引入是 8.0 在 INSERT 上有实验性
RETURNING，但远不如 PG 完整。
加分项：
- 能举一个 ORM 场景：psycopg / SQLAlchemy 等会自动用
RETURNING拿主键，所以 PG 上插入 + 拿 ID 是一次 RTT； - 能提到性能：相比
INSERT+SELECT两次往返，RETURNING在高并发场景能显著降低延迟。
易错点：以为 RETURNING * 没成本 —— 它会增加返回数据量，超大行数据（含 TEXT/JSONB）批量时要按需指定列。
🔗 延伸阅读
- 想全面了解 PG 的类型系统（数组 / JSONB / UUID / 范围 / ENUM）？→ 第 3 章 数据类型详解
- 想深入研究复杂查询（JOIN、CTE、窗口函数、UPSERT）？→ 第 5 章 高级查询
- 想搞懂
CHECK / UNIQUE / FK / 视图 / 物化视图？→ 第 4 章 约束与视图
📌 下一章预告：第 3 章「数据类型详解」—— 我们要全面盘点 PG 的类型系统，包括数组、JSONB、范围、UUID、自定义复合类型，以及它们在生产环境的最佳实践。
🎬 可视化演示
演示加载缓慢或样式异常？点此在新标签页打开 ↗
💻 示例代码
python
"""basic_query.py —— 第 2 章配套代码 · 图书管理系统
用途
用 psycopg v3 演示连接、查询、参数化查询、INSERT ... RETURNING、
事务（BEGIN/COMMIT/ROLLBACK）、批量执行（executemany）、
高效批量加载（COPY）等 PG 常用编程姿势。
前置
1. 已经按第 2 章 init.sql 建好 ch2_books / ch2_authors / ch2_categories 三张表：
psql -h 127.0.0.1 -U postgres -d learn_pg -f ../init.sql
2. 安装 psycopg v3：
pip install "psycopg[binary]>=3.1"
运行
python basic_query.py
"""
from __future__ import annotations
import io
import sys
from decimal import Decimal
try:
import psycopg
from psycopg import sql
from psycopg.rows import dict_row
except ImportError:
sys.exit('请先安装 psycopg v3：pip install "psycopg[binary]>=3.1"')
CONN_INFO = "host=127.0.0.1 port=5432 dbname=learn_pg user=postgres"
def section(title: str) -> None:
line = "─" * 70
print(f"\n{line}\n {title}\n{line}")
# ------------------------------------------------------------------ #
# Demo 1：基础查询 + 参数化（防 SQL 注入）
# ------------------------------------------------------------------ #
def demo_select_basic(conn: psycopg.Connection) -> None:
section("Demo 1 · 基础查询 + 参数化查询")
with conn.cursor(row_factory=dict_row) as cur:
cur.execute("SELECT count(*) AS n FROM ch2_books")
print("ch2_books 总条数 :", cur.fetchone()["n"])
# 参数化查询：%s 占位符（不是 Python 的 % 格式化！）
category = "科幻"
min_stock = 1
cur.execute(
"""
SELECT b.id, b.title, a.name AS author, b.price, b.stock
FROM ch2_books b
JOIN ch2_authors a ON a.id = b.author_id
JOIN ch2_categories c ON c.id = b.category_id
WHERE c.name = %s AND b.stock >= %s
ORDER BY b.price DESC
FETCH FIRST 5 ROWS ONLY
""",
(category, min_stock),
)
print(f"分类 = {category}, 库存 >= {min_stock} 的 Top5：")
for row in cur.fetchall():
print(
f" #{row['id']:<3} {row['title']:<30} "
f"author={row['author']:<10} ¥{row['price']} stock={row['stock']}"
)
# ------------------------------------------------------------------ #
# Demo 2：INSERT ... RETURNING（PG 特色）
# ------------------------------------------------------------------ #
def demo_insert_returning(conn: psycopg.Connection) -> int:
section("Demo 2 · INSERT ... RETURNING 一次拿到主键 + 时间戳")
with conn.cursor() as cur:
cur.execute(
"""
INSERT INTO ch2_books (title, author_id, category_id, isbn, published_at, stock, price)
VALUES (%s, %s, %s, %s, %s, %s, %s)
RETURNING id, created_at
""",
(
"psycopg 实战手册",
5, # Knuth 占个位
3, # 技术分类
"9999999999999",
"2026-04-01",
100,
Decimal("59.90"),
),
)
new_id, created_at = cur.fetchone()
print(f" 新书已插入：id={new_id}, created_at={created_at}")
conn.commit()
return new_id
# ------------------------------------------------------------------ #
# Demo 3：事务 —— BEGIN / COMMIT / ROLLBACK
# ------------------------------------------------------------------ #
def demo_transaction(conn: psycopg.Connection, book_id: int) -> None:
section("Demo 3 · 事务的 ROLLBACK 保护数据安全")
with conn.cursor() as cur:
cur.execute("SELECT stock FROM ch2_books WHERE id = %s", (book_id,))
before = cur.fetchone()[0]
print(f" 事务前 stock = {before}")
# psycopg v3 默认就是事务模式（autocommit=False），
# with conn.transaction() 显式开启嵌套事务 / 保存点。
try:
with conn.transaction():
with conn.cursor() as cur:
cur.execute(
"UPDATE ch2_books SET stock = stock + 999 WHERE id = %s", (book_id,)
)
raise RuntimeError("人为抛出错误，触发回滚")
except RuntimeError as exc:
print(f" 捕获异常并回滚：{exc}")
with conn.cursor() as cur:
cur.execute("SELECT stock FROM ch2_books WHERE id = %s", (book_id,))
after = cur.fetchone()[0]
print(f" 事务后 stock = {after}（与事务前一致 → 回滚成功）")
# ------------------------------------------------------------------ #
# Demo 4：executemany —— 批量插入
# ------------------------------------------------------------------ #
def demo_executemany(conn: psycopg.Connection) -> None:
section("Demo 4 · executemany 批量插入 5 个新作者")
rows = [
("Sample Author A", "测试国", 2000),
("Sample Author B", "测试国", 2001),
("Sample Author C", "测试国", 2002),
("Sample Author D", "测试国", 2003),
("Sample Author E", "测试国", 2004),
]
with conn.cursor() as cur:
cur.executemany(
"INSERT INTO ch2_authors(name, country, born_year) VALUES (%s, %s, %s)",
rows,
)
print(f" 已插入 {cur.rowcount} 行")
cur.execute(
"DELETE FROM ch2_authors WHERE name LIKE 'Sample Author%' RETURNING id"
)
print(f" 已清理 {cur.rowcount} 行（避免污染数据）")
conn.commit()
# ------------------------------------------------------------------ #
# Demo 5：COPY —— PG 最快的批量加载
# ------------------------------------------------------------------ #
def demo_copy(conn: psycopg.Connection) -> None:
section("Demo 5 · COPY FROM STDIN 高速批量加载（10x ~ 100x 快于 INSERT）")
csv_data = io.StringIO()
for i in range(20):
csv_data.write(f"copy_demo_author_{i}\t测试国\t{1900 + i}\n")
csv_data.seek(0)
with conn.cursor() as cur:
with cur.copy(
"COPY ch2_authors(name, country, born_year) FROM STDIN WITH (FORMAT text)"
) as cp:
cp.write(csv_data.read())
cur.execute(
"DELETE FROM ch2_authors WHERE name LIKE 'copy_demo_author_%' RETURNING id"
)
print(f" COPY 加载并清理 {cur.rowcount} 行")
conn.commit()
# ------------------------------------------------------------------ #
# Demo 6：动态构造表名 —— sql.Identifier 防注入
# ------------------------------------------------------------------ #
def demo_dynamic_identifier(conn: psycopg.Connection) -> None:
section("Demo 6 · 动态拼接表名（用 psycopg.sql 模块，不要用 f-string！）")
table = "ch2_books"
with conn.cursor() as cur:
query = sql.SQL("SELECT count(*) FROM {tbl}").format(
tbl=sql.Identifier(table)
)
cur.execute(query)
print(f" 表 {table} 共 {cur.fetchone()[0]} 行")
# ------------------------------------------------------------------ #
# Demo 7：服务端游标 —— 流式遍历大结果集
# ------------------------------------------------------------------ #
def demo_server_cursor(conn: psycopg.Connection) -> None:
section("Demo 7 · 服务端游标流式读 ch2_books 表（避免一次性加载到内存）")
with conn.cursor(name="books_stream") as cur:
cur.itersize = 5
cur.execute("SELECT id, title FROM ch2_books ORDER BY id")
for row in cur:
print(f" id={row[0]:<3} title={row[1]}")
# ------------------------------------------------------------------ #
# Main
# ------------------------------------------------------------------ #
def main() -> None:
print(f"连接：{CONN_INFO}")
try:
conn = psycopg.connect(CONN_INFO)
except psycopg.OperationalError as exc:
sys.exit(f"连接失败：{exc}\n请先按第 2 章说明初始化 learn_pg 数据库与 init.sql。")
try:
with conn:
demo_select_basic(conn)
new_id = demo_insert_returning(conn)
demo_transaction(conn, new_id)
demo_executemany(conn)
demo_copy(conn)
demo_dynamic_identifier(conn)
demo_server_cursor(conn)
with conn.cursor() as cur:
cur.execute(
"DELETE FROM ch2_books WHERE isbn = %s RETURNING id", ("9999999999999",)
)
if cur.rowcount:
print(f"\n [清理] 删掉 demo 插入的书 id={cur.fetchone()[0]}")
conn.commit()
finally:
conn.close()
print("\n✅ All demos done.")
if __name__ == "__main__":
main()markdown
# 第 2 章 配套代码
> 演示「图书管理系统」（`ch2_books / ch2_authors / ch2_categories`）下 psql + psycopg 的常见编程姿势。
## 准备工作
1. 跑初始化脚本建表 + 灌测试数据：
```bash
psql -h 127.0.0.1 -U postgres -d learn_pg -f ../init.sql- 安装依赖：bash
pip install "psycopg[binary]>=3.1" - （可选）通过环境变量覆盖默认连接信息：bash
export PG_DSN="host=127.0.0.1 port=5432 dbname=learn_pg user=postgres"
脚本一览
| 脚本 | 一句话说明 | 关键 PG 特性 |
|---|---|---|
basic_query.py | 用 psycopg v3 打通连接、查询、事务、批量写入、流式读 | 参数化查询、INSERT ... RETURNING、executemany、COPY FROM STDIN、sql.Identifier、服务端游标 |
后续随着章节推进，本目录会陆续新增
transaction_savepoint.py/copy_csv_loader.py等脚本，统一遵循「先建表 → 再python xxx.py」的运行约定。
预期输出
basic_query.py 跑完会看到 7 段 demo 输出，关键片段大致是：
──────────────────────────────────────────────────────────────────────
Demo 1 · 基础查询 + 参数化查询
──────────────────────────────────────────────────────────────────────
ch2_books 总条数 : 14
分类 = 科幻, 库存 >= 1 的 Top5：
#3 三体 III：死神永生 author=刘慈欣 ¥42.00 stock=25
...
──────────────────────────────────────────────────────────────────────
Demo 2 · INSERT ... RETURNING 一次拿到主键 + 时间戳
──────────────────────────────────────────────────────────────────────
新书已插入：id=15, created_at=2026-04-17 10:00:00+08
...
✅ All demos done.常见报错
connection refused→ PG 没起 / 端口不对 / 监听localhost时用了127.0.0.1之外的 hostrelation "ch2_books" does not exist→ 没跑../init.sqlpassword authentication failed→ 修改~/.pgpass或环境变量PGPASSWORDpermission denied for sequence ch2_books_id_seq→ 用IDENTITY而非SERIAL时不会出现；如果出现，说明老库残留了SERIAL的序列对象，重新跑init.sql即可