PostgreSQL常用元命令

  Postgresql元命令: 以反斜线开头的命令也被称为元命令，或斜线命令或反斜线PG命令

1. \l \l+

---查看pg所有的数据库
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 abc       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 mydb      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(5 rows)




postgres=# \l+
                                                                    List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   |
 Tablespace |                Description                 
-----------+----------+----------+-------------+-------------+-----------------------+---------+
------------+--------------------------------------------
 abc       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7505 kB |
 pg_default | 
 mydb      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7505 kB |
 pg_default | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7687 kB |
 pg_default | default administrative connection database
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7505 kB |
 pg_default | unmodifiable empty database
           |          |          |             |             | postgres=CTc/postgres |         |
            | 
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7505 kB |
 pg_default | default template for new databases
           |          |          |             |             | postgres=CTc/postgres |         |
            | 
(5 rows)


---查看所有数据库
postgres=# select * from pg_database;


---查看当前连接数据库
postgres=# select current_database();
 current_database 
------------------
 postgres
(1 row)

2. \d \d+ \dS

---查看当前数据库所有的表、视图、序列

postgres=# \d
          List of relations
 Schema | Name  |   Type   |  Owner   
--------+-------+----------+----------
 public | seq01 | sequence | postgres
 public | t     | table    | postgres
 public | v_t   | view     | postgres
(3 rows)

---查看表结构
postgres=# \d t
 id     | integer |           |          | 

---查看对象所属表空间及详细信息
postgres-# \d+ t1
                                    Table "public.t1"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
--------+---------+-----------+----------+---------+---------+--------------+-------------
 id     | integer |           |          |         | plain   |              | 
Tablespace: "hr_data"


                             View "public.v_t"
 Column |  Type   | Collation | Nullable | Default | Storage | Description 
--------+---------+-----------+----------+---------+---------+-------------
 id     | integer |           |          |         | plain   | 
View definition:
 SELECT t.id
   FROM t;
   
 
----\dS 显示系统所有表
postgres=# \dS
                        List of relations
   Schema   |              Name               | Type  |  Owner   
------------+---------------------------------+-------+----------
 pg_catalog | pg_aggregate                    | table | postgres
 pg_catalog | pg_am                           | table | postgres
 pg_catalog | pg_amop                         | table | postgres
 pg_catalog | pg_amproc                       | table | postgres
 pg_catalog | pg_attrdef                      | table | postgres
 pg_catalog | pg_attribute                    | table | postgres
 pg_catalog | pg_auth_members                 | table | postgres
 pg_catalog | pg_authid                       | table | postgres
 pg_catalog | pg_available_extension_versions | view  | postgres
 pg_catalog | pg_available_extensions         | view  | postgres
 pg_catalog | pg_cast                         | table | postgres
 pg_catalog | pg_class                        | table | postgres
 pg_catalog | pg_collation                    | table | postgres
 pg_catalog | pg_config                       | view  | postgres
 pg_catalog | pg_constraint                   | table | postgres
 pg_catalog | pg_conversion                   | table | postgres
 pg_catalog | pg_cursors                      | view  | postgres

3. \dp \z

---查看数据库所有表、视图、序列、权限
postgres-# \dp
                              Access privileges
 Schema | Name  |   Type   | Access privileges | Column privileges | Policies 
--------+-------+----------+-------------------+-------------------+----------
 public | seq01 | sequence |                   |                   | 
 public | t     | table    |                   |                   | 
 public | v_t   | view     |                   |                   | 
(3 rows)

postgres-# \z
                              Access privileges
 Schema | Name  |   Type   | Access privileges | Column privileges | Policies 
--------+-------+----------+-------------------+-------------------+----------
 public | seq01 | sequence |                   |                   | 
 public | t     | table    |                   |                   | 
 public | v_t   | view     |                   |                   | 
(3 rows)

4. \dt

---查看当前数据库所有表

postgres=# \dt
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | t    | table | postgres
(1 row)

