Connection
\c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo} 连线到新的资料库 (目前是 "test")
\encoding [ENCODING] 显示或设定用户端字元编码
\password [USERNAME] 修改用户密码
\conninfo
显示当前的连接信息
Operating System
\cd [DIR]
改变目前的工作目录
\setenv NAME [VALUE] 设置或者取消环境变量
\timing [on|off]
切换命令计时开关 (默认是off)
\! [COMMAND]
执行shell命令 or 打开内部 shell命令
Variables
\prompt [TEXT] NAME 提示用户设置内部变量
\set [NAME [VALUE]] 设置内部变量, 假如没有参数则显示所有参数
\unset NAME
取消（删除）内部变量
Large Objects
\lo_export LOBOID FILE
\lo_import FILE [COMMENT]
\lo_list
\lo_unlink LOBOID
大对象的操作
4.一些常用命令使用举例
(1)psql加上-E参数，可以把psql中各种以"\"开头的命令执行的实际SQL打印出来
-sh-4.1$ psql -E -h /var/opt/gitlab/postgresql -d gitlabhq_production
psql (9.2.18)
Type "help" for help.
gitlabhq_production=# \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;
如果你在使用之后，想立即关闭
postgres=# \set ECHO_HIDDEN off
postgres=# \d
No relations found.
(2)显示所有表
gitlabhq_production=# \d
List of relations
Schema |
Name
| Type | Owner
--------+---------------------------------------------+----------+--------
public | abuse_reports
| table | gitlab
public | abuse_reports_id_seq
| sequence | gitlab
(3)\d后面跟一个表名，表示显示这个表的结构定义
gitlabhq_production=# \d abuse_reports
Table "public.abuse_reports"
Column |
Type
|
Modifiers
--------------+-----------------------------+------------------------------------------------------------
id
| integer
| not null default nextval('abuse_reports_id_seq'::regclass)
reporter_id | integer
|
user_id
| integer
|
message
| text
|
created_at | timestamp without time zone |
updated_at | timestamp without time zone |
message_html | text
|
Indexes:
"abuse_reports_pkey" PRIMARY KEY, btree (id)
(4)\d后面可以跟一通配符"*"或"?"
gitlabhq_production=# \d a*
Table "public.abuse_reports"
Column |
Type
|
Modifiers
--------------+-----------------------------+------------------------------------------------------------
id
| integer
| not null default nextval('abuse_reports_id_seq'::regclass)
reporter_id | integer
|
user_id
| integer
|
message
| text
|
created_at | timestamp without time zone |
updated_at | timestamp without time zone |
message_html | text
|
Indexes:
"abuse_reports_pkey" PRIMARY KEY, btree (id)
(5)\d+命令，该命令将显示比\d命令更详细的信息
gitlabhq_production=# \d+
List of relations
Schema |
Name
| Type | Owner | Size | Description
--------+---------------------------------------------+----------+--------+------------+-------------
public | abuse_reports
| table | gitlab | 8192 bytes |
public | abuse_reports_id_seq
| sequence | gitlab | 8192 bytes |
public | appearances
| table | gitlab | 8192 bytes |
(6)显示所有表空间
gitlabhq_production=# \db
List of tablespaces
Name | Owner | Location
------------+-------------+----------
pg_default | gitlab-psql |
pg_global | gitlab-psql |
(2 rows)
(7)匹配不同对象类型的\d命令
只显示匹配的表，可以使用\dt命令
只显示索引，可以使用\di命令
只显示序号，可以使用\ds命令
只显示视图，可以使用\dv命令
只显示函数，可以使用\df命令
(8)想显示SQL已执行的时间，可以用\timing命令
gitlabhq_production=# \timing on
Timing is on.
sample_db=# select count(*) from abuse_reports;
count
-------
69
(69 row)
Time: 3.48 ms
(9)显示所有用户或者角色
gitlabhq_production=# \du
List of roles
Role name
|
Attributes
| Member of
-------------------+------------------------------------------------+-----------
gitlab
|
| {}
gitlab-psql
| Superuser, Create role, Create DB, Replication | {}
gitlab_replicator | Replication
| {}
gitlabhq_production=# \dg
List of roles
Role name
|
Attributes
| Member of
-------------------+------------------------------------------------+-----------
gitlab
|
| {}
gitlab-psql
| Superuser, Create role, Create DB, Replication | {}
gitlab_replicator | Replication
| {}
(10)\dp或\z命令用于显示表的权限分配情况
gitlabhq_production=# \dp abuse_reports
Access privileges
Schema |
Name
| Type | Access privileges | Column access privileges
--------+---------------+-------+-------------------+--------------------------
public | abuse_reports | table |
|
(1 row)