Postgresql psql工具

psql介绍

psql是PostgreSQL中的一个命令行交互式客户端工具，类似Oracle中的命令行工具sqlplus，它允许用户交互地键入SQL语句或命令，然后将其发送给PostgreSQL服务器，再显示SQL语句或命令的结果。另外，所输入内容还可以来自一个文件。此外，它还提供了一些快捷命令和多种类似Shell的特性来实现书写脚本，以及对大量任务的自动化操作。虽然psql的功能与sqlplus差不多，但使用起来远比sqlplus简便，如psq工具可以用上下方向键把上一条和下一条SQL命令翻出来，还有单击Tab键自动补全的强大功能。

psql 的简单使用

如果已建好数据库，可以直接输入“psq|”进入命令交互输入模式：
osdba@osdba-laptop：~$ psql
psql （9.0.3）
Type “help“ for help。
osdba=#
进入命令交互输入模式后会显示psql版本，然后出现提示符，可以在此提示符下输入标准的SQL命令，也可以输入psq|工具特有的快捷命令，这些快捷命令都是以斜杠“\” 开头的。
为什么不需要输入用户名和密码？安装PostgresQL数据库时，会创建一个与初始化数据库时的操作系统用户同名的数据库用户，这个用户是数据库的超级用户，在此OS用户下登录数据库时，因为执行的是操作系统认证，所以是不需要用户名和密码的用户也可以通过修改pg_hba。conf文件来要求用户输入密码。
当然，psqI也支持直接使用命令行参数查询信息和执行SQL，这种非交互模式与使用一般的Linux命令没有区别，如使用“psql -”命令可以查看数据库：
osdba@osdba-work：~$ psql -l
也可以进入psqI的命令交互输入模式使用“V”命令查看有哪些数据库，与使用上面的“psql-”命令得到的结果是相同的。
上面的查询结果中有一个叫“postgres”的数据库，这是默认postgreSQL安装完成后就有的一个数据库，还有两个模板数据库：template0和template1。当用户创建数据库时，默认是从模板数据库“template1”克隆来的，所以通常我们可以定制template1数据库中的内容，如向template1中添加一些表后函数，这样后续创建的数据库就会继承 template1中的内容，也会拥有这些表和函数。而templateO是一个最简化的模板库，如果创建数据库时明确指定从此数据库克隆，将创建出一个最简化的数据库。
下面演示交互模式下的使用方法。
使用“\d”命令查看表的示例如下：
osdba=# create table t（id int primary key，name varchar（40））；
还可以使用SQL语句“CREATE DATABASExxx”创建用户数据库，下面是创建 testdb数据库的SQL语句：
osdba=# CREATE DATABASE testdb；
CREATE DATABASE
然后使用“\c testdb”命令连接到testdb数据库上：
osdba=#\c testdb；
You are now connected to database “testdb“ as user “osdba“
testdb=#
下面介绍psql连接数据库的常用的方法，命令格式如下：
psql -h -p＜端口>［数据库名称］［用户名称］ 示例如下：
Lpostgres@db1~J$ psql -h 192.168.56.11-p 5432 testdb postgres
psql （9.0.3）
Type “help“ for help。
testdb=#
其中-h指定要连接的数据库所在的主机名或IP地址，一p指定连接的数据库端口，最后两个参数分别是数据库名和用户名。
这些连接参数也可以通过环境变量指定，示例如下：
export PGDATABASE=testdb
export PGHOST=192.168.56.11
export PGPORT=5432
export PGUSER=postgres
然后运行psql， 其运行结果与“psql -h 192.168.56.11-p 5432 testdb postgres”的运行结果相同。

psql 常用命令

\h命令

使用psq|工具需要记住的第一个命令是“\h”，该命令用于查询SQL语句的语法，如我们不知道如何用SQL语句创建用户，就可以执行“\h create user”命令来查询。

\d命令

