psql 命令
元命令在 psql(PostgreSQL 的终端客户端)里执行,而不是在服务器上执行。它们以反斜杠开头,不需要分号。
| 作用 | 命令 |
|---|
| 列出数据库 | \l |
| 连接到另一个数据库 | \c shop |
| 列出表 | \dt |
| 列出所有模式中的表 | \dt *.* |
| 查看表结构(列、索引、键) | \d users |
| 查看表结构及存储信息和注释 | \d+ users |
| 列出模式 | \dn |
| 列出视图 | \dv |
| 列出索引 | \di |
| 列出函数 | \df |
| 列出角色(用户) | \du |
| 显示当前连接 | \conninfo |
| 运行文件中的 SQL | \i setup.sql |
| 开启或关闭查询计时 | \timing |
| 扩展显示(每列单独一行) | \x |
| 在编辑器中修改上一条查询 | \e |
| 查看 SQL 语法帮助 | \h CREATE TABLE |
| 列出所有元命令 | \? |
| 退出 psql | \q |
从终端连接
| 作用 | 命令 |
|---|
| 以指定用户连接到数据库 | psql -U postgres -d shop |
| 连接到远程主机和端口 | psql -h db.example.com -p 5432 -U app -d shop |
| 用连接 URL 连接 | psql postgresql://app:secret@localhost:5432/shop |
| 运行一条查询后退出 | psql -d shop -c "SELECT count(*) FROM users;" |
| 运行 SQL 文件 | psql -d shop -f setup.sql |
| 在 shell 中创建数据库 | createdb shop |
| 在 shell 中删除数据库 | dropdb shop |
数据库与模式
| 作用 | SQL |
|---|
| 创建数据库 | CREATE DATABASE shop; |
| 删除数据库 | DROP DATABASE shop; |
| 显示当前数据库 | SELECT current_database(); |
| 创建模式 | CREATE SCHEMA sales; |
| 显示模式搜索顺序 | SHOW search_path; |
| 优先在某个模式中查找 | SET search_path TO sales, public; |
| 显示服务器版本 | SELECT version(); |
表与数据类型
| 作用 | SQL |
|---|
| 创建带 identity 主键的表 | CREATE TABLE products (id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL, price numeric(10,2) CHECK (price >= 0), created_at timestamptz DEFAULT now()); |
| 添加列 | ALTER TABLE users ADD COLUMN phone text; |
| 重命名列 | ALTER TABLE users RENAME COLUMN country TO country_code; |
| 修改列的类型 | ALTER TABLE users ALTER COLUMN age TYPE smallint; |
| 设置默认值 | ALTER TABLE users ALTER COLUMN active SET DEFAULT false; |
| 删除列 | ALTER TABLE users DROP COLUMN tags; |
| 清空表并重置 identity | TRUNCATE orders, users RESTART IDENTITY; |
| 表存在时删除 | DROP TABLE IF EXISTS products; |
| 用于金额的精确小数 | numeric(10,2) |
| 带时区的日期和时间 | timestamptz |
| 随机 UUID | SELECT gen_random_uuid(); |
| 枚举类型 | CREATE TYPE mood AS ENUM ('happy', 'sad'); |
约束与键
| 作用 | SQL |
|---|
| 添加主键 | ALTER TABLE logs ADD PRIMARY KEY (id); |
| 级联删除子行的外键 | CREATE TABLE reviews (id integer PRIMARY KEY, user_id integer REFERENCES users(id) ON DELETE CASCADE); |
| 添加检查约束 | ALTER TABLE orders ADD CONSTRAINT amount_positive CHECK (amount > 0); |
| NULL 视为相等的唯一约束(15+) | CREATE TABLE emails (address text UNIQUE NULLS NOT DISTINCT); |
| 把列设为必填 | ALTER TABLE users ALTER COLUMN country SET NOT NULL; |
| 删除约束 | ALTER TABLE users DROP CONSTRAINT users_email_key; |
查询(PostgreSQL 特有写法)
标准的 SELECT、WHERE 和 JOIN 在 SQL 速查表里;这里是 PostgreSQL 特有的写法。
| 作用 | SQL |
|---|
| 不区分大小写的匹配 | SELECT * FROM users WHERE name ILIKE 'a%'; |
| 正则表达式匹配 | SELECT * FROM users WHERE email ~ '@example\.com$'; |
| 每组取第一行 | SELECT DISTINCT ON (user_id) user_id, amount FROM orders ORDER BY user_id, amount DESC; |
| 限制行数和偏移 | SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20; |
| 标准的行数限制 | SELECT * FROM users ORDER BY id FETCH FIRST 5 ROWS ONLY; |
| 用 :: 转换类型 | SELECT '42'::integer + 1; |
| NULL 的替代值 | SELECT name, COALESCE(email, 'none') FROM users; |
| 把 NULL 视为相等的比较 | SELECT * FROM users WHERE email IS DISTINCT FROM 'asha@example.com'; |
| 生成数字序列 | SELECT * FROM generate_series(1, 5); |
| 聚合成字符串 | SELECT string_agg(name, ', ' ORDER BY name) FROM users; |
| 条件聚合 | SELECT count(*) FILTER (WHERE status = 'paid') AS paid, count(*) AS total FROM orders; |
插入、更新、删除和 upsert
| 作用 | SQL |
|---|
| 插入并返回新 id | INSERT INTO users (name, email) VALUES ('Dana', 'dana@example.com') RETURNING id; |
| 插入多行 | INSERT INTO users (name) VALUES ('Eli'), ('Femi'); |
| Upsert(插入或更新) | INSERT INTO users (name, email) VALUES ('Asha K', 'asha@example.com') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name; |
| 插入时忽略重复 | INSERT INTO users (name, email) VALUES ('Ben', 'ben@example.com') ON CONFLICT DO NOTHING; |
| 更新并查看被修改的行 | UPDATE employees SET salary = salary * 1.1 WHERE department = 'IT' RETURNING name, salary; |
| 根据另一张表更新 | UPDATE orders SET status = 'vip' FROM users WHERE orders.user_id = users.id AND users.country = 'IN'; |
| 删除并查看被删除的行 | DELETE FROM orders WHERE status = 'refunded' RETURNING id; |
| 借助另一张表删除 | DELETE FROM orders USING users WHERE orders.user_id = users.id AND users.active = false; |
| 把行复制到新表 | CREATE TABLE paid_orders AS SELECT * FROM orders WHERE status = 'paid'; |
JSONB
| 作用 | SQL |
|---|
| 以 JSON 形式取字段 | SELECT profile -> 'plan' FROM users; |
| 以文本形式取字段 | SELECT profile ->> 'plan' FROM users; |
| 以文本形式取嵌套字段 | SELECT profile #>> '{address,city}' FROM users; |
| JSON 包含某个值的行 | SELECT * FROM users WHERE profile @> '{"plan": "pro"}'; |
| 包含某个键的行 | SELECT * FROM users WHERE profile ? 'city'; |
| 设置键的值 | UPDATE users SET profile = jsonb_set(profile, '{plan}', '"team"') WHERE id = 2; |
| 合并对象 | UPDATE users SET profile = profile || '{"beta": true}'; |
| 删除键 | UPDATE users SET profile = profile - 'city'; |
| 用列构建 JSON | SELECT jsonb_build_object('id', id, 'name', name) FROM users; |
| 把行转成 JSON 数组 | SELECT jsonb_agg(users) FROM users; |
| 用于 @> 和 ? 查找的索引 | CREATE INDEX users_profile_idx ON users USING gin (profile); |
数组
| 作用 | SQL |
|---|
| 数组字面量 | SELECT ARRAY[1, 2, 3]; |
| 数组中包含某个值的行 | SELECT * FROM users WHERE 'sql' = ANY(tags); |
| 数组包含全部给定值的行 | SELECT * FROM users WHERE tags @> ARRAY['sql', 'python']; |
| 每个元素一行 | SELECT name, unnest(tags) FROM users; |
| 追加元素 | UPDATE users SET tags = array_append(tags, 'sql') WHERE id = 2; |
| 数组长度 | SELECT name, cardinality(tags) FROM users; |
日期与时间
| 作用 | SQL |
|---|
| 当前时间(带时区) | SELECT now(); |
| 今天 | SELECT current_date; |
| 加上时间间隔 | SELECT current_date + INTERVAL '7 days'; |
| 最近 30 天的行 | SELECT * FROM orders WHERE created_at > now() - INTERVAL '30 days'; |
| 截断到月 | SELECT date_trunc('month', created_at) AS month, sum(amount) FROM orders GROUP BY 1 ORDER BY 1; |
| 提取某个部分 | SELECT EXTRACT(YEAR FROM hired_on) FROM employees; |
| 日期之差(天数) | SELECT DATE '2026-12-25' - DATE '2026-11-17'; |
| 两个日期之间的时长 | SELECT name, age(current_date, hired_on) FROM employees; |
| 把日期格式化为文本 | SELECT to_char(hired_on, 'DD Mon YYYY') FROM employees; |
| 把文本解析为日期 | SELECT to_date('17/11/2026', 'DD/MM/YYYY'); |
| 转换到某个时区 | SELECT now() AT TIME ZONE 'Asia/Kolkata'; |
文本函数
| 作用 | SQL |
|---|
| 拼接(遇到 NULL 结果为 NULL) | SELECT name || ' <' || email || '>' FROM users; |
| 拼接并跳过 NULL | SELECT concat_ws(' ', name, email) FROM users; |
| 大写和小写 | SELECT upper(name), lower(name) FROM users; |
| 长度 | SELECT name, length(name) FROM users; |
| 截取子串 | SELECT substring(name FROM 1 FOR 2) FROM users; |
| 替换 | SELECT replace(email, '@example.com', '') FROM users; |
| 按分隔符取其中一段 | SELECT split_part(email, '@', 2) FROM users; |
| 正则表达式替换 | SELECT regexp_replace('a1b22c', '[0-9]+', '', 'g'); |
| 去掉首尾空格 | SELECT trim(' hi '); |
CTE 与窗口函数
| 作用 | SQL |
|---|
| 用 WITH 给子查询命名 | WITH paid AS (SELECT user_id, sum(amount) AS total FROM orders WHERE status = 'paid' GROUP BY user_id) SELECT * FROM paid WHERE total > 50; |
| 递归 CTE(数到 5) | WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 5) SELECT i FROM n; |
| 按组给行编号 | SELECT name, department, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) FROM employees; |
| 并列排名 | SELECT name, DENSE_RANK() OVER (ORDER BY salary DESC) FROM employees; |
| 累计求和 | SELECT id, amount, sum(amount) OVER (ORDER BY id) FROM orders; |
| 上一行的值 | SELECT name, salary, LAG(salary) OVER (ORDER BY hired_on) FROM employees; |
| 为每一行连接它自己的子查询 | SELECT u.name, o.amount FROM users u CROSS JOIN LATERAL (SELECT amount FROM orders WHERE user_id = u.id ORDER BY amount DESC LIMIT 1) o; |
索引
| 作用 | SQL |
|---|
| B-tree 索引(默认) | CREATE INDEX orders_user_idx ON orders (user_id); |
| 唯一索引 | CREATE UNIQUE INDEX employees_name_idx ON employees (name); |
| 多列索引 | CREATE INDEX orders_user_created_idx ON orders (user_id, created_at); |
| 部分索引 | CREATE INDEX orders_paid_idx ON orders (created_at) WHERE status = 'paid'; |
| 表达式索引 | CREATE INDEX users_lower_email_idx ON users (lower(email)); |
| 覆盖索引(仅索引扫描) | CREATE INDEX orders_user_cover_idx ON orders (user_id) INCLUDE (amount); |
| 创建时不阻塞写入 | CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status); |
| 删除索引 | DROP INDEX IF EXISTS orders_user_idx; |
| 查看执行计划 | EXPLAIN SELECT * FROM orders WHERE user_id = 1; |
| 查看带实际耗时的执行计划 | EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1; |
事务与锁
| 作用 | SQL |
|---|
| 开始事务,然后保留或撤销 | BEGIN; UPDATE employees SET salary = 0 WHERE id = 3; ROLLBACK; |
| 撤销事务的一部分 | BEGIN; INSERT INTO users (name) VALUES ('Gil'); SAVEPOINT s1; DELETE FROM users WHERE name = 'Gil'; ROLLBACK TO s1; COMMIT; |
| 更严格的隔离级别 | BEGIN ISOLATION LEVEL SERIALIZABLE; SELECT count(*) FROM orders; COMMIT; |
| 锁住读取的行 | BEGIN; SELECT * FROM orders WHERE id = 1 FOR UPDATE; COMMIT; |
| 任务队列:跳过已锁定的行 | BEGIN; SELECT id FROM orders WHERE status = 'paid' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED; COMMIT; |
用户、角色与权限
| 作用 | SQL |
|---|
| 创建可登录的角色 | CREATE ROLE app LOGIN PASSWORD 'change-me'; |
| 授予表的只读权限 | GRANT SELECT ON users TO app; |
| 授予模式中所有表的读取权限 | GRANT SELECT ON ALL TABLES IN SCHEMA public TO app; |
| 收回权限 | REVOKE SELECT ON users FROM app; |
| 修改密码 | ALTER ROLE app PASSWORD 'new-secret'; |
| 显示当前用户 | SELECT current_user; |
维护与监控
| 作用 | SQL |
|---|
| 正在运行的查询 | SELECT pid, state, query FROM pg_stat_activity WHERE state <> 'idle'; |
| 取消查询 | SELECT pg_cancel_backend(12345); |
| 结束连接 | SELECT pg_terminate_backend(12345); |
| 表及其索引的大小 | SELECT pg_size_pretty(pg_total_relation_size('orders')); |
| 数据库的大小 | SELECT pg_size_pretty(pg_database_size(current_database())); |
| 更新查询规划器的统计信息 | ANALYZE orders; |
| 回收死行 | VACUUM orders; |
| 把表重写到最小体积(会锁表) | VACUUM FULL orders; |
| 显示某个设置 | SHOW work_mem; |
| 修改本会话的设置 | SET work_mem = '64MB'; |
备份与恢复
PostgreSQL 客户端工具中的 shell 命令。
| 作用 | 命令 |
|---|
| 把数据库导出为 SQL 文件 | pg_dump -U postgres shop > shop.sql |
| 以自定义格式导出 | pg_dump -Fc -U postgres shop > shop.dump |
| 导出单张表 | pg_dump -t orders shop > orders.sql |
| 恢复 SQL 导出文件 | psql -d shop -f shop.sql |
| 恢复自定义格式的导出文件 | pg_restore -d shop shop.dump |
| 导出所有数据库和角色 | pg_dumpall > cluster.sql |
| 把查询结果导出为 CSV(在 psql 中) | \copy (SELECT * FROM orders) TO 'orders.csv' CSV HEADER |
| 导入 CSV(在 psql 中) | \copy orders (user_id, amount, status) FROM 'orders.csv' CSV HEADER |
常用的 psql 命令和 PostgreSQL 语句,都整理在可直接复制的表格里:先是连接和 \d 系列命令,然后是表、upsert、JSONB、日期、窗口函数、索引、角色、备份和维护。
这里的 SQL 使用 PostgreSQL 17 语法,大部分在更早的版本中也能用。其中的 SQL 语句都在 PostgreSQL 17 上验证过;想试试的话,把它粘贴到自带示例表的 PostgreSQL 在线编辑器里即可。