上一篇【第07篇】查询的艺术——单表查询、多表关联与子查询完全指南下一篇【第09篇】PostgreSQL数据类型全景图——从基础类型到高级类型很多人觉得数据库管理工具就是 DBeaver 或 pgAdmin但其实 PostgreSQL 自带的 psql 才是最强大、最高效的数据库交互工具。本文带你全面掌握 psql 的核心功能。写在前面如果你用 PostgreSQL一定绕不开 psql。它是 PG 自带的命令行客户端虽然界面朴素但功能极其强大——元命令、自动补全、输出格式化、快捷键、变量替换……这些功能让 psql 的操作效率远超图形化工具。很多老 DBA 的习惯是简单查询用图形化工具复杂的操作全部用 psql。读完这篇文章你就能理解为什么。一、psql 简介1.1 什么是 psqlpsql 是 PostgreSQL 的官方命令行客户端随 PG 安装包一起提供。它不仅可以执行 SQL 语句还提供了大量内置的元命令以\开头来管理数据库。1.2 启动 psql# 最基本的连接方式psql-Upostgres-dpostgres# 指定主机、端口、用户、数据库psql-h192.168.1.100-p5432-Umyuser-dmydb# 使用连接字符串psqlhost192.168.1.100 port5432 dbnamemydb usermyuser passwordmypwd# 使用 Unix 域套接字本地连接最快psql-Upostgres# 直接执行 SQL 语句psql-cSELECT version();# 执行 SQL 文件psql-f/path/to/script.sql# 从 stdin 管道输入catscript.sql|psql1.3 psql 的常用启动参数参数说明示例-h数据库服务器地址-h 192.168.1.100-p端口号-p 5432-U用户名-U postgres-d数据库名-d mydb-c执行一条 SQL 后退出-c SELECT 1-f执行 SQL 文件-f script.sql-l列出所有数据库psql -l-W强制提示输入密码-W-1在单个事务中执行全成功或全回滚-1 -f script.sql二、核心元命令\命令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.-- 断开连接\q2.3 表和对象浏览-- 列出当前数据库的所有表\dt-- 只显示普通表\dt-- 显示表大小等额外信息\dtpublic.*-- 显示 public 模式下的表\dt schema_name.*-- 显示指定模式下的表-- 列出所有对象类型\d-- 列出表、视图、序列、索引\dv-- 列出视图\di-- 列出索引\ds-- 列出序列\dS-- 列出系统表\dm-- 列出物化视图\dn-- 列出所有模式schema\df-- 列出所有函数\dT-- 列出所有数据类型\db-- 列出所有表空间-- 查看表结构最常用\d table_name \dtable_name-- 显示更多信息大小、描述等\d student \dstudent-- 查看视图定义\dview_name-- 查看函数定义\dffunction_name-- 查看索引信息\diindex_name2.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 HEADER2.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 自定义变量-- 设置变量\setmyvarhello world\echo :myvar-- 输出hello world-- 在 SQL 中使用变量\settablenameusersSELECT*FROM:tablenameWHEREid1;-- 变量中包含特殊字符\setmyquerySELECT * FROM users WHERE name \test\\:myquery3.3 变量在脚本中的妙用# 在 shell 脚本中使用 psql 变量psql-vtable_nameusers-vmin_age18EOF SELECT * FROM :table_name WHERE age :min_age; EOF四、快捷键4.1 编辑快捷键在 psql 命令行中可以使用以下快捷键快捷键功能CtrlA光标移到行首CtrlE光标移到行尾CtrlB光标左移CtrlF光标右移Backspace删除前一个字符CtrlD删除当前字符CtrlK删除到行尾CtrlU删除到行首CtrlW删除前一个单词CtrlL清屏4.2 历史快捷键快捷键功能上/下箭头浏览历史命令CtrlR反向搜索历史命令CtrlP上一条命令CtrlN下一条命令4.3 自动补全psql 支持Tab 键自动补全需要 readline 库支持-- 输入部分命令后按 TabSELTab→SELECT\dtTab→ 列出以 dt 开头的元命令 \dTabTab→ 列出所有 \d 开头的元命令-- SQL 关键字补全CREATETABTab→CREATETABLE-- 表名和列名补全SELECT*FROMstuTab→SELECT*FROMstudentSELECTno,naTabFROMstudent →SELECTno,nameFROMstudent五、事务控制-- psql 默认每条语句自动提交-- 如果需要手动控制事务使用以下方式-- 开启事务BEGIN;-- 执行多条 SQLINSERTINTOusers(name)VALUES(test1);INSERTINTOusers(name)VALUES(test2);UPDATEusersSETstatusactiveWHEREnameLIKEtest%;-- 查看中间结果SELECT*FROMusersWHEREnameLIKEtest%;-- 确认无误提交COMMIT;-- 或者回滚-- ROLLBACK;六、psql 的实用技巧6.1 查看表的行数不使用 COUNT(*)-- 快速估算表的行数基于统计信息秒出结果SELECTreltuples::bigintASestimated_rowsFROMpg_classWHERErelnameyour_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!idleORDERBYquery_start;-- 终止正在执行的 SQLSELECTpg_cancel_backend(pid);-- 取消但不终止连接SELECTpg_terminate_backend(pid);-- 终止连接6.4 查看表的定义-- 方式1使用 \dpsql 元命令\dyour_table-- 方式2查询系统表SELECTcolumn_name,data_type,is_nullable,column_defaultFROMinformation_schema.columnsWHEREtable_nameyour_tableORDERBYordinal_position;6.5 生成 SQL 脚本-- 为所有表生成 VACUUM ANALYZE 命令SELECTVACUUM ANALYZE ||schemaname||.||relname||;FROMpg_stat_user_tables;-- 为所有表生成 CREATE INDEX CONCURRENTLY 语句重建索引不锁表SELECTREINDEX INDEX CONCURRENTLY ||indexrelname||;FROMpg_stat_user_indexes;七、定制 psql7.1 配置文件psql 的配置文件.psqlrcLinux/Mac或%APPDATA%\postgresql\psqlrc.confWindows在每次启动时自动执行。推荐配置-- ~/.psqlrc-- 历史记录\setHISTSIZE10000\setHISTCONTROL ignoredups-- 分页\pset pager always-- NULL 显示\psetnull(NULL)-- 时间格式\pset timezoneAsia/Shanghai-- 提示符显示数据库用户\setPROMPT1%n%/%R%# -- 安全禁止危险操作-- \set ON_ERROR_STOP on -- SQL 出错时停止执行脚本模式推荐-- 别名\setlist_tablesSELECT table_name FROM information_schema.tables WHERE table_schema \public\ ORDER BY table_name;-- 欢迎信息\echoWelcome to PostgreSQL! Type \\? for help.\echoCurrent database::DBNAME7.2 自定义提示符-- 简单提示符\setPROMPT1%n%/%R%# -- postgresmydb#-- 带事务状态提示符\setPROMPT1%[%033[1;33m%]%n%/%R%[%033[0m%]%# -- 事务中显示 !, 正常显示 #-- 带时间的提示符\setPROMPT1%n%/%R%# [%t] 八、psql 与图形化工具的对比功能psqlpgAdmin / DBeaver执行速度⚡ 极快较慢GUI 渲染开销远程操作SSH 隧道即可需要配置连接批量操作脚本一键执行需要手动输出格式高度可定制固定格式自动补全✅ SQL 表名列名✅ 部分支持可视化设计❌✅ ER 图、表设计资源占用极低较高学习曲线中等较低自动化/脚本✅ 完美支持❌ 困难最佳实践日常简单查询用图形化工具脚本执行、批量操作、排错用 psql。九、总结本文要点psql 是 PG 最强大、最轻量的客户端功能远超你的想象\d是最常用的元命令查看表结构、列出对象、查看函数定义\x扩展显示模式在查看宽表时非常有用Tab 自动补全大幅提升输入效率\i执行外部文件是批量操作的首选方式.psqlrc配置文件让你的 psql 个性化psql 适合脚本化、自动化操作图形化工具适合可视化浏览psql 元命令速查表命令功能\l列出所有数据库\c dbname切换数据库\dt列出所有表\d table查看表结构\dv列出所有视图\di列出所有索引\df列出所有函数\dn列出所有模式\x切换扩展显示\q退出 psql\hSQL 帮助\?元命令帮助\i file执行 SQL 文件\o file输出重定向\timing显示 SQL 执行时间\set var val设置变量下篇预告第9篇《PostgreSQL数据类型全景图——从基础类型到高级类型》从下一篇开始我们进入基础进阶篇。数据类型是数据库的基础PG 的类型系统是主流数据库中最丰富的。了解每种类型的特点和适用场景是写出高效 SQL 的前提。上一篇【第07篇】查询的艺术——单表查询、多表关联与子查询完全指南下一篇【第09篇】PostgreSQL数据类型全景图——从基础类型到高级类型