【PostgreSQL从零到精通】第08篇：psql工具完全指南——被严重低估的数据库管理利器

上一篇【第07篇】查询的艺术——单表查询、多表关联与子查询完全指南
下一篇【第09篇】PostgreSQL数据类型全景图——从基础类型到高级类型

很多人觉得数据库管理工具就是 DBeaver 或 pgAdmin，但其实 PostgreSQL 自带的 psql 才是最强大、最高效的数据库交互工具。本文带你全面掌握 psql 的核心功能。

写在前面

如果你用 PostgreSQL，一定绕不开 psql。它是 PG 自带的命令行客户端，虽然界面朴素，但功能极其强大——元命令、自动补全、输出格式化、快捷键、变量替换……这些功能让 psql 的操作效率远超图形化工具。

很多老 DBA 的习惯是：简单查询用图形化工具，复杂的操作全部用 psql。读完这篇文章，你就能理解为什么。

一、psql 简介

1.1 什么是 psql？

psql 是 PostgreSQL 的官方命令行客户端，随 PG 安装包一起提供。它不仅可以执行 SQL 语句，还提供了大量内置的元命令（以\开头）来管理数据库。

1.2 启动 psql

# 最基本的连接方式psql-Upostgres-dpostgres# 指定主机、端口、用户、数据库psql-h192.168.1.100-p5432-Umyuser-dmydb# 使用连接字符串psql"host=192.168.1.100 port=5432 dbname=mydb user=myuser password=mypwd"# 使用 Unix 域套接字（本地连接，最快）psql-Upostgres# 直接执行 SQL 语句psql-c"SELECT version();"# 执行 SQL 文件psql-f/path/to/script.sql# 从 stdin 管道输入catscript.sql|psql

1.3 psql 的常用启动参数

二、核心元命令（\命令）

2.1 帮助命令

-- 查看所有元命令\?-- 查看 SQL 命令的帮助\h-- 列出所有 SQL 命令\hSELECT-- 查看 SELECT 语法帮助\hCREATETABLE-- 查看 CREATE TABLE 语法帮助\hALTERTABLE-- 查看 ALTER TABLE 语法帮助-- 查看所有数据类型的帮助\hT-- 查看特定函数的帮助\df-- 列出所有函数\df substring-- 查找包含 substring 的函数

2.2 数据库和连接管理

-- 列出所有数据库\l-- 或 \list-- 切换数据库\c mydb-- 连接到 mydb 数据库\c mydb user1-- 以 user1 身份连接到 mydb-- 查看当前连接信息\conninfo-- 输出：You are connected to database "mydb" as user "postgres" on host "localhost" at port "5432".-- 断开连接\q

2.3 表和对象浏览

-- 列出当前数据库的所有表\dt-- 只显示普通表\dt+-- 显示表大小等额外信息\dtpublic.*-- 显示 public 模式下的表\dt schema_name.*-- 显示指定模式下的表-- 列出所有对象类型\d-- 列出表、视图、序列、索引\dv-- 列出视图\di-- 列出索引\ds-- 列出序列\dS-- 列出系统表\dm-- 列出物化视图\dn-- 列出所有模式（schema）\df-- 列出所有函数\dT-- 列出所有数据类型\db-- 列出所有表空间-- 查看表结构（最常用！）\d table_name \d+table_name-- 显示更多信息（大小、描述等）\d student \d+student-- 查看视图定义\d+view_name-- 查看函数定义\df+function_name-- 查看索引信息\di+index_name

2.4 输出格式控制

-- 切换扩展显示模式（推荐！每个字段占一行，方便查看宽表）\x \x auto-- 自动判断是否使用扩展显示-- 分页控制\pset pager-- 开启分页\pset pageroff-- 关闭分页（输出长内容时很有用）-- 设置输出格式\pset format aligned-- 对齐格式（默认）\pset format wrapped-- 自动换行\pset format html-- HTML 格式\pset format latex-- LaTeX 格式-- 设置 NULL 的显示\psetnull'(NULL)'-- 将 NULL 显示为 (NULL) 而不是空白-- 设置字段分隔符\pset fieldsep'|'-- 使用 | 作为字段分隔符\pset fieldsep','-- CSV 格式-- 设置边框样式\pset border0-- 无边框\pset border1-- 内部边框\pset border2-- 全部边框（默认）-- 导出查询结果到文件\o/tmp/result.txt-- 输出重定向到文件SELECT*FROMusers;\o-- 恢复输出到终端-- 复制查询结果到系统剪贴板\copy(SELECT*FROMusers)TOSTDOUTWITHCSV HEADER

2.5 执行外部文件

-- 执行外部 SQL 文件\i/path/to/script.sql-- 如果文件不在当前目录，使用绝对路径\i/home/postgres/scripts/create_tables.sql-- 查看文件内容（不执行）\ef-- 编辑函数\e-- 编辑上一条 SQL 到编辑器中

三、psql 变量系统

3.1 系统变量

-- 查看所有 psql 变量\set-- 常用系统变量\echo :DBNAME-- 当前数据库名\echo :USER-- 当前用户\echo :HOST-- 当前主机\echo :PORT-- 当前端口\echo :PROMPT1-- 当前提示符

3.2 自定义变量

-- 设置变量\setmyvar'hello world'\echo :myvar-- 输出：hello world-- 在 SQL 中使用变量\settablename'users'SELECT*FROM:tablenameWHEREid=1;-- 变量中包含特殊字符\setmyquery'SELECT * FROM users WHERE name = '\''test\'\'':myquery

3.3 变量在脚本中的妙用

# 在 shell 脚本中使用 psql 变量psql-vtable_name=users-vmin_age=18<<EOF SELECT * FROM :table_name WHERE age > :min_age; EOF

