psql使用介绍

2019-01-19 约 2025 字预计阅读 5 分钟次阅读

psql介绍

psql是PostgreSQL中的一个命令行交互式客户端工具，类似Oracle中的命令行工具sqlplus，但它使用起来远比sqlplus方便。

在postgres用户下可以直接输入psql进入到数据库：

1
2
3
4
5


$ psql
psql (10.5 (Debian 10.5-1.pgdg90+1))
Type "help" for help.

postgres=#

为什么不需要输入用户名和密码呢?因为在安装PostgreSQL数据库时，会创建一个数据库超级用户，这个用户与初始化数据库的操作系统用户。用这个用户登录数据库执行的是操作系统认证，所以不需要用户名和密码。

PostgreSQL安装好后，默认会有一个叫postgres的数据库，还有两个模板数据库template1和template0。当用户创建数据库时，默认是从模板数据库template1克隆出来的。通常可以定制template1数据库中的内容，如往template1中添加一些表和函数，这样后续创建的数据库就会继承这些表和函数。而template0是一个最简化的模板库，创建数据库时，如果明确指定从此数据库中继承，将创建出一个最简化的数据库。

常用斜杠命令

`\l`命令

\l命令查看数据库，相当于psql -l

1
2
3
4
5
6
7
8
9


postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------|----------|----------|------------|------------|-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres

\c命令

\c命令用于连接指定数据库，语法如下：

1

\c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}

相当于psql -h hostname -p port [db_name][username]，比如：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


$ psql -h localhost -p 5432 postgres postgres
psql (10.5 (Debian 10.5-1.pgdg90+1))
Type "help" for help.

postgres=# \c template1;
You are now connected to database "template1" as user "postgres".
postgres=# \c postgres gpadmin 192.168.205.135 5432
Password for user gpadmin:
psql (10.5 (Debian 10.5-1.pgdg90+1), server 8.3.23)
You are now connected to database "postgres" as user "gpadmin" on host "192.168.205.135" at port "5432".

也可以将这些连接参数设置为环境变量：

1
2
3
4


export PGDATABASE=postgres
export PGHOST=192.168.56.11
export PGPORT=5432
export PGUSER=postgres

\d命令

\d命令格式如下

1
2


\d [pattern]
\d [pattern]+

该命令将显示每个匹配关系（表、视图、索引、序列）的信息。

如果\d命令什么都不带，将列出当前数据库中的所有表
\d后面跟一个对象名，显示这个对象的结构定义
\d后面也可以跟通配符如*或?
d+命令显示比\d命令更详细的信息
匹配不同对象类型的\d命令
- \dt命令只显示匹配的表
- \di命令只显示索引
- \ds命令只显示序列
- \dv命令只显示视图
- \df命令只显示函数
\dn命令列出所有的schema
\db命令显示所有的表空间
\du或\dg命令列出数据库中的所有角色或用户
\dp或\z命令显示表的权限分配情况

\timing命令

\timing命令显示SQL执行耗时

1
2


postgres=# \timing
Timing is on.

\encoding命令

\encoding命令设置客户端字符编码，比如utf8，gbk等。

1

postgres=# \encoding utf8;

\pset命令

\pset命令设置输出的格式，具体如下：

\pset border 0：表示输出内容无边框
\pset border 1：表示边框只在内部
\pset border 2：表示内外都有边框

\x命令

\x命令单行展示表中每行的每列数据，等价于psql -x

\i命令

\i sql_file执行存储在文件中sql语句，等价于psql -f sql_file

\echo命令

\echo命令输出一行信息，常用于SQL脚本文件中输出提示信息。

1
2


postgres=# \echo hello world
hello world

\set命令

\set [NAME [VALUE]] 命令设置内部变量，如果没有指定变量名称，则显示所有变量的值。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30


postgres=# \set
AUTOCOMMIT = 'off'
COMP_KEYWORD_CASE = 'preserve-upper'
DBNAME = 'postgres'
ECHO = 'none'
ECHO_HIDDEN = 'off'
ENCODING = 'SQL_ASCII'
FETCH_COUNT = '0'
HISTCONTROL = 'none'
HISTSIZE = '500'
HOST = '/var/run/postgresql'
IGNOREEOF = '0'
LASTOID = '0'
ON_ERROR_ROLLBACK = 'off'
ON_ERROR_STOP = 'off'
PORT = '5432'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
QUIET = 'off'
SERVER_VERSION_NAME = '10.5 (Debian 10.5-1.pgdg90+1)'
SERVER_VERSION_NUM = '100005'
SHOW_CONTEXT = 'errors'
SINGLELINE = 'off'
SINGLESTEP = 'off'
USER = 'postgres'
VERBOSITY = 'default'
VERSION = 'PostgreSQL 10.5 (Debian 10.5-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit'
VERSION_NAME = '10.5 (Debian 10.5-1.pgdg90+1)'
VERSION_NUM = '100005'

\h命令

\h命令查看SQL命令的语法，*查看所有命令的语法：

1
2
3
4
5


template1=# \h drop table
Command:     DROP TABLE
Description: remove a table
Syntax:
DROP TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

?命令

\?帮助命令

一些使用技巧

历史命令与补全的功能

可以使用上下键选择之前使用过的命令或SQL语句，ctrl+r快捷键可以搜索历史命令。连续按两次tab键表示把命令补全或给出提示输入。

1
2
3
4
5


postgres=# \d然后连续按2次tab
\d     \dE    \dFt   \dRs   \db    \des   \df    \dm    \drds  \dv
\dA    \dF    \dL    \dS    \dc    \det   \dg    \dn    \ds    \dx
\dC    \dFd   \dO    \dT    \dd    \deu   \di    \do    \dt    \dy
\dD    \dFp   \dRp   \da    \ddp   \dew   \dl    \dp    \du

自动提交的技巧

在psql中事务是自动提交的。比如执行完一条delete或update语句后，事务就自动提交了。如果不想自动提交，有两种方法：

运行begin命令，然后执行DML语句，最后再执行commit或rollback语句。
使用\set AUTOCOMMIT off命令关闭自动提交，注意AUTOCOMMIT是大写的。

显示psql命令实际执行的SQL语句

变量ECHO_HIDDEN控制斜杠命令是否显示执行的SQL语句，\set ECHO_HIDDEN on显示执行的SQL语句，相当于psql -E。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28


postgres=# \set ECHO_HIDDEN on
postgres=# \d
********* QUERY **********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN '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','p','v','m','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 | bit_test    | table | postgres
 public | class       | table | postgres
 public | date_test   | table | postgres
 public | duty        | table | postgres
 public | score       | table | postgres
 public | student     | table | postgres
 public | student_bak | table | postgres
(7 rows)

目录