postgresql 元命令
Formatting
\a toggle between unaligned and aligned output mode
在格式化和非格式化之间切换
postgres=# \a
Output format is unaligned.
postgres=# select oid, datname from pg_database;
oid|datname
5|postgres
1|template1
4|template0
(3 rows)
postgres=# \a
Output format is aligned.
postgres=# select oid, datname from pg_database;
oid | datname
-----+-----------
5 | postgres
1 | template1
4 | template0
(3 rows)
\C [STRING] set table title, or unset if none
设置表头
postgres=# \C "This is pg_database"
Title is ""This is pg_database"".
postgres=# select oid, datname from pg_database;
"This is pg_database"
oid | datname
-----+-----------
5 | postgres
1 | template1
4 | template0
(3 rows)
\f [STRING] show or set field separator for unaligned query output
用\a切换到非格式化模式后，可设置字段分隔符
postgres=# \a
Output format is unaligned.
postgres=# select oid, datname from pg_database;
oid|datname
5|postgres
1|template1
4|template0
(3 rows)
postgres=# \f "==="
Field separator is ""==="".
postgres=# select oid, datname from pg_database;
oid"==="datname
5"==="postgres
1"==="template1
4"==="template0
(3 rows)
\H toggle HTML output mode (currently on)
普通格式和Html格式切换开关
postgres=# select oid, datname from pg_database;
oid | datname
-----+-----------
5 | postgres
1 | template1
4 | template0
(3 rows)
postgres=# \H
Output format is html.
postgres=# select oid, datname from pg_database;
<table border="1">
<tr>
<th align="center">oid</th>
<th align="center">datname</th>
</tr>
<tr valign="top">
<td align="right">5</td>
<td align="left">postgres</td>
</tr>
<tr valign="top">
<td align="right">1</td>
<td align="left">template1</td>
</tr>
<tr valign="top">
<td align="right">4</td>
<td align="left">template0</td>
</tr>
</table>
<p>(3 rows)<br />
</p>
\pset [NAME [VALUE]] set table output option
(border|columns|csv_fieldsep|expanded|fieldsep|
fieldsep_zero|footer|format|linestyle|null|
numericlocale|pager|pager_min_lines|recordsep|
recordsep_zero|tableattr|title|tuples_only|
unicode_border_linestyle|unicode_column_linestyle|
unicode_header_linestyle)
设置输出Html的属性
postgres=# \pset border 5
Border style is 5.
postgres=# \H
Output format is html.
postgres=# select oid, datname from pg_database;
<table border="5" "height=100;width=200;">
<tr valign="top">
<td align="right">5</td>
<td align="left">postgres</td>
</tr>
<tr valign="top">
<td align="right">1</td>
<td align="left">template1</td>
</tr>
<tr valign="top">
<td align="right">4</td>
<td align="left">template0</td>
</tr>
</table>
\t [on|off] show only rows (currently off)
只显示结果记录
postgres=# \t
Tuples only is off.
postgres=# select oid, datname from pg_database;
oid | datname
-----+-----------
5 | postgres
1 | template1
4 | template0
(3 rows)
postgres=# \t
Tuples only is on.
postgres=# select oid, datname from pg_database;
5 | postgres
1 | template1
4 | template0
\T [STRING] set HTML <table> tag attributes, or unset if none
设置输出Html标签的属性
postgres=# \T "height=100;width=200;"
Table attributes are ""height=100;width=200;"".
postgres=# \H
Output format is html.
postgres=# select oid, datname from pg_database;
<table border="1" "height=100;width=200;">
<tr valign="top">
<td align="right">5</td>
<td align="left">postgres</td>
</tr>
<tr valign="top">
<td align="right">1</td>
<td align="left">template1</td>
</tr>
<tr valign="top">
<td align="right">4</td>
<td align="left">template0</td>
</tr>
</table>
\x [on|off|auto] toggle expanded output (currently off)
postgres=# select oid, datname from pg_database;
oid | datname
-----+-----------
5 | postgres
1 | template1
4 | template0
(3 rows)
postgres=# \x on
Expanded display is on.
postgres=# select oid, datname from pg_database;
-[ RECORD 1 ]------
oid | 5
datname | postgres
-[ RECORD 2 ]------
oid | 1
datname | template1
-[ RECORD 3 ]------
oid | 4
datname | template0
postgres=# \x
Expanded display is off.
postgres=# \x
Expanded display is on.
postgres=#
Connection
\c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}
connect to new database (currently “postgres”)
\c 数据库名 用户名
postgres=# \c template1 postgres
You are now connected to database "template1" as user "postgres".
\conninfo display information about current connection
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "localhost" (address "::1") at port "5432".
\encoding [ENCODING] show or set client encoding
显示或设置客户端编码方式
postgres=# \encoding
UTF8
\password [USERNAME] securely change the password for a user
修改用户密码
Operating System
\cd [DIR] change the current working directory
postgres-# \! ls
log.file result.file sql.file
postgres-# \cd /home
postgres-# \! ls
postgres
postgres-#
\getenv PSQLVAR ENVVAR fetch environment variable
将执行环境的环境变量设置为 psql 变量
[postgres@pg ~]$ export OS_TEST_VAL="Hello world"
[postgres@pg ~]$ /opt/pgsql/bin/psql -h localhost -U postgres
psql (15.2)
Type "help" for help.
postgres=# \getenv PG_TEST_VAL OS_TEST_VAL
postgres=# select :'PG_TEST_VAL';
?column?
-------------
Hello world
(1 row)
\setenv NAME [VALUE] set or unset environment variable
\timing [on|off] toggle timing of commands (currently off)
postgres=# \timing on
Timing is on.
postgres=# select current_database();
current_database
------------------
postgres
(1 row)
Time: 0.727 ms
postgres=# \timing off
Timing is off.
postgres=# select current_database();
current_database
------------------
postgres
(1 row)
! [COMMAND] execute command in shell or start interactive shell
postgres-# \! ls -l /home/postgres
总用量 12
-rw-r--r--. 1 postgres dba 408 3月 21 11:14 log.file
-rw-r--r--. 1 postgres dba 335 3月 21 11:13 result.file
-rw-r--r--. 1 postgres dba 84 3月 21 11:20 sql.file
Variables
\prompt [TEXT] NAME prompt user to set internal variable
postgres=# \prompt "Input ECHO_HIDDEN value:" ECHO_HIDDEN
"Input ECHO_HIDDEN value:"^C
postgres=# \prompt 'Input ECHO_HIDDEN value:' ECHO_HIDDEN
Input ECHO_HIDDEN value:off
postgres=#
\set [NAME [VALUE]] set internal variable, or list all if no parameters
显示或设置内部参数，例如：\set ECHO_HIDDEN on|off，注意大小写
postgres=# \set
AUTOCOMMIT = 'on' --自动提交，默认打开
COMP_KEYWORD_CASE = 'preserve-upper'
DBNAME = 'postgres'
ECHO = 'none'
ECHO_HIDDEN = 'off' --是否显示元命令对应的 SQL
ENCODING = 'UTF8'
FETCH_COUNT = '0'
HIDE_TABLEAM = 'off'
HIDE_TOAST_COMPRESSION = 'off'
HISTCONTROL = 'none'
HISTSIZE = '500'
HOST = 'localhost'
IGNOREEOF = '0'
LAST_ERROR_MESSAGE = ''
LAST_ERROR_SQLSTATE = '00000'
ON_ERROR_ROLLBACK = 'off'
ON_ERROR_STOP = 'off'
PORT = '5432'
PROMPT1 = '%/%R%x%# '
PROMPT2 = '%/%R%x%# '
PROMPT3 = '>> '
QUIET = 'off'
SERVER_VERSION_NAME = '15.2'
SERVER_VERSION_NUM = '150002'
SHOW_ALL_RESULTS = 'on'
SHOW_CONTEXT = 'errors'
SINGLELINE = 'off'
SINGLESTEP = 'off'
USER = 'postgres'
VERBOSITY = 'default'
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'
VERSION_NAME = '15.2'
VERSION_NUM = '150002'
\set ECHO_HIDDEN on|off，注意大小写
postgres=# \set ECHO_HIDDEN on
postgres=# \du
********* QUERY **********
SELECT r.rolname, r.rolsuper, r.rolinherit,
r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
r.rolconnlimit, r.rolvaliduntil,
ARRAY(SELECT b.rolname
FROM pg_catalog.pg_auth_members m
JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'
ORDER BY 1;
**************************
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=# \set ECHO_HIDDEN off
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=# \set autocommit off
postgres=# \set autocommit on
\unset NAME unset (delete) internal variable
3777
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
