一、连接与基础操作
| 操作 |
命令 |
说明 |
| 连接到数据库 |
psql -U 用户名 -d 数据库名 -h 主机地址 -p 端口号 |
使用 psql 客户端连接 PostgreSQL |
| 查看当前连接信息 |
\conninfo |
显示当前数据库连接详情 |
| 查看 PostgreSQL 版本 |
SELECT VERSION(); |
返回数据库服务器版本 |
| 退出 psql |
\q |
退出 psql 命令行工具 |
cover: 附件/Halo封面/database.jpg
二、数据库管理命令
| 操作 |
SQL 命令 |
说明 |
| 查看所有数据库 |
\l |
psql 元命令，列出所有数据库 |
| 创建数据库 |
CREATE DATABASE 数据库名 OWNER 所有者; |
创建一个新数据库，可指定所有者 |
| 切换数据库 |
\c 数据库名 |
切换到指定的数据库 |
| 删除数据库 |
DROP DATABASE 数据库名; |
永久删除数据库，谨慎操作 |
| 查看当前数据库 |
SELECT CURRENT_DATABASE(); |
显示当前连接的数据库名 |
| 修改数据库 |
ALTER DATABASE 数据库名 RENAME TO 新名称; |
重命名数据库 |
| 查看数据库大小 |
SELECT pg_database_size('数据库名'); |
返回数据库占用的磁盘空间 |
cover: 附件/Halo封面/database.jpg
三、模式（Schema）管理命令
| 操作 |
SQL 命令 |
说明 |
| 查看所有模式 |
\dn |
psql 元命令，列出所有模式 |
| 创建模式 |
CREATE SCHEMA 模式名; |
创建一个新模式 |
| 删除模式 |
DROP SCHEMA 模式名; |
删除一个模式 |
| 级联删除模式 |
DROP SCHEMA 模式名 CASCADE; |
强制删除模式及其下的所有对象 |
cover: 附件/Halo封面/database.jpg
四、数据表管理命令
4.1 查看表信息
| 操作 |
命令 |
说明 |
| 查看所有表 |
\dt |
列出当前数据库中所有表 |
| 查看表结构 |
\d 表名 |
显示表的列、类型、约束等详细信息 |
| 查看表的详细信息 |
\dt+ 表名 |
显示包括注释、索引在内的更多信息 |
| 查看表的创建 SQL |
SELECT pg_get_serial_sequence('表名','列名'); 或使用 pg_dump -t 表名 |
查看表的定义语句 |
4.2 创建与删除表
| 操作 |
SQL 命令 |
| 创建表 |
CREATE TABLE 表名 (列名 数据类型 [约束], …); |
| 删除表 |
DROP TABLE 表名; |
| 条件删除表 |
DROP TABLE IF EXISTS 表名; |
创建表示例：
CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY, -- 自增主键
first_name VARCHAR(50) NOT NULL, -- 名字，非空
last_name VARCHAR(50) NOT NULL, -- 姓氏，非空
email VARCHAR(100) UNIQUE NOT NULL, -- 邮箱，唯一且非空
phone VARCHAR(20), -- 电话，可选
hire_date DATE NOT NULL, -- 入职日期
salary NUMERIC(10,2) CHECK (salary > 0), -- 薪资，必须大于 0
department_id INT
);
4.3 修改表结构
| 操作 |
SQL 命令 |
| 添加列 |
ALTER TABLE 表名 ADD COLUMN 列名 数据类型; |
| 删除列 |
ALTER TABLE 表名 DROP COLUMN 列名; |
| 修改列数据类型 |
ALTER TABLE 表名 ALTER COLUMN 列名 TYPE 新数据类型; |
| 重命名列 |
ALTER TABLE 表名 RENAME COLUMN 旧列名 TO 新列名; |
| 重命名表 |
ALTER TABLE 旧表名 RENAME TO 新表名; |
| 添加约束 |
ALTER TABLE 表名 ADD CONSTRAINT 约束名 CHECK (条件); |
| 删除约束 |
ALTER TABLE 表名 DROP CONSTRAINT 约束名; |
| 清空表数据 |
TRUNCATE TABLE 表名; |
cover: 附件/Halo封面/database.jpg
五、数据操作命令（DML）
5.1 插入数据
| 操作 |
SQL 命令 |
| 插入单行 |
INSERT INTO 表名 (列1, 列2, …) VALUES (值1, 值2, …); |
| 插入多行 |
INSERT INTO 表名 (列1, 列2, …) VALUES (值1, 值2), (值3, 值4), …; |
| 从查询结果插入 |
INSERT INTO 目标表 (列1, 列2) SELECT 列1, 列2 FROM 源表 WHERE 条件; |
| 冲突时更新 |
INSERT INTO 表名 (列1, 列2) VALUES (值1, 值2) ON CONFLICT (冲突列) DO UPDATE SET 列1 = 新值; |
5.2 查询数据
| 操作 |
SQL 命令 |
| 基本查询 |
SELECT 列1, 列2 FROM 表名 WHERE 条件; |
| 查询所有列 |
SELECT * FROM 表名; |
| 条件查询 |
SELECT * FROM 表名 WHERE 条件1 AND/OR 条件2; |
| 排序查询 |
SELECT * FROM 表名 ORDER BY 列名 ASC/DESC; |
| 分组查询 |
SELECT 列名, COUNT(*) FROM 表名 GROUP BY 列名; |
| 分组后过滤 |
SELECT 列名, COUNT(*) FROM 表名 GROUP BY 列名 HAVING COUNT(*) > 值; |
| 限制结果数量 |
SELECT * FROM 表名 LIMIT 数量 OFFSET 偏移量; |
| 去重查询 |
SELECT DISTINCT 列名 FROM 表名; |
5.3 更新与删除数据
| 操作 |
SQL 命令 |
| 更新数据 |
UPDATE 表名 SET 列1 = 值1, 列2 = 值2 WHERE 条件; |
| 删除数据 |
DELETE FROM 表名 WHERE 条件; |
| 从多表删除 |
DELETE FROM 表1 USING 表2 WHERE 表1.列 = 表2.列 AND 条件; |
| 返回修改的行 |
UPDATE 表名 SET 列 = 值 WHERE 条件 RETURNING *; |
cover: 附件/Halo封面/database.jpg
六、索引与约束管理命令
6.1 索引操作
| 操作 |
SQL 命令 |
| 查看索引 |
\di 或 SELECT * FROM pg_indexes WHERE tablename = '表名'; |
| 创建普通索引 |
CREATE INDEX 索引名 ON 表名 (列名); |
| 创建唯一索引 |
CREATE UNIQUE INDEX 索引名 ON 表名 (列名); |
| 创建复合索引 |
CREATE INDEX 索引名 ON 表名 (列1, 列2, …); |
| 创建表达式索引 |
CREATE INDEX 索引名 ON 表名 (LOWER(列名)); |
| 创建部分索引 |
CREATE INDEX 索引名 ON 表名 (列名) WHERE 条件; |
| 删除索引 |
DROP INDEX 索引名; |
| 重建索引 |
REINDEX INDEX 索引名; |
6.2 约束操作
| 约束类型 |
SQL 示例 |
| 主键 |
ALTER TABLE 表名 ADD PRIMARY KEY (列名); |
| 外键 |
ALTER TABLE 表名 ADD FOREIGN KEY (列名) REFERENCES 父表(列名); |
| 唯一约束 |
ALTER TABLE 表名 ADD UNIQUE (列名); |
| 检查约束 |
ALTER TABLE 表名 ADD CHECK (列名 > 0); |
| 非空约束 |
ALTER TABLE 表名 ALTER COLUMN 列名 SET NOT NULL; |
cover: 附件/Halo封面/database.jpg
七、事务与并发控制命令
| 操作 |
SQL 命令 |
说明 |
| 开始事务 |
BEGIN; 或 START TRANSACTION; |
开启一个新事务 |
| 提交事务 |
COMMIT; |
提交当前事务，永久保存修改 |
| 回滚事务 |
ROLLBACK; |
回滚当前事务，撤销所有修改 |
| 设置保存点 |
SAVEPOINT 保存点名; |
在事务中创建回滚点 |
| 回滚到保存点 |
ROLLBACK TO SAVEPOINT 保存点名; |
回滚到指定保存点 |
| 释放保存点 |
RELEASE SAVEPOINT 保存点名; |
删除保存点 |
| 查看当前事务隔离级别 |
SHOW TRANSACTION_ISOLATION; |
返回当前会话的事务隔离级别 |
| 设置事务隔离级别 |
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; |
可选值：READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE |
| 锁定表 |
LOCK TABLE 表名 IN ACCESS EXCLUSIVE MODE; |
对表加锁，用于显式控制并发 |
cover: 附件/Halo封面/database.jpg
八、用户与权限管理命令
8.1 用户/角色管理
| 操作 |
SQL 命令 |
说明 |
| 查看所有用户/角色 |
\du |
psql 元命令，列出所有角色及权限 |
| 创建用户（可登录） |
CREATE USER 用户名 WITH LOGIN PASSWORD '密码'; |
CREATE USER 是 CREATE ROLE … WITH LOGIN 的简写 |
| 创建角色（不可登录） |
CREATE ROLE 角色名; |
创建普通角色，通常用作权限组 |
| 创建超级用户 |
CREATE ROLE super_admin WITH SUPERUSER LOGIN PASSWORD '密码'; |
创建具有超级权限的用户 |
| 修改密码 |
ALTER ROLE 用户名 WITH PASSWORD '新密码'; |
修改用户密码 |
| 允许/禁止登录 |
ALTER ROLE 用户名 WITH LOGIN/NOLOGIN; |
控制用户是否可登录 |
| 授予超级用户权限 |
ALTER ROLE 用户名 WITH SUPERUSER; |
提升用户为超级用户 |
| 允许创建数据库 |
ALTER ROLE 用户名 WITH CREATEDB; |
授予创建数据库权限 |
| 删除用户 |
DROP ROLE 角色名; |
删除角色，需先确保无依赖对象 |
| 强制删除用户 |
DROP ROLE IF EXISTS 角色名 CASCADE; |
级联删除所有关联权限 |
8.2 权限授予与撤销
| 操作 |
SQL 命令 |
说明 |
| 授予表权限 |
GRANT SELECT, INSERT, UPDATE, DELETE ON 表名 TO 用户名; |
授予用户对表的特定操作权限 |
| 授予所有权限 |
GRANT ALL ON 表名 TO 用户名; |
授予用户对表的所有权限 |
| 授予数据库权限 |
GRANT CONNECT, CREATE ON DATABASE 数据库名 TO 用户名; |
授予用户连接和创建模式的权限 |
| 授予模式权限 |
GRANT USAGE ON SCHEMA 模式名 TO 用户名; |
授予用户使用模式的权限 |
| 授予函数权限 |
GRANT EXECUTE ON FUNCTION 函数名 TO 用户名; |
授予用户执行函数的权限 |
| 撤销权限 |
REVOKE 权限 ON 对象 FROM 用户名; |
撤销用户权限 |
| 授予 PUBLIC |
GRANT 权限 ON 对象 TO PUBLIC; |
将权限授予所有用户 |
| 授予授予选项 |
GRANT 权限 ON 对象 TO 用户名 WITH GRANT OPTION; |
允许用户将权限授予他人 |
| 查看用户权限 |
\du 或 SELECT * FROM information_schema.role_table_grants; |
查看用户拥有的权限 |
cover: 附件/Halo封面/database.jpg
九、psql 元命令大全
psql 元命令以反斜杠 \ 开头，是 PostgreSQL 客户端提供的快捷命令。
9.1 信息查询类
| 命令 |
说明 |
\l 或 \list |
列出所有数据库 |
\c 数据库名 |
切换到指定数据库 |
\dt |
列出当前数据库中的所有表 |
\dt+ |
列出所有表（含注释、大小等信息） |
\d 表名 |
查看指定表的结构 |
\d+ 表名 |
查看表结构（含存储参数、注释） |
\di |
列出所有索引 |
\dn |
列出所有模式 |
\df |
列出所有函数 |
\dv |
列出所有视图 |
\du 或 \dg |
列出所有角色和用户 |
\conninfo |
显示当前连接信息 |
9.2 格式控制类
| 命令 |
说明 |
\x |
切换扩展显示模式（垂直显示，适合宽表） |
\pset format unaligned |
设置无对齐输出格式（适合导出数据） |
\pset border 0|1|2 |
设置表格边框样式 |
\H |
切换 HTML 输出格式 |
9.3 性能与统计类
| 命令 |
说明 |
\timing |
切换 SQL 执行时间显示 |
\watch [秒数] |
重复执行当前查询，用于监控 |
9.4 文件操作类
| 命令 |
说明 |
\i 文件名 |
执行指定 SQL 文件中的命令 |
\o 文件名 |
将查询结果重定向到文件 |
\copy |
导入/导出数据（客户端侧） |
9.5 其他实用命令
| 命令 |
说明 |
\e |
打开编辑器编辑当前查询缓冲区 |
\! 命令 |
在 shell 中执行外部命令 |
\? |
查看所有元命令的帮助 |
\h SQL命令 |
查看指定 SQL 命令的语法帮助 |
cover: 附件/Halo封面/database.jpg
十、数据类型速查
PostgreSQL 提供了丰富的内置数据类型。
10.1 数值类型
| 类型 |
别名 |
说明 |
SMALLINT |
int2 |
2 字节整数，范围 -32768 到 32767 |
INTEGER |
int, int4 |
4 字节整数，范围 -2³¹ 到 2³¹-1 |
BIGINT |
int8 |
8 字节整数，范围 -2⁶³ 到 2⁶³-1 |
DECIMAL(p,s) |
numeric |
精确数字，可指定精度和小数位数 |
REAL |
float4 |
单精度浮点数，4 字节 |
DOUBLE PRECISION |
float8 |
双精度浮点数，8 字节 |
SERIAL |
serial4 |
自动增长的 4 字节整数 |
BIGSERIAL |
serial8 |
自动增长的 8 字节整数 |
10.2 字符串类型
| 类型 |
别名 |
说明 |
CHAR(n) |
character(n) |
定长字符串，不足补空格 |
VARCHAR(n) |
character varying(n) |
变长字符串，最大 n 字符 |
TEXT |
无 |
变长字符串，无长度限制 |
BYTEA |
无 |
二进制数据 |
10.3 日期/时间类型
| 类型 |
说明 |
DATE |
日历日期（年、月、日） |
TIME |
一天中的时间（无时区） |
TIMESTAMP |
日期和时间（无时区） |
TIMESTAMPTZ |
日期和时间（含时区） |
INTERVAL |
时间段 |
10.4 其他常用类型
| 类型 |
说明 |
BOOLEAN |
逻辑布尔值（真/假） |
JSON |
文本 JSON 数据 |
JSONB |
二进制 JSON 数据，支持索引 |
UUID |
通用唯一标识码 |
INET |
IPv4 或 IPv6 主机地址 |
CIDR |
IPv4 或 IPv6 网络地址 |
MACADDR |
MAC 地址 |
cover: 附件/Halo封面/database.jpg
十一、备份与恢复命令
11.1 pg_dump（单数据库备份）
| 操作 |
命令 |
说明 |
| 备份为 SQL 脚本 |
pg_dump -U 用户名 -d 数据库名 > backup.sql |
导出为纯文本 SQL 脚本 |
| 备份为自定义格式 |
pg_dump -U 用户名 -d 数据库名 -Fc -f backup.dump |
压缩的自定义格式，支持选择性恢复 |
| 仅备份结构 |
pg_dump -U 用户名 -d 数据库名 --schema-only |
只导出表结构，不导出数据 |
| 仅备份数据 |
pg_dump -U 用户名 -d 数据库名 --data-only |
只导出数据，不导出结构 |
| 备份指定表 |
pg_dump -U 用户名 -d 数据库名 -t 表名 > table_backup.sql |
只备份特定表 |
| 包含清理命令 |
pg_dump -U 用户名 -d 数据库名 --clean |
在创建前添加 DROP 命令 |
| 备份远程数据库 |
pg_dump -h 主机 -p 端口 -U 用户名 -d 数据库名 > backup.sql |
从远程数据库导出 |
11.2 pg_dumpall（全集群备份）
| 操作 |
命令 |
说明 |
| 备份所有数据库 |
pg_dumpall -U 用户名 > all_backup.sql |
备份整个 PostgreSQL 集群的所有数据库 |
| 仅备份全局对象 |
pg_dumpall -U 用户名 --globals-only > globals.sql |
只备份角色和表空间等全局对象 |
11.3 恢复
| 操作 |
命令 |
说明 |
| 从 SQL 脚本恢复 |
psql -U 用户名 -d 数据库名 < backup.sql |
执行 SQL 脚本恢复数据库 |
| 从自定义格式恢复 |
pg_restore -U 用户名 -d 数据库名 backup.dump |
使用 pg_restore 恢复归档格式 |
| 选择性恢复 |
pg_restore -U 用户名 -d 数据库名 -t 表名 backup.dump |
只恢复指定表 |
| 查看归档内容 |
pg_restore -l backup.dump |
列出归档文件中的内容 |
cover: 附件/Halo封面/database.jpg
十二、性能分析与优化命令
12.1 执行计划分析
| 操作 |
命令 |
说明 |
| 查看执行计划 |
EXPLAIN SELECT …; |
显示 SQL 语句的执行计划 |
| 实际执行并分析 |
EXPLAIN ANALYZE SELECT …; |
实际执行并返回详细的执行统计信息 |
| 带缓冲区的分析 |
EXPLAIN (ANALYZE, BUFFERS) SELECT …; |
显示缓冲区使用情况 |
| 详细输出 |
EXPLAIN (VERBOSE, ANALYZE) SELECT …; |
输出更详细的执行信息 |
12.2 系统统计信息
| 操作 |
命令 |
说明 |
| 查看表统计信息 |
SELECT * FROM pg_stats WHERE tablename = '表名'; |
查看表的数据分布统计 |
| 分析表（更新统计） |
ANALYZE 表名; |
手动更新表的统计信息 |
| 查看表大小 |
SELECT pg_relation_size('表名'); |
返回表占用的磁盘空间 |
| 查看表总大小 |
SELECT pg_total_relation_size('表名'); |
返回表及索引的总大小 |
| 查看所有表大小 |
SELECT relname, pg_size_pretty(pg_relation_size(relid)) FROM pg_stat_user_tables ORDER BY pg_relation_size(relid) DESC; |
按大小排序显示所有用户表 |
12.3 连接与活动监控
| 操作 |
命令 |
说明 |
| 查看当前活动连接 |
SELECT * FROM pg_stat_activity; |
查看所有当前连接的会话信息 |
| 终止连接 |
SELECT pg_terminate_backend(pid); |
终止指定 PID 的会话 |
| 查看锁信息 |
SELECT * FROM pg_locks; |
查看当前持有的锁 |
12.4 参数配置
| 操作 |
命令 |
说明 |
| 查看当前配置 |
SHOW ALL; |
显示所有配置参数 |
| 查看特定参数 |
SHOW shared_buffers; |
查看指定参数的值 |
| 设置参数 |
SET work_mem = '16MB'; |
临时设置当前会话的参数 |
cover: 附件/Halo封面/database.jpg
十三、常用函数速查
| 类别 |
函数示例 |
说明 |
| 字符串 |
LENGTH(), UPPER(), LOWER(), CONCAT(), SUBSTRING() |
字符串处理 |
| 日期时间 |
NOW(), CURRENT_DATE, EXTRACT(), DATE_TRUNC() |
日期时间处理 |
| 数学 |
ROUND(), FLOOR(), CEIL(), ABS() |
数学计算 |
| 聚合 |
COUNT(), SUM(), AVG(), MAX(), MIN() |
聚合统计 |
| 条件 |
CASE WHEN … END, COALESCE(), NULLIF() |
条件判断 |
| JSON |
jsonb_extract_path_text(), jsonb_array_elements() |
JSON 数据操作 |
| 窗口函数 |
ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD() |
高级分析查询 |
cover: 附件/Halo封面/database.jpg
以上命令涵盖了 PostgreSQL 从基础到高级的绝大部分操作场景，可作为日常开发和运维的速查参考。如需特定模块的更详细说明，可以随时告知。