“\d”命令的格式如下：
\d ［pattern］
\d［pattern ］+
该命令将显示每个匹配“pattern” （表、视图、索引、序列）的信息，包括对象中所有的列、各列的数据类型、表空间（如果不是默认的）和所有特殊属性（诸如“NOT NULL”或默认值等）等。唯一约束相关的索引、规则、约束、触发器也同样会显示出来。如果关系是一个视图，还会显示视图的定义（“匹配模式”将在下面定义）。下面来看看该命令的具体用法。
1）如果“\d”命令后什么都不带，将列出当前数据库中的所有表，示例如下：
osdba=#\d
2）“\d”命令后面跟一个表名，表示显示这个表的结构定义，示例如下：
osdba=#\dt
3）“\d”命令也可以用于显示索引信息，示例如下：
osdba=#\d t_pkey
4）“\d”命令后面的表名或索引名中也可以使用通配符，如“*”或“？”等，示例如下：
osdba=#\d x？
5）使用“\d+”命令可以显示比“\d”命令的执行结果更详细的信息，除了前面介绍的信息，还会显示所有与表的列关联的注释，以及表中出现的OID。示例如下：
osdba=#\d+t
6） 匹配不同对象类型的“\d”命令如下：
•如果只想显示匹配的表，可以使用“\dt”命令。
•如果只想显示索引，可以使用“\d”命令。
•如果只想显示序列，可以使用“\ds”命令。
•如果只想显示视图，可以使用“\dv”命令。
•如果想显示函数，可以使用“\df”命令。
7） 如果想显示执行SQL语句的时间，可以用“\timing”命令，示例如下：
osdba=#\timing on
8）要想列出所有的schema，可以使用“\dn”命令，示例如下：
osdba=#\dn
9）要想显示所有的表空间，可以用“\db”命令，示例如下：
osdba=#\db
实际上，PostgreSQL中的表空间对应一个目录，放在这个表空间中的表，就是把表的数据文件放到该表空间下。
10）要想列出数据库中的所有角色或用户，可以使用“\du”或“ldg”命令，示例如下：
osdba=#\dg
“\du”和“\dg”命令等价。原因是，在PostgreSQL数据库中，用户和角色是不分的。
11）“\dp”或“\z”命令用于显示表的权限分配情况，示例如下：
osdba=#\dpt

指定客户端字符集的命令

当客户端的字符编码与服务器不一致时，可能会出现乱码，可以使用“\encoding” 命令指定客户端的字符编码，如使用“\encoding gbk；”命令设置客户端的字符编码为 “gbk”；使用“\encoding utf8；”命令设置客户端的字符编码为“utf8” 。

格式化输出的\pset命令

“\pset”命令的语法如下：
pset ［option ［value］］
根据命令后面“option”和“value”的不同可以设置很多种不同的输出格式，这里只介绍一些常用的用法。
默认情况下，psql中执行SQL语句后输出的内容是只有内边框的表格：
osdba=# select * from class；
如果要像MySQL中一样输出带有内外边框的表格内容，可以用命令“\pset boder 2” 来实现，示例如下：
osdba=#\pset border 2
当然也可以用“\pset boder O”命令输出不带任何边框的内容，示例如下：
osdba=#\pset border 0
综上所述， “pset“命令设置边框的用法如下。
•\pset border 0：表示输出内容无边框。
•\pset border 1：表示输出内容只有内边框。
•\pset border 2：表示输出内容内外都有边框。
psq|中默认的输出格式是“\pset border 1’。
不管输出的内容加不加边框，内容本身都是对齐的，是为增强数据的可读性而专门格式化过的，而有时我们需要把命令的结果输出为其他程序可以读取的文件，如以逗号分隔或以Tab分隔的文本文件，这时就需要用到“\pset format unaligned”命令了，示例如下：
osdba=#\pset format unaligned
默认分隔符是“Y”，我们可以用命令“\pset fieldsep”来设置分隔符，如改成Tab分隔符的方法如下：
osdba=#\pset fieldsep“\t
实际使用时，我们需要把SQL命令输出到一个文件中，而不是屏幕上，这时可以用 “\o”命令指定一个文件，然后再执行上面的SQL命令，执行结果就会输出到这个文件中， 示例如下：
osdba=#\pset format unaligned

\x命令

使用“\x”命令可以把按行展示的数据变成按列展示，示例如下：
osdba=#\×
如果数据行太长出现折行，就可以使用这里介绍的“\x”命令将其拆分为多行显示。
这与MySQL中命令后加“\G”的功能类似。

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