postgres=# select * from pg_tables where tablename='t1';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity 
------------+-----------+------------+------------+------------+----------+-------------+-------------
 public     | t1        | postgres   | hr_data    | t          | f        | f           | f
(1 row)

5. \dv \dm

---查看当前数据库所有视图

postgres=# \dv
        List of relations
 Schema | Name | Type |  Owner   
--------+------+------+----------
 public | v_t  | view | postgres
(1 row)


---列出物化视图
postgres=# create materialized view mv_t as select * from t with no data;
CREATE MATERIALIZED VIEW

postgres=# \dm
              List of relations
 Schema | Name |       Type        |  Owner   
--------+------+-------------------+----------
 public | mv_t | materialized view | postgres
(1 row)

6. \ds

---查看当前数据库所有序列
postgres=# create sequence seq;

postgres=# \ds
          List of relations
 Schema | Name  |   Type   |  Owner   
--------+-------+----------+----------
 public | seq01 | sequence | postgres
(1 row)

7. \di \di+

---查看当前数据库所有索引

postgres=# \di
              List of relations
 Schema |   Name   | Type  |  Owner   | Table 
--------+----------+-------+----------+-------
 public | idx_t_id | index | postgres | t
(1 row)


postgres=# \di+
                            List of relations
 Schema |   Name    | Type  |  Owner   | Table |    Size    | Description 
--------+-----------+-------+----------+-------+------------+-------------
 public | idx_t1_id | index | postgres | t1    | 8192 bytes | 
 public | idx_t_id  | index | postgres | t     | 16 kB      | 
(2 rows)


postgres=# select schemaname,tablename,indexname,tablespace from pg_indexes;
 schemaname |        tablename        |                 indexname                 | tablespace 
------------+-------------------------+-------------------------------------------+------------
 public     | t                       | idx_t_id                                  | 
 public     | t1                      | idx_t1_id                                 | hr_index
 

8. \dl

---列出大对象 list large objects, same as \lo_list

 

9. \db

---查看表空间

postgres=# \db
 pg_default | postgres | 
 pg_global  | postgres |
 
 
 postgres=# select * from pg_tablespace;
  spcname   | spcowner | spcacl | spcoptions 
------------+----------+--------+------------
 pg_default |       10 |        | 
 pg_global  |       10 |        | 
 hr_data    |       10 |        | 
 hr_index   |    16402 |        | 
(4 rows)

 

10. \dn

---查看所有的模式信息

postgres=# \dn
 public | postgres
 

11. \du \dg

---查看数据库的所有角色或用户

postgres=# \du
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# \dg
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 

12. \x

---设置输出格式
postgres=# select * from t;
 id 
----
  1
  2
  3
(3 rows)

postgres=# \x
Expanded display is on.

postgres=# select * from t;
-[ RECORD 1 ]
id | 1
-[ RECORD 2 ]
id | 2
-[ RECORD 3 ]
id | 3

13. ?

---显示Postgresql支持的所有命令

postgres=# \?
General
  \copyright             show PostgreSQL usage and distribution terms
  \crosstabview [COLUMNS] execute query and display results in crosstab
  \errverbose            show most recent error message at maximum verbosity
  \g [FILE] or ;         execute query (and send results to file or |pipe)
  \gexec                 execute query, then execute each value in its result
  \gset [PREFIX]         execute query and store results in psql variables
  \gx [FILE]             as \g, but forces expanded output mode
  \q                     quit psql
  \watch [SEC]           execute query every SEC seconds

Help
  \? [commands]          show help on backslash commands
  \? options             show help on psql command-line options
  \? variables           show help on special variables
  \h [NAME]              help on syntax of SQL commands, * for all commands

Query Buffer
  \e [FILE] [LINE]       edit the query buffer (or file) with external editor
  \ef [FUNCNAME [LINE]]  edit function definition with external editor
  \ev [VIEWNAME [LINE]]  edit view definition with external editor
  \p                     show the contents of the query buffer
  \r                     reset (clear) the query buffer
  \s [FILE]              display history or save it to file
  \w FILE                write query buffer to file
  

