4.4　psql的使用技巧

本节将介绍psql最常用的使用技巧，如历史命令和补全技巧、关闭自动提交功能、获得快捷命令实际的SQL，以便学习数据库的系统表等。

4.4.1　历史命令与补全功能

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

---

osdba=# \d ←这里连续单击了两次Tab键
\d    \db   \dC   \dD    \deu   \df   \dFd   \dFt  \di   \dn   \dp     \ds   \dt   \du
\da   \dc   \dd   \des   \dew   \dF   \dFp   \dg   \dl   \do   \drds   \dS   \dT   \dv
osdba=# \d t←这里连续单击了两次Tab键
t        t2       t2_pkey  t_pkey   
osdba=# \d x←这里连续单击了两次Tab键
x1       x1_pkey  x2       x2_pkey
osdba=# \d x

---

4.4.2　自动提交技巧

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

方法一： 运行“begin;”命令，然后执行DML语句，最后再执行commit或rollback语句，示例如下。

---

osdba=# begin;
BEGIN
osdba=# update x1 set name='xxxxx' where id=1;
UPDATE 1
osdba=# select * from x1;
 id |  name  
----+--------
  2 | bbbbbb
  3 | cccccc
  1 | xxxxx
(3 rows)

osdba=# rollback;
ROLLBACK
osdba=# select * from x1;
 id |  name  
----+--------
  1 | aaaaaa
  2 | bbbbbb
  3 | cccccc
(3 rows)

---

方法二： 直接使用psql中的命令关闭自动提交功能。

---

\set AUTOCOMMIT off

---

这个命令中的“AUTOCOMMIT”是大写的，不能使用小写，如果使用小写，虽不会报错，但会导致关闭自动提交的操作无效。

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

在启动psql的命令行中加上“-E”参数，就可以把psql中各种以“\”开头的命令执行的实际SQL语句打印出来，示例如下：

---

osdba@osdba-laptop:~$ psql -E postgres
psql (9.2.4)
Type "help" for help.

postgres=# \d
********* QUERY **********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','S','f','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

         List of relations
 Schema |   Name    | Type  | Owner 
--------+-----------+-------+-------
 public | testtable | table | osdba
(1 row)

postgres=# \d testtable*
********* QUERY **********
SELECT c.oid,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(testtable.*)$'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
**************************

********* QUERY **********
SEECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, c.relhastriggers, c.relhasoids, '', c.reltablespace, CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence
FROM pg_catalog.pg_class c
  LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
WHERE c.oid = '16390';
**************************

********* QUERY **********
SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod),
  (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
    FROM pg_catalog.pg_attrdef d
    WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
  a.attnotnull, a.attnum,
  (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
    WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation,
  NULL AS indexdef,
  NULL AS attfdwoptions
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '16390' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;
**************************

********* QUERY **********
SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '16390' ORDER BY inhseqno;
**************************

********* QUERY **********
SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '16390' ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;
**************************

    Table "public.testtable"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | 
 note   | text    | 

postgres=#

---

如果在已运行的psql中显示了某个命令实际执行的SQL语句后又想关闭此功能，该怎么办？这时可以使用“\set ECHO_HIDDEN on|off”命令，示例如下：

---

osdba@osdba-laptop:~$ psql postgres
psql (9.2.4)
Type "help" for help.

postgres=# \dn
List of schemas
  Name  | Owner 
--------+-------
 public | osdba
(1 row)

postgres=# \set ECHO_HIDDEN on
postgres=# \dn
********* QUERY **********
SELECT n.nspname AS "Name",
  pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
ORDER BY 1;
**************************

List of schemas
  Name  | Owner 
--------+-------
 public | osdba
(1 row)

postgres=# \set ECHO_HIDDEN off
postgres=# \dn
List of schemas
  Name  | Owner 
--------+-------
 public | osdba
(1 row)

---

通过分析这个方法输出的SQL语句，可以让我们快速学习PostgreSQL的系统表原理。 HdO0sa7xK8aXi/Hlb0+ZQpwDfD7yMNDlGSvflWpSfHs3Kh9sXWwBfYeR4J00DLn5