四、快捷键

4.1 编辑快捷键

在 psql 命令行中，可以使用以下快捷键：

4.2 历史快捷键

4.3 自动补全

psql 支持Tab 键自动补全（需要 readline 库支持）：

-- 输入部分命令后按 TabSEL<Tab>→SELECT\dt<Tab>→ 列出以 dt 开头的元命令 \d<Tab><Tab>→ 列出所有 \d 开头的元命令-- SQL 关键字补全CREATETAB<Tab>→CREATETABLE-- 表名和列名补全SELECT*FROMstu<Tab>→SELECT*FROMstudentSELECTno,na<Tab>FROMstudent →SELECTno,nameFROMstudent

五、事务控制

-- psql 默认每条语句自动提交-- 如果需要手动控制事务，使用以下方式-- 开启事务BEGIN;-- 执行多条 SQLINSERTINTOusers(name)VALUES('test1');INSERTINTOusers(name)VALUES('test2');UPDATEusersSETstatus='active'WHEREnameLIKE'test%';-- 查看中间结果SELECT*FROMusersWHEREnameLIKE'test%';-- 确认无误，提交COMMIT;-- 或者回滚-- ROLLBACK;

六、psql 的实用技巧

6.1 查看表的行数（不使用 COUNT(*)）

-- 快速估算表的行数（基于统计信息，秒出结果）SELECTreltuples::bigintASestimated_rowsFROMpg_classWHERErelname='your_table_name';-- 精确行数（大表会很慢）SELECTCOUNT(*)FROMyour_table_name;

6.2 查看表的磁盘占用

-- 查看表大小SELECTpg_size_pretty(pg_relation_size('your_table'));-- 查看表+索引的总大小SELECTpg_size_pretty(pg_total_relation_size('your_table'));-- 查看数据库中每个表的大小（排名）SELECTrelnameAStable_name,pg_size_pretty(pg_total_relation_size(relid))AStotal_sizeFROMpg_stat_user_tablesORDERBYpg_total_relation_size(relid)DESCLIMIT10;

6.3 查看当前活动的 SQL

-- 查看正在执行的 SQLSELECTpid,state,query,wait_event_type,wait_eventFROMpg_stat_activityWHEREstate!='idle'ORDERBYquery_start;-- 终止正在执行的 SQLSELECTpg_cancel_backend(pid);-- 取消但不终止连接SELECTpg_terminate_backend(pid);-- 终止连接

6.4 查看表的定义

-- 方式1：使用 \d（psql 元命令）\d+your_table-- 方式2：查询系统表SELECTcolumn_name,data_type,is_nullable,column_defaultFROMinformation_schema.columnsWHEREtable_name='your_table'ORDERBYordinal_position;

6.5 生成 SQL 脚本

-- 为所有表生成 VACUUM ANALYZE 命令SELECT'VACUUM ANALYZE '||schemaname||'.'||relname||';'FROMpg_stat_user_tables;-- 为所有表生成 CREATE INDEX CONCURRENTLY 语句（重建索引不锁表）SELECT'REINDEX INDEX CONCURRENTLY '||indexrelname||';'FROMpg_stat_user_indexes;

七、定制 psql

7.1 配置文件

psql 的配置文件.psqlrc（Linux/Mac）或%APPDATA%\postgresql\psqlrc.conf（Windows）在每次启动时自动执行。

推荐配置：

-- ~/.psqlrc-- 历史记录\setHISTSIZE10000\setHISTCONTROL ignoredups-- 分页\pset pager always-- NULL 显示\psetnull'(NULL)'-- 时间格式\pset timezone'Asia/Shanghai'-- 提示符（显示数据库@用户）\setPROMPT1'%n@%/%R%# '-- 安全：禁止危险操作-- \set ON_ERROR_STOP on -- SQL 出错时停止执行（脚本模式推荐）-- 别名\setlist_tables'SELECT table_name FROM information_schema.tables WHERE table_schema = '\''public'\'' ORDER BY table_name;'-- 欢迎信息\echo'Welcome to PostgreSQL! Type \\? for help.'\echo'Current database:':DBNAME

7.2 自定义提示符

-- 简单提示符\setPROMPT1'%n@%/%R%# '-- postgres@mydb=#-- 带事务状态提示符\setPROMPT1'%[%033[1;33m%]%n@%/%R%[%033[0m%]%# '-- 事务中显示 !=, 正常显示 =#-- 带时间的提示符\setPROMPT1'%n@%/%R%# [%t] '

八、psql 与图形化工具的对比

最佳实践：日常简单查询用图形化工具，脚本执行、批量操作、排错用 psql。

九、总结

本文要点

1. psql 是 PG 最强大、最轻量的客户端，功能远超你的想象
2. \d是最常用的元命令，查看表结构、列出对象、查看函数定义
3. \x扩展显示模式在查看宽表时非常有用
4. Tab 自动补全大幅提升输入效率
5. \i执行外部文件是批量操作的首选方式
6. .psqlrc配置文件让你的 psql 个性化
7. psql 适合脚本化、自动化操作，图形化工具适合可视化浏览

psql 元命令速查表

下篇预告

第9篇：《PostgreSQL数据类型全景图——从基础类型到高级类型》

从下一篇开始，我们进入基础进阶篇。数据类型是数据库的基础，PG 的类型系统是主流数据库中最丰富的。了解每种类型的特点和适用场景，是写出高效 SQL 的前提。

上一篇【第07篇】查询的艺术——单表查询、多表关联与子查询完全指南
下一篇【第09篇】PostgreSQL数据类型全景图——从基础类型到高级类型