14. \timing

---显示SQL语句执行的时间，再执行一次此命令则关闭

postgres=# \timing
Timing is on.
postgres=# select * from t;
 id 
----
  1
  2
  3
(3 rows)

Time: 0.387 ms

15. \conninfo

---查看当前连接的信息
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "1314".

16. \c

---连接到其它的数据库
  \c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}
  
mydb=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 abc       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 mydb      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(5 rows)

mydb=# \c abc
You are now connected to database "abc" as user "postgres".

abc=# select current_database();
 current_database 
------------------
 abc
(1 row)


mydb=# \c mydb postgres 
You are now connected to database "mydb" as user "postgres".

mydb=# \c mydb postgres 192.168.1.68
You are now connected to database "mydb" as user "postgres".

mydb=# \c mydb postgres 192.168.1.68 1314
You are now connected to database "mydb" as user "postgres".

mydb=# select current_database();
 current_database 
------------------
 mydb
(1 row)


第二种连接方法:
postgres=# \c "host=127.0.0.1 dbname=postgres password=postgres user=postgres port=5432"
You are now connected to database "postgres" as user "postgres".


[postgres@server ~]$ psql "host=127.0.0.1 dbname=postgres user=postgres port=5432"
psql (10.12)
Type "help" for help.

17. \!

---执行操作系统shell命令

postgres-# \! ls -l
total 8
-rw------- 1 postgres postgres 402 Apr 22 22:38 postgre.log
-rw------- 1 postgres postgres 373 Apr  5 10:07 postgresql.log

postgres-# \! uptime
 16:41:30 up  3:29,  2 users,  load average: 0.00, 0.01, 0.05
 

18. \cd

--- \cd 改变当前的工作目录  change the current working directory

postgres=# \cd /tmp

19. \set

--- \set查看系统变量

postgres=# \set
AUTOCOMMIT = 'on'
COMP_KEYWORD_CASE = 'preserve-upper'
DBNAME = 'postgres'
ECHO = 'none'
ECHO_HIDDEN = 'off'
ENCODING = 'UTF8'
FETCH_COUNT = '0'
HISTCONTROL = 'none'
HISTSIZE = '500'
HOST = '/tmp'
IGNOREEOF = '0'
ON_ERROR_ROLLBACK = 'off'
ON_ERROR_STOP = 'off'
PORT = '5432'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
QUIET = 'off'
SERVER_VERSION_NAME = '10.12'
SERVER_VERSION_NUM = '100012'
SHOW_CONTEXT = 'errors'
SINGLELINE = 'off'
SINGLESTEP = 'off'
USER = 'postgres'
VERBOSITY = 'default'
VERSION = 'PostgreSQL 10.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit'
VERSION_NAME = '10.12'
VERSION_NUM = '100012'

20. \pset

[postgres@server ~]$ psql
psql (10.12)
Type "help" for help.

postgres=# select 1;
 ?column? 
----------
        1
(1 row)

postgres=# \pset border 3
Border style is 3.
postgres=# select 1;
+----------+
| ?column? |
+----------+
|        1 |
+----------+
(1 row)


postgres=# \pset
border                   3
columns                  0
expanded                 off
fieldsep                 '|'
fieldsep_zero            off
footer                   on
format                   aligned
linestyle                ascii
null                     ''
numericlocale            off
pager                    1
pager_min_lines          0
recordsep                '\n'
recordsep_zero           off
tableattr                
title                    
tuples_only              off
unicode_border_linestyle single
unicode_column_linestyle single
unicode_header_linestyle single

21. \gset

--- \gset 将sql中的列名及值通过操作系统命令来获取 

postgres=# select 1 as id, 'postgres' as user;
 id |   user   
----+----------
  1 | postgres
(1 row)

postgres=# \gset

postgres=# \echo :id  :user
1 postgres


也可以自定义变量的名字:
postgres=# \gset var_

