postgresql meta-command
-
- meta command help
- General
- help
- Query Buffer
- Input/Output
- Conditional
Meta command help
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
row to column
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
output last query result to file
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
Describe the last query result
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
Execute each value of the previous query result as an 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
Vertically output the last query result to a file
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
Exit psql, same as ctrl + z
postgres=# \q [postgres@pg ~]$
\watch [SEC] execute query every SEC seconds
Each specified time, repeat the previous 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 Tuesday March 21, 2023 16:15:31 (every 10s) oid | datname | datdba | encoding ----- + ----------- + -------- + ---------- 5 | postgres | 10 | 6 1 | template1 | 10 | 6 4 | template0 | 10 | 6 (3 rows) Tuesday March 21, 2023 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
If the file is followed by a line number, the cursor stays on the specified line after entering the edit mode. The script will be automatically executed after saving and exiting, and the script will not be executed without saving and exiting.
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
edit function
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
edit view
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=# \\Query buffer reset (cleared).
\s [FILE] display history or save it to file
\s display all query history, \s filename save to file
postgres=# \s his.file Write 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
Execute SQL command file
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 outputs the query result to a file, \o restores the output to 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)
When \o is output to a file, echo cannot be output to a file, and qecho -n does not have a newline after the output
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)
When \o is output to a file, \warn is still output to the screen, not to the file
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