Postgresql meta-commands (3)

postgresql meta-command

    • Formatting
    • connection
    • Operating System
    • Variables

Formatting

\a toggle between unaligned and aligned output mode
toggle between formatting and unformatting

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
set header

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
After switching to non-formatting mode with \a, the field separator can be set

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)
Normal format and Html format switching switch

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)
Set the properties of the output 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)
Show only result records

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

tag attributes, or unset if none
Set the properties of the output Html tag

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 DATABASENAME USERNAME

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
Display or set the client encoding method

postgres=# \encoding
UTF8

\password [USERNAME] securely change the password for a user
Modify user password

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
Set the environment variable of the execution environment as a psql variable

[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
Total usage 12
-rw-r--r--. 1 postgres dba 408 Mar 21 11:14 log.file
-rw-r--r--. 1 postgres dba 335 Mar 21 11:13 result.file
-rw-r--r--. 1 postgres dba 84 Mar 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
Display or set internal parameters, for example: \set ECHO_HIDDEN on|off, pay attention to capitalization

postgres=# \set
AUTOCOMMIT = 'on' -- autocommit, on by default
COMP_KEYWORD_CASE = 'preserve-upper'
DBNAME = 'postgres'
ECHO = 'none'
ECHO_HIDDEN = 'off' -- Whether to display the SQL corresponding to the meta command
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, pay attention to capitalization

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.rollreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'
ORDER BY 1;
***************************

                                   List of roles
 Role name | Attributes |
----------- + -------------------------------------- ---------------------- + -----------
 postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {<!-- -->}

postgres=# \set ECHO_HIDDEN off
postgres=# \du
                                   List of roles
 Role name | Attributes |
----------- + -------------------------------------- ---------------------- + -----------
 postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {<!-- -->}

postgres=# \set autocommit off
postgres=# \set autocommit on

\unset NAME unset (delete) internal variable

syntaxbug.com © 2021 All Rights Reserved.