Postgresql 元命令（1）

postgresql 元命令

- 元命令帮助
- General
- Help
- Query Buffer
- Input/Output
- Conditional

元命令帮助

postgres=# \?

General

\copyright show PostgreSQL usage and distribution terms

postgres=# \copyright
PostgreSQL Database Management System
(formerly known as Postgres, then as Postgres95)

Portions Copyright (c) 1996-2022, PostgreSQL Global Development Group

Portions Copyright (c) 1994, The Regents of the University of California

Permission to use, copy, modify, and distribute this software and its
documentation for any purpose, without fee, and without a written agreement
is hereby granted, provided that the above copyright notice and this
paragraph and the following two paragraphs appear in all copies.

IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR
DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGE.

THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES,
INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
AND FITNESS FOR A PARTICULAR PURPOSE.  THE SOFTWARE PROVIDED HEREUNDER IS
ON AN "AS IS" BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO
PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.

\crosstabview [COLUMNS] execute query and display result in crosstab
行转列

postgres=# select oid, datname, datdba from pg_database;
 oid |  datname  | datdba 
-----+-----------+--------
   5 | postgres  |     10
   1 | template1 |     10
   4 | template0 |     10
(3 rows)

postgres=# \crosstabview oid datname datdba
 oid | postgres | template1 | template0 
-----+----------+-----------+-----------
   5 |       10 |           |          
   1 |          |        10 |          
   4 |          |           |        10
(3 rows)

\errverbose show most recent error message at maximum verbosity
\g [(OPTIONS)] [FILE] execute query (and send result to file or |pipe);
\g with no arguments is equivalent to a semicolon
将上一个查询结果输出到文件

postgres=# select version();
                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Anolis 8.5.0-10.0.3), 64-bit
(1 row)

postgres=# \g log.file
postgres=# \! cat log.file
                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Anolis 8.5.0-10.0.3), 64-bit
(1 row)

\gdesc describe result of query, without executing it
描述上一个查询结果

postgres=# select oid, datname, datdba, encoding from pg_database;
 oid |  datname  | datdba | encoding 
-----+-----------+--------+----------
   5 | postgres  |     10 |        6
   1 | template1 |     10 |        6
   4 | template0 |     10 |        6
(3 rows)

postgres=# \gdesc
  Column  |  Type   
----------+---------
 oid      | oid
 datname  | name
 datdba   | oid
 encoding | integer
(4 rows)

\gexec execute query, then execute each value in its result
将上一个查询结果的每一个值当作一个 SQL 去执行

postgres=# select 'select version();', 'select current_database();';
     ?column?      |          ?column?          
-------------------+----------------------------
 select version(); | select current_database();
(1 row)

postgres=# \gexec
                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Anolis 8.5.0-10.0.3), 64-bit
(1 row)

 current_database 
------------------
 postgres
(1 row)

\gset [PREFIX] execute query and store result in psql variables
\gx [(OPTIONS)] [FILE] as \g, but forces expanded output mode
将上一个查询结果纵向输出到文件

postgres=# \! rm log.file
postgres=# select oid, datname from pg_database;
 oid |  datname  
-----+-----------
   5 | postgres
   1 | template1
   4 | template0
(3 rows)

postgres=# \gx log.file
postgres=# \! cat log.file
-[ RECORD 1 ]------
oid     | 5
datname | postgres
-[ RECORD 2 ]------
oid     | 1
datname | template1
-[ RECORD 3 ]------
oid     | 4
datname | template0

\q quit psql
退出 psql，同 ctrl + z

postgres=# \q
[postgres@pg ~]$

\watch [SEC] execute query every SEC seconds
各指定时间，重复执行上一条 SQL

postgres=# select oid, datname, datdba, encoding from pg_database;
 oid |  datname  | datdba | encoding 
-----+-----------+--------+----------
   5 | postgres  |     10 |        6
   1 | template1 |     10 |        6
   4 | template0 |     10 |        6
(3 rows)

postgres=# \watch 10
2023年03月21日 星期二 16时15分31秒 (every 10s)

 oid |  datname  | datdba | encoding 
-----+-----------+--------+----------
   5 | postgres  |     10 |        6
   1 | template1 |     10 |        6
   4 | template0 |     10 |        6