postgres=# \echo :var_id
1
postgres=# \echo :var_user
postgres


使用if elif else endif判断语句:

22. \o

--- \o 将查询结果输出到文件

postgres=# \o /tmp/out.txt
postgres=# select 1 as id, 2 as postgre;


[root@server ~]# more /tmp/out.txt
 id | postgre 
----+---------
  1 |       2
(1 row)

23. \p

--- \p 打印buffer里的SQL语句

postgres=# \p
select 1;

24. \e \ev \ef

--- \e 编辑buffer里的SQL语句

postgres=# \p
select 1;

postgres=# \e

---使用操作系统的编辑器编辑脚本
postgres=# \ev
create table t4(id int);

postgres=# \d t4
                 Table "public.t4"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           |          | 


 ---\ef编辑系统中存在的函数

25. \set prompt

--- \set prompt1 修改提示符
客户端psql提供两种方式，修改提示符：
1、在psql命令行进行修改，退出登陆后失败
2、在文件~/.psqlrc中进行配置


--- psql命令行修改
[postgres@server ~]$ psql
psql (10.12)
Type "help" for help.

postgres=# \set PROMPT1 '%/@%M:%>%R%#%p '

postgres@[local]:5432=#5635 \echo :PROMPT1
%/@%M:%>%R%#%p 

--- .psqlrc配置文件修改
cd ~
touch .psqlrc
vi .psqlrc
\set PROMPT1 '%/@%M:%>%R%#%p '
:wq

[postgres@pg14 ~]$ psql
psql (14.2)
Type "help" for help.

postgres@[local]:1931=#8201


常用设置项：

%M  数据库别名，不是主机名
%>  数据库服务端口号
%n  数据库会话的用户名
%/  当前数据库名称
%#  超级用户显示# ，其它用户显示>
%p  显示当前连接的进程号
%R  在PROMPT1中通常显示为= ，当进程被断开则显示！
%1  显示命令行号









Prompting
The prompts psql issues can be customized to your preference. The three variables PROMPT1, PROMPT2, and PROMPT3 contain strings and special escape sequences that describe the appearance of the prompt. Prompt 1 is the normal prompt that is issued when psql requests a new command. Prompt 2 is issued when more input is expected during command entry, for example because the command was not terminated with a semicolon or a quote was not closed. Prompt 3 is issued when you are running an SQL COPY FROM STDIN command and you need to type in a row value on the terminal.

The value of the selected prompt variable is printed literally, except where a percent sign (%) is encountered. Depending on the next character, certain other text is substituted instead. Defined substitutions are:

%M
The full host name (with domain name) of the database server, or [local] if the connection is over a Unix domain socket, or [local:/dir/name], if the Unix domain socket is not at the compiled in default location.

%m
The host name of the database server, truncated at the first dot, or [local] if the connection is over a Unix domain socket.

%>
The port number at which the database server is listening.

%n
The database session user name. (The expansion of this value might change during a database session as the result of the command SET SESSION AUTHORIZATION.)

%/
The name of the current database.

%~
Like %/, but the output is ~ (tilde) if the database is your default database.

%#
If the session user is a database superuser, then a #, otherwise a >. (The expansion of this value might change during a database session as the result of the command SET SESSION AUTHORIZATION.)

%p
The process ID of the backend currently connected to.

%R
In prompt 1 normally =, but @ if the session is in an inactive branch of a conditional block, or ^ if in single-line mode, or ! if the session is disconnected from the database (which can happen if \connect fails). In prompt 2 %R is replaced by a character that depends on why psql expects more input: - if the command simply wasn't terminated yet, but * if there is an unfinished /* ... */ comment, a single quote if there is an unfinished quoted string, a double quote if there is an unfinished quoted identifier, a dollar sign if there is an unfinished dollar-quoted string, or ( if there is an unmatched left parenthesis. In prompt 3 %R doesn't produce anything.

