psql使用入门 | 褚哥说|

文章目录

psql是Postgres的命令行终端，类似于Oracle的sqlplus，提供dba管理的功能。本文介绍其基本使用。

首先，psql的登陆参数比较多，不像oracle都配置在tnsnames里面，命令如下

1	psql -h localhost -p 5432 -d test -U postgres -W

-h表示host的名称，本文连接本机的Postgres，-p表示端口，默认是5432，-d表示数据库名称，-U表示用户名，-W表示需要提示输入密码，这个参数其实比较重要，对dba来说，多输入一次密码可以防止连接错数据库。其实所有psql的参数也可以通过psql --help来看，里面有全部的参数。

使用-c参数可以用一个sql做参数，运行该sql，命令如下

1	psql -h localhost -p 5432 -d test -U postgres -c "SELECT current_time"

也可以用-f参数，以一个文件为参数，运行该文件中的sql，命令如下

1	psql -h localhost -p 5432 -d test -U postgres -f test.sql

在psql中，使用\h可以获得所有SQL的帮助，用\?可以获得所有psql的帮助，\h的后面还可以加参数，例如获取delete的相关帮助可以用\h delete。

psql中一个比较有用的命令是计时，即计算一个SQL运行了多久。命令和输出如下

test=# \timing on
Timing is on.
test=# select current_time;
      timetz
-------------------
 20:33:15.88761+08
(1 row)
Time: 4.301 ms
test=# \timing off
Timing is off.

psql中还有一个非常重要的参数是AUTOCOMMIT，默认情况下，改参数设置为on，表示每个DML语句在完成之后自动提交(commit)，如果该参数设置为off的话，需要手动commit，或rollback。示例如下

test=# create table test(id int);
CREATE TABLE
test=# \set AUTOCOMMIT off
test=# insert into test values(1);
INSERT 0 1
test=# select * from test;
 id
----
  1
(1 row)
test=# rollback;
ROLLBACK
test=# select *from test;
 id
----
(0 rows)
test=# \set AUTOCOMMIT on
test=# ;
test=# insert into test values (1);
INSERT 0 1
test=# rollback;
NOTICE:  there is no transaction in progress
ROLLBACK
test=# select * from test;
 id
----
  1
(1 row)

本文使用的是Postgres 9.3.4版本，psql似乎有一个bug，在AUTOCOMMIT由off变为on的时候，一些情况下\set AUTOCOMMIT on会不成功，我在之后需要加一个;，然后才能进入自动提交状态，否则还是非自动提交模式。事后实验多次发现，该方法也不是每次都成功，在\set AUTOCOMMIT on之后加一个select current_time运行似乎就可以了。而这种情况在使用\timing on的时候不存在，上例是在\timing off状态下的输出。

还有一些好用的命令，比如临时变量，类似bash中的变量赋值，示例如下

test=# \set eav 'EXPLAIN ANALYZE VERBOSE'
test=# :eav select * from test;
                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Seq Scan on public.test  (cost=0.00..34.00 rows=2400 width=4) (actual time=0.027..0.035 rows=1 loops=1)
   Output: id
 Total runtime: 0.160 ms
(3 rows)
Time: 3.266 ms

查看执行计划的命令比较长，可以用一个变量代替之，如上述的eav，之后使用:eav就可以代替一长段文字。

还有控制psql的历史键入的命令的，命令如下，可惜我没实验出来结果

1 2	test=# \set HISTFILE ~/.psql_history- :HOST - :DBNAME test=# \set HISTSIZE 100

还有查看表结构的命令，也非常实用，类似Oracle中的desc，如下

test=# \dt+ pg_catalog.pg_t*
                              List of relations
   Schema   |       Name       | Type  |  Owner   |    Size    | Description
------------+------------------+-------+----------+------------+-------------
 pg_catalog | pg_tablespace    | table | postgres | 40 kB      |
 pg_catalog | pg_trigger       | table | postgres | 8192 bytes |
 pg_catalog | pg_ts_config     | table | postgres | 40 kB      |
 pg_catalog | pg_ts_config_map | table | postgres | 48 kB      |
 pg_catalog | pg_ts_dict       | table | postgres | 40 kB      |
 pg_catalog | pg_ts_parser     | table | postgres | 40 kB      |
 pg_catalog | pg_ts_template   | table | postgres | 40 kB      |
 pg_catalog | pg_type          | table | postgres | 96 kB      |
(8 rows)
test=# \d+ pg_ts_dict
                       Table "pg_catalog.pg_ts_dict"
     Column     | Type | Modifiers | Storage  | Stats target | Description
----------------+------+-----------+----------+--------------+-------------
 dictname       | name | not null  | plain    |              |
 dictnamespace  | oid  | not null  | plain    |              |
 dictowner      | oid  | not null  | plain    |              |
 dicttemplate   | oid  | not null  | plain    |              |
 dictinitoption | text |           | extended |              |
Indexes:
    "pg_ts_dict_dictname_index" UNIQUE, btree (dictname, dictnamespace)
    "pg_ts_dict_oid_index" UNIQUE, btree (oid)
Has OIDs: yes
test=# \d
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | test | table | postgres
(1 row)
test=# \dt t*
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | test | table | postgres
(1 row)
test=# \d test
     Table "public.test"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer |

\dt+和\d+可以列出符合条件的系统表和系统表的表结构，\dt和\d可以列出符合条件的用户表和用户表的表结构。

\copy可以将数据文件导出成文本文件形式，简单的示例如下

test=# \copy test to '/tmp/test.txt'
test=# \h copy
Command:     COPY
Description: copy data between a file and a table
Syntax:
COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]
COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
    TO { 'filename' | PROGRAM 'command' | STDOUT }
    [ [ WITH ] ( option [, ...] ) ]
where option can be one of:
    FORMAT format_name
    OIDS [ boolean ]
    FREEZE [ boolean ]
    DELIMITER 'delimiter_character'
    NULL 'null_string'
    HEADER [ boolean ]
    QUOTE 'quote_character'
    ESCAPE 'escape_character'
    FORCE_QUOTE { ( column_name [, ...] ) | * }
    FORCE_NOT_NULL ( column_name [, ...] )
    ENCODING 'encoding_name'

其实\copy命令有很多参数，可以从help中看。

最后，文中的实验结果的图片，统一贴在下面