psql 介绍

psql 是 PostgreSQL 中的一个交互式命令行工具，类似 Oracle 的 sqlplus，它允许用户交互式输入 SQL 语句或命令，然后发送给 PostgreSQL 服务器，再显示结果。 此外，psql 还有大量类似 shell 的特性来编写脚本，实现自动化操作。

在安装 postgresql 的时候，会创建一个与初始化数据库时的操作系统同名的数据库用户，这个用户是这个数据库的超级用户。 因此，在 OS 用户下登陆数据库时，会执行操作系统认证，因此无需用户名和密码，也可以通过修改 pg_hba.conf 文件来要求用户输入密码。

psql 也支持使用命令行参数来查询信息和执行 SQL，这种非交互模式与 linux 命令就没有太大区别了。 例如使用 psql -l 查看数据库，当然也可以通过命令进入数据库后，输出 \l 命令查看有哪些数据库。 默认会有一个叫 postgres 的数据库，这是默认安装后就有的一个数据库。 还有两个模板数据库，template0 和 template1。

当用户创建创建数据库时，是从模板数据库 template1 克隆来的，因此通常可以订制 template1 中的内容，例如添加一些表和函数，这样后续创建的数据库也会有这些表和函数。 template0 是一个最简化的数据库，如果指明从此数据库克隆，将创建出一个最简化的数据库。

使用 \d 查看有哪些表，使用 \c 连接到某数据库。连接命令格式如下：

psql -h <hostname or ip> -p <port> [数据库名称] [用户名称]

也可以通过环境变量指定

export PGDATABASE=db_name
export PGHOST=ip
export PGPORT=port
export PGUSER=postgres

psql 常用命令

  • \h 命令查询 SQL 语法

    \h CREATE USER

  • \d [ pattern ]: 该命令将显示每个匹配 pattern (表、视图、索引、序列) 的信息，包括对象中的所有列、各列的数据类型、表空间和所有特殊属性等。

    • 如果 \d 后面什么都不带，会显示当前数据库中的所有表
    • 如果 \d 后面根一个表名，会显示这个表的结构定义
    • \d 也可以显示索引信息
    • \d 后面的表明或索引名也可以使用通配符，如 * 或 ?
    • \d+ 可以显示更加详细的信息
    • \dt 只显示匹配的列表；\di 只显示索引；\ds 只显示序列；\dv 只显示视图；\df 只显示函数
    • \timing 让执行的 SQL 语句显示消耗的时间
    • \dn 列出所有的 schema
    • db 显示所有表空间
    • \du 或 \dg 列出数据库中所有角色或用户
    • \dp 或 \z 显示权限分配情况

  • 指定客户端字符集
    当客户端的字符编码与服务器不一致时，可能出现乱码，可以使用 \encoding 命令指定客户端的字符编码。 例如 \encoding bgk; 命令设置客户端的字符串编码为 gbk，\encoding utf8; 命令则设置编码为 utf8。

  • 格式化输出命令
    \pset 命令格式化输出

    \pset [option [value]]

    命令后的 option 和 value 参数可以设置多种不同的输出格式，下面介绍一些常见用法：

    • \pset border 2 输出和 MySQL 一样的外边框的内容，如果要修改回不带边框的内容，使用命令 \pset border 0，1 则是只有内边框
    • \pset fieldsep 设置分隔符（默认为 |）
    • \t 删除头信息
    • \o <filename> 将 SQL 命令输出到文件，而不是终端
    • \x 将按行展示的数据改成按列展示，如果数据太长，就可以使用 \x 命令分为多行显示，类似 MySQL 的 \G 功能

  • 执行存储在外部文件中的 SQL 命令

    • \i <filename> 用于执行存储在外部文件中的 SQL 命令
    • 也可以在 psql 命令行通过 -f 参数实现：psql -f <filename>

  • 编辑命令
    \e 可以用于编辑文件，可以用于编辑系统中已存在的函数或视图定义。 该命令会调用一个编辑器，通常是 vi / vim，不带任何参数时会打开一个临时文件。 在里面输出 SQl 语句，退出后就会执行该命令，但在 psql 中是看不到的。 \e 也可以指定一个文件名，但要求这个文件必须存在，否则会报错。

    可以使用 \ef 命令编辑一个函数的定义，如果后面参数为空会出现一个编辑函数的模板。 如果后面跟一个文件名，则函数定义的内容会出现在编辑器中，:wq 退出后，在输入 ; 就会执行创建函数的 SQL 指令。

    同样输入 \ev 且不带任何参数时，会出现一个创建视图的模板。编辑后退出，输入 ; 执行视图 SQL 指令。

    最后注意，退出后需要在 psql 中输入 \reset 来清除 psql 的命令缓冲区，防止误执行创建函数和视图的 SQL 语句。

  • 输出信息命令
    \echo 命令用于输出一行信息，例如：

    \echo hello world

    此命令常用语 .sql 脚本文件中输出提示信息。

  • 其他命令
    更多其他命令可以使用 /? 命令来显示

对于上方命令，如果需要获取对应的 SQL 命令，可以在启动时加上 -E 参数：

psql -E postgresql