%x
Transaction status: an empty string when not in a transaction block, or * when in a transaction block, or ! when in a failed transaction block, or ? when the transaction state is indeterminate (for example, because there is no connection).

%l
The line number inside the current statement, starting from 1.

%digits
The character with the indicated octal code is substituted.

%:name:
The value of the psql variable name. See the section Variables for details.

%`command`
The output of command, similar to ordinary “back-tick” substitution.

%[ ... %]
Prompts can contain terminal control characters which, for example, change the color, background, or style of the prompt text, or change the title of the terminal window. In order for the line editing features of Readline to work properly, these non-printing control characters must be designated as invisible by surrounding them with %[ and %]. Multiple pairs of these can occur within the prompt. For example:

testdb=> \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%# '
results in a boldfaced (1;) yellow-on-black (33;40) prompt on VT100-compatible, color-capable terminals.

To insert a percent sign into your prompt, write %%. The default prompts are '%/%R%# ' for prompts 1 and 2, and '>> ' for prompt 3.

26.\setenv

--- \setenv pager cat 设置查询结果显示方式，默认是less，如果查询输出记录很多，修改cat后可以显示所有的查询结果记录

27.\h

--- \h 查看帮助

28.\copy

---往表里输入数据，字段间隔分隔符默认是TAB(4个空格距离)，分隔符默认是单字节，不能使用4个空格代替

postgres=# \copy t2(id, name) from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> 1    'a'
>> 2    'b'
>> \.     
COPY 2

postgres=# select * from t2;
 id | name 
----+------
  1 | 'a'
  2 | 'b'
(2 rows)


将sql脚本导入到表或将表导出到文件,用来备份或准备开发环境

mydb=# create table t1(id int, name varchar(20));
CREATE TABLE
mydb=# insert into t1 values (1, 'zhangsan');
INSERT 0 1
mydb=# insert into t1 values (2, 'xiangming');
INSERT 0 1
mydb=# insert into t1 values (3, '');
INSERT 0 1
mydb=# insert into t1 values (4, 'lihong');
INSERT 0 1

---copy表所有数据，with delimiter '|'用来指定字段间的分隔符，默认是TAB
mydb=# \copy t1 to '/tmp/t1.csv' with csv;
COPY 4
[root@server ~]# more /tmp/t1.csv 
1,zhangsan
2,xiangming
3,""
4,lihong

mydb=# \copy t1 to '/tmp/t1.csv' with delimiter '|';
COPY 4
[root@server ~]# more /tmp/t1.csv 
1|zhangsan
2|xiangming
3|
4|lihong

mydb=# \copy t1 to '/tmp/t1.csv' with delimiter '|' null as '---';
COPY 4

---copy指定列的数据
mydb=# \copy t1(id) to '/tmp/t1.csv' with csv;
COPY 4
[root@server ~]# more /tmp/t1.csv 
1
2


---copy from从外部文件导入数据进pg
[root@server ~]# more /tmp/t2.txt 
1,Oracle
2,MySQL
4, DB2
5,MariaDB

mydb=# create table t2(id int, name varchar(10));
CREATE TABLE

mydb=# \copy t2 from '/tmp/t2.txt' with delimiter ',';
COPY 4
mydb=# select * from t2;
 id |  name   
----+---------
  1 | Oracle
  2 | MySQL
  4 |  DB2
  5 | MariaDB
(4 rows)

[root@server ~]# more /tmp/t3.txt 
1 Oracle
2 MySQL
4 DB2
5 MariaDB

mydb=# \copy t2 from '/tmp/t3.txt' with delimiter ' ';
COPY 4

mydb=# select  * from t2;
 id |  name   
----+---------
  1 | Oracle
  2 | MySQL
  4 |  DB2
  5 | MariaDB
  1 | Oracle
  2 | MySQL
  4 | DB2
  5 | MariaDB
(8 rows)

[postgres@server ~]$ psql -d mydb -c "copy t2 from stdin with delimiter ' '" < /tmp/t3.txt
COPY 4

29.?

--- \? 查看元命令帮助