命令“\i<文件名>”用于执行存储在外部文件中的SQL语句或命令。示例如下：
osdba=#\×
Expanded display （expanded） is on。
osdba=# \i getrunsql
当然也可以在psq|命令行中加上“_f”来执行SQL脚本文件中的命令，示例如下：
osdba@osdba-work：~$ psql -x -f getrunsql
其中命令行参数“ ”的作用相当于在psql交互模式下运行“\x”命令。

编辑命令

编辑命令“\e”可以用于编辑文件，也可用于编辑系统中已存在的函数或视图定义， 下面来举例说明此命令的使用方法。
输入“\e”命令后会调用一个编辑器，在Linux下通常是Vi，当“\e”命令不带任何参数时则是生成一个临时文件，前面执行的最后一条命令会出现在临时文件中，当编辑完成后退出编辑器并回到psql中时会立即执行该命令：
osdba=#\e
这里输入“\e”后，会进入Vi编辑器，退出Vi编辑器后就会执行Vi中编辑的内容，然后下面就显示出执行的内容
在上面的操作中，我们在Vi中输入的内容为“‘select * from class where no=1；” ’ 当退出Vi编辑器后，就会执行SQL语句“select * from class where no=1；”，这条SQL 语句的内容在psql中是看不到的。
“\e后面也可以指定一个文件名，但要求这个文件必须存在，否则会报错：
osdba=# e 1.sgl
1.sq ： No such file or directory
可以用“\ef”命令编辑一个函数的定义，如果“\ef”后面不跟任何参数，则会出现一个编辑函数的模板
如果“\ef”后面跟一个函数名，则函数定义的内容会出现在Vi编辑器中，当编辑完成后按“wq：”保存并退出，再输入“，”就会执行所创建函数的SQL语句。
同样输入“\ev”且后面不跟任何参数时，在Vi中会出现一个创建视图的模板
然后用户就可以在Vi中编辑这个创建视图的SQL语句，编辑完成后，保存并退出， 再输入分号“；”，就会执行所创建视图的SQL语句。
也可以编辑已存在的视图的定义，只需在“\ev”命令后面跟视图的名称即可。
“\ef”和“\ev”命令可以用于查看函数或视图的定义，当然用户需要注意，退出V后， 要在psq|中输入“\reset”来清除psq的命令缓冲区，防止误执行创建函数和视图的SQL 语句，示例如下：
postgres=#\ev vm_class 在这里进入Vi后，在Vi中用“：q“退出
No changes
postgres-#\reset 在这里不要忘了输入“\reset”“命令清除psql缓冲区

输出信息的\echo 命令

“\echo”命令用于输出一行信息，示例如下：
osdba=#\echo hello word
hello word
此命令通常用于在使用。sq|脚本的文件中输出提示信息。

其他命令

更多其他的命令可以用“\？”命令来显示，示例如下：
osdba=#\？

psql的使用技巧

历史命令与补全功能

可以使用上下方向键把以前使用过的命令或SQL语句调出来，连续单击两次Tab键表示把命令补全或给出输入提示。

自动提交技巧

需要特别注意的是，在psq|中事务是自动提交的，比如，执行完一条DELETE或 UPDATE语句后，事务就会自动提交，如果不想让事务自动提交，方法有两种。

方法一：运行“begin；”命令，然后执行DML语句，最后再执行commit或rollback语句，示例如下。
osdba=# begin；
osdba=# update x1 set name=‘xxxxx’ where id=1;
UPDATE 1
osdba=# select * from x1;
osdba=# rollback;
ROLLBACK
osdba=# select * from x1;
方法二：直接使用psq|中的命令关闭自动提交功能。
\set AUTOCOMMIT off
这个命令中的“AUTOCOMMIT”是大写的，不能使用小写，如果使用小写，虽不会报错，但会导致关闭自动提交的操作无效。

如何得到psql中快捷命令执行的实际SQL

在启动psq的命令行中加上“-E”参数，就可以把psal中各种以“\”开头的命令执行的实际SQL语句打印出来，示例如下：
osdba@osdba-laptop：~$ psql -E postgres
如果在已运行的psgl中显示了某个命令实际执行的SQL语句后又想关闭此功能，该怎么办？这时可以使用“\set ECHO_HIDDEN onloff”命令，示例如下：
osdba@osdba-laptop：~$ psql postgres