(3 rows)

2023年03月21日 星期二 16时15分41秒 (every 10s)

 oid |  datname  | datdba | encoding 
-----+-----------+--------+----------
   5 | postgres  |     10 |        6
   1 | template1 |     10 |        6
   4 | template0 |     10 |        6
(3 rows)

^C

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
如果file后带行号，进入编辑模式后光标停留在指定行。保存退出后会自动执行脚本，不保存退出则不会执行脚本。

postgres=# \e sql.file
                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Anolis 8.5.0-10.0.3), 64-bit
(1 row)

 current_database 
------------------
 postgres
(1 row)

 oid |  datname  
-----+-----------
   5 | postgres
   1 | template1
   4 | template0
(3 rows)

\ef [FUNCNAME [LINE]] edit function definition with external editor
编辑函数

postgres=# \ef
CREATE FUNCTION ( )
 RETURNS
 LANGUAGE
 -- common options:  IMMUTABLE  STABLE  STRICT  SECURITY DEFINER
AS $function$

$function$

\ev [VIEWNAME [LINE]] edit view definition with external editor
编辑视图

postgres=# \ev
CREATE VIEW  AS
 SELECT
  -- something...

\p show the contents of the query buffer

postgres=# \p
select oid, datname from pg_database;
postgres=# select version();
                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Anolis 8.5.0-10.0.3), 64-bit
(1 row)

postgres=# \p
select version();

\r reset (clear) the query buffer

postgres=# \r
Query buffer reset (cleared).

\s [FILE] display history or save it to file
\s显示全部查询历史，\s filename保存到文件

postgres=# \s his.file
Wrote history to file "his.file".
postgres=# \! tail his.file
\s
\p
\w buff.file
\! cat buff.file
\p
select version();
select oid, datname from pg_database;
\p
\s
\s his.file
postgres=#

\w FILE write query buffer to file

postgres=# \p
select version();
postgres=# \w buff.file
postgres=# \! cat buff.file
select version();

Input/Output

\copy … perform SQL COPY with data stream to the client host
\echo [-n] [STRING] write string to standard output (-n for no newline)

postgres=# \echo 'hello world!'
hello world!
postgres=# \echo -n 'hello world!'
hello world!postgres=#

\i FILE execute commands from file
执行SQL命令文件

postgres=# \i sql.file
                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Anolis 8.5.0-10.0.3), 64-bit
(1 row)

 current_database 
------------------
 postgres
(1 row)

 oid |  datname  
-----+-----------
   5 | postgres
   1 | template1
   4 | template0
(3 rows)

\ir FILE as \i, but relative to location of current script
\o [FILE] send all query results to file or |pipe
\o file将查询结果输出到文件，\o恢复输出到stdout

postgres=# \o result.file
postgres=# select version();
postgres=# select current_database();
postgres=# \! cat result.file
                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Anolis 8.5.0-10.0.3), 64-bit
(1 row)

 current_database 
------------------
 postgres
(1 row)

postgres=# \o
postgres=# select version();
                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Anolis 8.5.0-10.0.3), 64-bit
(1 row)

postgres=#

\qecho [-n] [STRING] write string to \o output stream (-n for no newline)
\o输出到文件时，echo不能输出到文件，qecho -n在输出之后不带newline

postgres=# \o result.file
postgres=# \echo 'echo-cmd'
echo-cmd
postgres=# \qecho 'qecho-cmd1'
postgres=# \qecho 'qecho-cmd2'
postgres=# \qecho -n 'qecho-cmd3'
postgres=# \o
postgres=# \! cat result.file
qecho-cmd1
qecho-cmd2
qecho-cmd3postgres=#

\warn [-n] [STRING] write string to standard error (-n for no newline)
\o输出到文件时，\warn仍然输出到屏幕，不输出到文件

postgres=# \o result.file
postgres=# \qecho 'qecho-cmd1'
postgres=# \warn 'warn-string'
warn-string
postgres=# \qecho 'qecho-cmd2'
postgres=# \o
postgres=# \! cat result.file
qecho-cmd1
qecho-cmd2
postgres=#

Conditional

\if EXPR begin conditional block
\elif EXPR alternative within current conditional block
\else final alternative within current conditional block
\endif end conditional block