Psql Tip #001
If you want to simply send a single line command to psql and
exit, try using the
-c or
--command=command flag.
laetitia:~$ psql -c 'select * from test;'
id | value
----+-------
1 | test
(1 row)Psql Tip #002
You can combine several
-c or
--command=command flags to execute several
commands or queries.
laetitia:~$ psql -c 'select count(*) from test;' -c 'select
* from test;'
count
-------
1
(1 row)
id | value
----+-------
1 | test
(1 row)Psql Tip #003
With the
--csv flag, psql will display the
result as a csv file.
psql --csv -c 'select * from test;'
id,value
1,testPsql Tip #004
The
-E or --echo-hidden will
display the actual query generated by backslash commands. It
is a great way to learn Postgres and psql internals.
laetitia:~$ psql -E -c '\l'
********* QUERY **********
SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
d.datcollate as "Collate",
d.datctype as "Ctype",
pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
**************************
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
laetitia | laetitia | 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
test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(5 rows)Psql Tip #005
Use
-f filename or --file=filename
to play queries from a file.
laetitia:~$ cat query.sql
select * from test;
laetitia:~$ psql -f query.sql
id | value
----+-------
1 | test
(1 row)Psql Tip #006
You can combine the
-c or --command
flag with the -f or --file flag.
laetitia:~$ cat query.sql
select * from test;
laetitia:~$ psql -f query.sql -c 'select count(*) from test;'
id | value
----+-------
1 | test
(1 row)
count
-------
1
(1 row)Psql Tip #007
If you use the value
- (hyphen) in the flag
-f or --filename, psql will read
commands and queries from the standard input. The read will
end when reaching EOF command (CTRL+D) or the \q
command.
laetitia:~$ psql -f -
laetitia=# select * from test; \q
id | value
----+-------
1 | test
(1 row) Psql Tip #008
Using the
-f or --filename flag
will allow to get useful information as error messages with
line numbers.
laetitia:~$ cat query.sql
select count(*) from test;
test;
select * from test;
laetitia:~$ psql -f query.sql
count
-------
1
(1 row)
psql:query.sql:3: ERROR: syntax error at or near "test"
LINE 1: test;
^
id | value
----+-------
1 | test
(1 row)Psql Tip #009
You can give queries to psql from the standard input using
< filename.
laetitia:~$ cat query.sql
select * from test;
laetitia:~$ psql < query.sql
id | value
----+-------
1 | test
(1 row)Psql Tip #010
You can use the result of another command as an input for
psql using the
| character.
laetitia:~$ cat query.sql
select * from test;
laetitia:~$ cat query.sql | psql
id | value
----+-------
1 | test
(1 row)Psql Tip #011
-d dbname or --dbname=dbname will
allow you to specify the name of the database psql needs to
connect to.
laetitia:~$ psql -d laetitia
psql (14devel)
Type "help" for help.
laetitia=# Psql Tip #012
The remaining word after option processing will be taken as a
database name.
laetitia:~$ psql test
psql (14devel)
Type "help" for help.
test=# Psql Tip #013
The
See Postgres documentation for connection URIs.
-d sometext or
--dbname=sometext can accept a connection URI
instead of a dbname.See Postgres documentation for connection URIs.
laetitia:~$ psql -d postgresql://localhost
psql (14devel)
Type "help" for help.
laetitia=# Psql Tip #014
The
See Postgres documentation for Keyword/Value connection strings.
-d sometext or
--dbname=sometext can accept a Keyword/Value
connection string instead of a dbname.See Postgres documentation for Keyword/Value connection strings.
laetitia:~$ psql -d "host=localhost port=5432"
psql (14devel)
Type "help" for help.
laetitia=# Psql Tip #015
The database psql will try to connect to is in order:
- the database name provided with the
-dor--dbnameflag - the first parameter not linked to a flag if the
-dor--dbnameis not set - the value of the $PGDATABASE environment variable, if set
- the same as the name of the user who's trying to connect
laetitia:~$ psql -d test
psql (14devel)
Type "help" for help.
test=# \q
laetitia:~$ psql test
psql (14devel)
Type "help" for help.
test=# \q
laetitia:~$ export PGDATABASE=test
laetitia:~$ psql
psql (14devel)
Type "help" for help.
test=# \q
laetitia:~$ unset PGDATABASE
laetitia:~$ psql
psql (14devel)
Type "help" for help.
laetitia=#Psql Tip #016
Using the
-e or --echo-queries will
display the query just before the query result in the
standard output.
laetitia:~$ psql -e -c 'select * from test;'
select * from test;
id | value
----+-------
1 | test
(1 row)Psql Tip #017
Using twice a
-c or --command flag
will display the result of both commands whereas feeding a
string with two queries to a -c or
--command flag will only display the result of
the last one.
laetitia:~$ psql -c 'select count(*) from test; select * from test;'
id | value
----+-------
1 | test
(1 row)
laetitia:~$ psql -c 'select count(*) from test;' -c ' select * from test;'
count
-------
1
(1 row)
id | value
----+-------
1 | test
(1 row) Psql Tip #018
The
-h hostname or --hostname
hostname flag will allow you to provide a host psql
will connect to.
laetitia:~$ psql -h localhost
psql (14devel)
Type "help" for help.
laetitia=#Psql Tip #019
You can use the
-h hostname or --hostname
hostname flag to provide the directory the server is
writing the into, instead of providing a hostname.
laetitia:~$ sudo grep unix_socket_directories /usr/local/pgsql/data/postgresql.conf
unix_socket_directories = '/tmp' # comma-separated list of directories
laetitia:~$ psql -h /tmp
psql (14devel)
Type "help" for help.
laetitia=# \qPsql Tip #020
The host psql will try to connect to is in order:
- The hostname/socket directory provided by the
-cor--command - The $PGHOST environment variable, if set
- The Unix-domain socket used by the local Postgres server (if supported)
localhostif Unix-domain socket are not supported
laetitia:~$ psql -h localhost
psql (14devel)
Type "help" for help.
laetitia=# \conninfo
You are connected to database "laetitia" as user "laetitia" on host "localhost" (address "::1") at port "5432".
laetitia=# \q
laetitia:~$ psql -h /tmp
psql (14devel)
Type "help" for help.
laetitia=# \conninfo
You are connected to database "laetitia" as user "laetitia" via socket in "/tmp" at port "5432".
laetitia=# \q
laetitia:~$ export PGHOST=localhost
laetitia:~$ psql
psql (14devel)
Type "help" for help.
laetitia=# \conninfo
You are connected to database "laetitia" as user "laetitia" on host "localhost" (address "::1") at port "5432".
laetitia=# \q
laetitia:~$ unset PGHOST
laetitia:~$ psql
psql (14devel)
Type "help" for help.
laetitia=# \conninfo
You are connected to database "laetitia" as user "laetitia" via socket in "/tmp" at port "5432".
laetitia=# \qPsql Tip #021
Use the
-H or --html flag to get
the query result in HTML format.
laetitia:~$ psql -H -c 'select * from test;'
<table border="1">
<tr>
<th align="center">id</th>
<th align="center">value</th>
</tr>
<tr valign="top">
<td align="right">1</td>
<td align="left">test</td>
</tr>
</table>
<p>(1 row)<br />
</p>Psql Tip #022
The
-l or --list flag will list the
databases available on the server.
laetitia:~$ psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
laetitia | laetitia | 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
test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(5 rows)Psql Tip #023
Unless a databasename is given, using the
-l or
--list flag will make psql attempt to connect to
the postgres database.
laetitia:~$ sudo cat /usr/local/pgsql/data/pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
local postgres laetitia reject
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
laetitia:~$ psql -l
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: pg_hba.conf rejects connection for host "[local]", user "laetitia", database "postgres", no encryption
laetitia:~$ psql -l laetitia
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
laetitia | laetitia | 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
test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(5 rows)Psql Tip #024
If you use the
-l or --list flag,
all the other options provided (except the database name
option) won't be taken into account.
laetitia:~$ psql -l -c 'select * from test;' laetitia
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
laetitia | laetitia | 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
test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(5 rows)Psql Tip #025
The flag
-a or --echo-all will
print all the non empty lines to the standard output.
laetitia:~$ cat query.sql
select count(*) from test;
select * from test;
laetitia:~$ psql -f query.sql -a
select count(*) from test;
count
-------
1
(1 row)
select * from test;
id | value
----+-------
1 | test
(1 row)Psql Tip #026
The
-L filename or
--log-file=filename flag will write all query
output into file filename in addition to the normal output
destination.
laetitia:~$ psql -c 'select * from test;' -L output.log
id | value
----+-------
1 | test
(1 row)
laetitia:~$ cat output.log
********* QUERY **********
select * from test;
**************************
id | value
----+-------
1 | test
(1 row)Psql Tip #027
The
-o filename or
--output=filename flag will write all query
output into file filename.
laetitia:~$ psql -c 'select * from test;' -o output.log
laetitia:~$ cat output.log
id | value
----+-------
1 | test
(1 row)Psql Tip #028
The
-p port or --port=port will
allow you to use a specific port number to connect.
laetitia:~$ psql -p 5432
psql (14devel)
Type "help" for help.
laetitia=#Psql Tip #029
The port psql will try to connect to is in order:
- the port provided with the
-por--portflag - the value of the $PGPORT environment variable, if set
- The port specified at compile time (usually 5432)
laetitia:~$ sudo grep "port =" /usr/local/pgsql/data/postgresql.conf
port = 5433 # (change requires restart)
laetitia:~$ psql -p 5433
psql (14devel)
Type "help" for help.
laetitia=# \q
laetitia:~$ export PGPORT=5433
laetitia:~$ psql
psql (14devel)
Type "help" for help.
laetitia=# \q
laetitia:~$ unset PGPORT
laetitia:~$ psql
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
Is the server running locally and accepting connections on that socket?Psql Tip #030
psql will be more quiet with the
-q or
--quiet flag on.
laetitia:~$ psql
psql (14devel)
Type "help" for help.
laetitia=# \q
laetitia:~$ psql -q
laetitia=# \qPsql Tip #031
If you want to debug a script, the
-s or
--single-step flag will make psql stop after
each command.
laetitia:~$ cat query.sql
select count(*) from test;
select * from test;
laetitia:~$ psql -s -f query.sql
***(Single step mode: verify command)*******************************************
select count(*) from test;
***(press return to proceed or enter x and return to cancel)********************
count
-------
1
(1 row)
***(Single step mode: verify command)*******************************************
select * from test;
***(press return to proceed or enter x and return to cancel)********************
id | value
----+-------
1 | test
(1 row)Psql Tip #032
The
-t or --tuples-only flag will
turn off printing column names and result row count footers.
laetitia:~$ psql -c 'select * from test;'
id | value
----+-------
1 | test
(1 row)
laetitia:~$ psql -c 'select * from test;' -t
1 | testPsql Tip #033
If you want to display the result of your query in HTML,
using the
-T table_options or --table-attr
table_options will allow you to specify options in the
HTML table tag.
laetitia:~$ psql -c 'select * from test;' -H -T class=\"myAwesomeTableClass\"
<table border="1" class="myAwesomeTableClass">
<tr>
<th align="center">id</th>
<th align="center">value</th>
</tr>
<tr valign="top">
<td align="right">1</td>
<td align="left">test</td>
</tr>
</table>
<p>(1 row)<br />
</p>Psql Tip #034
The
-U username or
--username=username flag will allow you to feed
the database user to connect to to psql.
laetitia:~$ psql -U postgres
psql (14devel)
Type "help" for help.
postgres=#Psql Tip #035
The user psql will try to connect with is in order:
- the user provided with the
-U usernameor--username=username - the value of the $PGUSER environment variable, if set
- the name of the OS user
laetitia:~$ psql -U postgres
psql (14devel)
Type "help" for help.
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5432".
postgres=# \q
laetitia:~$ export PGUSER=postgres
laetitia:~$ psql
psql (14devel)
Type "help" for help.
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5432".
postgres=# \q
laetitia:~$ unset PGUSER
laetitia:~$ psql
psql (14devel)
Type "help" for help.
laetitia=# \conninfo
You are connected to database "laetitia" as user "laetitia" via socket in "/tmp" at port "5432".Psql Tip #036
Long options can take a value with or without the equal sign.
laetitia:~$ psql --dbname laetitia
psql (14devel)
Type "help" for help.
laetitia=# \q
laetitia:~$ psql --dbname=laetitia
psql (14devel)
Type "help" for help.
laetitia=#Psql Tip #037
You can use variables in psql in non-interactive mode. the
flag
-v assignment or
--set=assignment or
--variable=assignment.
laetitia:~$ cat query.sql
select * from :tablename;
laetitia:~$ psql -v tablename=test -f query.sql
id | value
----+-------
1 | test
(1 row)
laetitia:~$ cat query2.sql
select * from test where value = :'var';
ilaetitia:~$ psql --set var=test -f query2.sql
id | value
----+-------
1 | test
(1 row)Psql Tip #038
To unset a variable or to set a variable to an empty value in
non interractive mode, use the
-v assignment or
--set=assignment or
--variable=assignment flag and leave off the
equal sign.
laetitia:~$ psql -v var=value -c '\echo :var'
value
laetitia:~$ psql -v var= -c '\echo :var' Psql Tip #039
psql will print the psql version and exit should you use the
-V or --version flag.
laetitia:~$ psql -V
psql (PostgreSQL) 14develPsql Tip #040
If you don't want psql to issue a password prompt, use the
-w or --no-password flag. Should a
password be needed, the connection attemp will fail. This is
useful in batch jobs and scripts where no user is present to
enter a password.
laetitia:~$ sudo cat /usr/local/pgsql/data/pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
local postgres laetitia scram-sha-256
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
laetitia:~$ psql postgres -w
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: fe_sendauth: no password suppliedPsql Tip #041
The
If the server requires a password, psql will waste a connection attempt finding out that the server wants a password. In some cases it is worth typing
-W or --password will force
psql to prompt for a password before connecting to a
database, even if the password will not be used.If the server requires a password, psql will waste a connection attempt finding out that the server wants a password. In some cases it is worth typing
-W to
avoid the extra connection attempt.
laetitia:~$ sudo cat /usr/local/pgsql/data/pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
laetitia:~$ psql -W
Password:
psql (14devel)
Type "help" for help.
laetitia=#Psql Tip #042
The
-W or --password is set for the
entire session. Should you try a connection in psql using the
\c command will still ask for a password even if
it's useless.
laetitia:~$ sudo cat /usr/local/pgsql/data/pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
laetitia:~$ psql -W
Password:
psql (14devel)
Type "help" for help.
laetitia=# \c postgres
Password:
You are now connected to database "postgres" as user "laetitia".
postgres=# Psql Tip #043
The
-x or --expand will expand the
output for better readability.
laetitia:~$ psql -x -c 'select * from animal;'
-[ RECORD 1 ]------+------------------------------------------
specie_code | cat or dog or other kind of 4 paws animal
name | Ada
sex | Female
birth_date | 2018-04-25
death_date |
mother_specie_code | cat or dog or other kind of 4 paws animal
mother_name | GracePsql Tip #044
The
-1 or --single-transaction flag
will encapsulate all your commands (provided with
-c or --command or with
-f or --file) into a single
transaction with begin and commit.
laetitia:~$ cat query.sql
select * from test;
insert into test values ('myvalue');
select * from test;
laetitia:~$ psql -1 -f query.sql -E
********* QUERY **********
BEGIN
**************************
id | value
----+-------
1 | test
(1 row)
psql:query.sql:2: ERROR: invalid input syntax for type integer: "myvalue"
LINE 1: insert into test values ('myvalue');
^
psql:query.sql:3: ERROR: current transaction is aborted, commands ignored until end of transaction block
********* QUERY **********
COMMIT
**************************Psql Tip #045
psql returns 0 to the shell if it finished normally, 1 if a fatal error of
its own occurs, 2 if the connection
to the server went bad and the session was not interactive, and 3 if an
error occurred in a script and the variable ON_ERROR_STOP was set.
laetitia:~$ psql -c 'select * from test;'
id | value
----+-------
1 | test
(1 row)
laetitia:~$ echo $?
0
laetitia:~$ psql -f notExistingFile.sql
psql: error: notExistingFile.sql: No such file or directory
laetitia:~$ echo $?
1
laetitia:~$ psql -U notExistingUser
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: role "notExistingUser" does not exist
laetitia:~$ echo $?
2
laetitia:~$ cat query.sql
\set ON_ERROR_STOP
select * from test;
test;
select * from test;
laetitia:~$ psql -f query.sql
id | value
----+-------
1 | test
(1 row)
psql:query.sql:3: ERROR: syntax error at or near "test"
LINE 1: test;
^
laetitia:~$ echo $?
3Psql Tip #046
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.
laetitia=# \set PROMPT1 'What can I do for you? '
What can I do for you? \set PROMPT2 'I\'m waiting... '
What can I do for you? select *
I'm waiting... from test;
id | value
----+-------
1 | test
(1 row)
What can I do for you? \set PROMPT3 'Please enter your value: '
What can I do for you? copy test (value) 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.
Please enter your value: blabla
Please enter your value: \.
COPY 1Psql Tip #047
The
psql prompt will nicely and discreetly (as a real gentleman) remind
you a quote, a double quote or a parenthesis is open but not closed.
laetitia=# select '
laetitia'#
laetitia=# select (
laetitia(#
laetitia=# select "
laetitia"#Psql Tip #048
The
psql prompt can remind you that you are or not inside a transaction.
It is the default behaviour for Postgres 14. For lower versions of
Postgres, you can have the exact same feature by setting the PROMPT1
variable with this command: \set PROMPT1 '%~%x%# '. This command can be
added to a .psqlrc in your home directory to be played automatically when
psql starts.
laetitia=# begin;
BEGIN
laetitia=*#
laetitia=*# rollback;
ROLLBACK
laetitia=#Psql Tip #049
By default, the
psql prompt will display # if you're connected with
a super admin user or > for a normal user.
laetitia=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
laetitia | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test | | {}
test=# \c laetitia test
You are now connected to database "laetitia" as user "test".
laetitia=> \c laetitia laetitia
You are now connected to database "laetitia" as user "laetitia".
laetitia=#Psql Tip #050
You can customize the
psql prompt with the full host name of the
database server by
using the %M value.
laetitia:~$ psql -h myHost.myAwesomeDomain.com
psql (14devel)
Type "help" for help.
laetitia=# \set PROMPT1 '%M '
myHost.myAwesomeDomain.comPsql Tip #051
You can customize the
psql prompt with the host name of the database
server by
using the %m value.
laetitia:~$ psql -h myHost.myAwesomeDomain.com
psql (14devel)
Type "help" for help.
laetitia=# \set PROMPT1 '%m '
myHost Psql Tip #052
The
psql prompt can display the port the database server is listening on
with the %> value.
laetitia=# \set PROMPT1 '%> '
5432 Psql Tip #053
The
psql prompt can display the database session user name
with the %n value.
postgres=# \set PROMPT1 '%n '
laetitiaPsql Tip #054
The
psql prompt can display the database name
with the %~ value or the %/ value. If you're connected to the default
database and you used the %~ value in the prompt, it will display ~
when you're connected to the default database.
laetitia:~$ psql
psql (14devel)
Type "help" for help.
laetitia=# \set PROMPT1 '%~ '
~ \set PROMPT1 '%/ '
laetitia Psql Tip #055
The
psql prompt can display the process ID of the backend currently
connected to
with the %p value.
laetitia=# \set PROMPT1 '%p #'
94162 #\! pgrep -fal postgres
1866 /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
1867 postgres: logger ptr_munge= main_stack=
1869 postgres: checkpointer ptr_munge= main_stack=
1870 postgres: background writer ptr_munge= main_stack=
1871 postgres: walwriter ptr_munge= main_stack=
1872 postgres: autovacuum launcher ptr_munge= main_stack=
1873 postgres: stats collector ptr_munge= main_stack=
1874 postgres: logical replication launcher ptr_munge= main_stack=
94162 postgres: laetitia laetitia [local] idle ptr_munge= main_stack=
94162 #Psql Tip #056
The
psql prompt can display the number of the line of your statement,
should you use the value %l when you set the prompt.
laetitia=# \set PROMPT1 '%l '
1 \set PROMPT2 '%l '
1 select
2 *
3 from
4 test
5 ;
id | value
----+-------
1 | test
(1 row)Psql Tip #057
The
This feature is available since Postgres 8.0.
[ ... %] value can allow to customize the terminal exactly like your
terminal prompt.
laetitia:~$ psql
psql (14devel)
Type "help" for help.
laetitia=# \set PROMPT1 '%[%033[36m%]%n@%/%R%]%# '
# laetitia@laetitia=This feature is available since Postgres 8.0.
Psql Tip #058
When already connected to
psql you can re-connect using the
\c or \connect meta-command.
laetitia:~$ psql
psql (14devel)
Type "help" for help.
laetitia=# \conninfo
You are connected to database "laetitia" as user "laetitia" via socket in "/tmp" at port "5432".
laetitia=# \c
You are now connected to database "laetitia" as user "laetitia".
laetitia=# \conninfo
You are connected to database "laetitia" as user "laetitia" via socket in "/tmp" at port "5432".Psql Tip #059
Anything you enter in psql that begins with an unquoted backslash is
a psql meta-command that is processed by psql itself. Meta-commands are
often called slash or backslash commands.
The format of a psql command is the backslash, followed immediately by a command verb, then any arguments. The arguments are separated from the command verb and each other by any number of whitespace characters.
The format of a psql command is the backslash, followed immediately by a command verb, then any arguments. The arguments are separated from the command verb and each other by any number of whitespace characters.
laetitia:~$ psql
psql (14devel)
Type "help" for help.
laetitia=# \test
invalid command \test
Try \? for help.Psql Tip #060
in interactive mode, you can connect to a different database with the
metacommand
\c dbname or \connect dbname
laetitia=# \conninfo
You are connected to database "laetitia" as user "laetitia" via socket in "/tmp" at port "5432".
laetitia=# \c postgres
You are now connected to database "postgres" as user "laetitia".
postgres=#Psql Tip #061
In interactive mode, you can connect to another database, host, port or
with another user by using the following metacommand
\c dbname username
host port.
laetitia:~$ psql
psql (14devel)
Type "help" for help.
laetitia=# \conninfo
You are connected to database "laetitia" as user "laetitia" via socket in "/tmp" at port "5432".
laetitia=# \c laetitia laetitia localhost 5432
You are now connected to database "laetitia" as user "laetitia" on host "localhost" (address "::1") at port "5432".
laetitia=#\c dbname username and since Postgres 8.2 for \c dbname username host port.
Psql Tip #062
If a
\c or \connect metacommand fails (wrong user name, access denied,
etc.), the previous connection will be kept if psql is in interactive
mode. But when executing a non-interactive script, processing will
immediately stop with an error.
laetitia:~$ cat query.sql
\c latiatia
select * from test;
laetitia:~$ psql -f query.sql
psql:query.sql:1: error: \connect: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: database "latiatia" does not exist
laetitia:~$ psql
psql (14devel)
Type "help" for help.
laetitia=# \c latiatia
connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: database "latiatia" does not exist
Previous connection kept
laetitia=#Psql Tip #063
You can use a
- to omit a positional parameter with the \c or
\connect meta-command.
laetitia=# \conninfo
You are connected to database "laetitia" as user "laetitia" via socket in "/tmp" at port "5432".
laetitia=# \c - - 127.0.0.1
You are now connected to database "laetitia" as user "laetitia" on host "127.0.0.1" at port "5432".Psql Tip #064
The
See Postgres documentation for connection URIs.
\c or
\connect metacommand can accept a connection URI
instead of a dbname.See Postgres documentation for connection URIs.
laetitia:~$ psql
psql (14devel)
Type "help" for help.
laetitia=# \conninfo
You are connected to database "laetitia" as user "laetitia" via socket in "/tmp" at port "5432".
laetitia=# \c postgresql://localhost
You are now connected to database "laetitia" as user "laetitia" on host "localhost" (address "::1") at port "5432".Psql Tip #065
The
See Postgres documentation for Keyword/Value connection strings.
\c or
\connect metacommand can accept a Keyword/Value
connection string instead of a dbname.See Postgres documentation for Keyword/Value connection strings.
laetitia=# \conninfo
You are connected to database "laetitia" as user "laetitia" via socket in "/tmp" at port "5432".
laetitia=# \c "host=localhost port=5432"
You are now connected to database "laetitia" as user "laetitia" on host "localhost" (address "::1") at port "5432".Psql Tip #066
The
\C title will allow you to set a title for your query. To unset
a title, just use \C.
laetitia=# \C 'test values'
Title is "test values".
laetitia=# select * from test;
test values
id | value
----+-------
1 | test
(1 row)
laetitia=# \C
Title is unset.
laetitia=# select * from test;
id | value
----+-------
1 | test
(1 row)Psql Tip #067
Use the
\conninfo metacommand to display information about the current
database connection.
laetitia:~$ psql
psql (14devel)
Type "help" for help.
laetitia=# \conninfo
You are connected to database "laetitia" as user "laetitia" via socket in "/tmp" at port "5432".Psql Tip #068
You can display copyright information and distribution term of PostgreSQL
by using the
\copyright metacommand.
laetitia=# \copyright
PostgreSQL Database Management System
(formerly known as Postgres, then as Postgres95)
Portions Copyright (c) 1996-2021, 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.Psql Tip #069
\d pattern will display for each relation (table, view, materialized
view, index, sequence, or foreign table) or composite type matching the
pattern, all columns, their types, the tablespace (if not the
default) and any special attributes such as NOT NULL or defaults.
Associated indexes, constraints, rules, and triggers are also shown.
For foreign tables, the associated foreign server is shown as
well.For some types of relation,
\d shows additional information for each
column: column values for sequences, indexed expressions for indexes,
and foreign data wrapper options for foreign tables.
laetitia=# \d test
Table "public.test"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+------------------------------
id | integer | | not null | generated always as identity
value | text | | not null |
Indexes:
"test_value_key" UNIQUE CONSTRAINT, btree (value)Psql Tip #070
The
\cd metacommand will change the current working directory.
laetitia=# \cd /tmp
laetitia=# \! pwd
/private/tmp Psql Tip #071
Using the
\d metacommand without a pattern will show a list of all
visible tables, views, materialized views, sequences and foreign tables.
laetitia=# \d
List of relations
Schema | Name | Type | Owner
--------+-------------+----------+----------
public | animal | table | laetitia
public | test | table | laetitia
public | test_id_seq | sequence | laetitia
(3 rows) Psql Tip #072
By default, the
\d won't show system object. You can either use
the \dS or supply pattern to include system objects.
laetitia=# \d
List of relations
Schema | Name | Type | Owner
--------+-------------+----------+----------
public | animal | table | laetitia
public | test | table | laetitia
public | test_id_seq | sequence | laetitia
(3 rows)
laetitia=# \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_views | view | postgres
public | animal | table | laetitia
public | test | table | laetitia
public | test_id_seq | sequence | laetitia
(136 rows)
laetitia=# \d pg_aggregate
Table "pg_catalog.pg_aggregate"
Column | Type | Collation | Nullable | Default
------------------+----------+-----------+----------+---------
aggfnoid | regproc | | not null |
aggkind | "char" | | not null |
aggnumdirectargs | smallint | | not null |
aggtransfn | regproc | | not null |
aggfinalfn | regproc | | not null |
aggcombinefn | regproc | | not null |
aggserialfn | regproc | | not null |
aggdeserialfn | regproc | | not null |
aggmtransfn | regproc | | not null |
aggminvtransfn | regproc | | not null |
aggmfinalfn | regproc | | not null |
aggfinalextra | boolean | | not null |
aggmfinalextra | boolean | | not null |
aggfinalmodify | "char" | | not null |
aggmfinalmodify | "char" | | not null |
aggsortop | oid | | not null |
aggtranstype | oid | | not null |
aggtransspace | integer | | not null |
aggmtranstype | oid | | not null |
aggmtransspace | integer | | not null |
agginitval | text | C | |
aggminitval | text | C | |
Indexes:
"pg_aggregate_fnoid_index" PRIMARY KEY, btree (aggfnoid) Psql Tip #073
The
+ modifier to the \d pattern metacommand will, on top of displaying
information about table, view, materialized view, index, sequence,
foreign table, or composite type matching the pattern, display any comments
associated with the columns of the table are shown, as is the presence of
OIDs in the table, the view definition if the relation is a view,
a non-default replica identity setting.
laetitia=# \d+ test
Table "public.test"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+------------------------------+----------+-------------+--------------+----------------
id | integer | | not null | generated always as identity | plain | | |
value | text | | not null | | extended | pglz | | column comment
Indexes:
"test_value_key" UNIQUE CONSTRAINT, btree (value)
Access method: heapPsql Tip #074
\dn will display a list of schemas. The S modifier will allow to list
system schemas too. \dn pattern will list schemas matching the pattern.
The + modifier will display access privileges and description.
laetitia=# \dn
List of schemas
Name | Owner
--------+----------
public | postgres
(1 row)
laetitia=# \dnS
List of schemas
Name | Owner
--------------------+----------
information_schema | postgres
pg_catalog | postgres
pg_toast | postgres
public | postgres
(4 rows)
laetitia=# \dn pub*
List of schemas
Name | Owner
--------+----------
public | postgres
(1 row)i
laetitia=# \dn+ pub*
List of schemas
Name | Owner | Access privileges | Description
--------+----------+----------------------+------------------------
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
(1 row)+ modifier and
in Postgres 9.0 to add the S modifier.
Psql Tip #075
\db will display a list of tablespaces. \db pattern will list
tablespaces matching the pattern.
The + modifier will display access privileges, options, size and description.
laetitia=# \db
List of tablespaces
Name | Owner | Location
------------+----------+----------
pg_default | postgres |
pg_global | postgres |
(2 rows)
laetitia=# \db+
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size | Description
------------+----------+----------+-------------------+---------+--------+-------------
pg_default | postgres | | | | 39 MB |
pg_global | postgres | | | | 559 kB |
(2 rows)Psql Tip #076
\dD will display a list of domains. \dD pattern will list
domain matching the pattern.
The + modifier will also display access privileges and description.
The S modifier will also display the system domains. There is
currently no system domains provided with Postgres.
laetitia=# \dD
List of domains
Schema | Name | Type | Collation | Nullable | Default | Check
--------+---------------+------+-----------+----------+---------+-------------------------------------
public | one_word_only | text | | | | CHECK (VALUE ~ '^[a-zA-Z]+$'::text)
(1 row)
laetitia=# \dD+
List of domains
Schema | Name | Type | Collation | Nullable | Default | Check | Access privileges | Description
--------+---------------+------+-----------+----------+---------+-------------------------------------+-------------------+-------------
public | one_word_only | text | | | | CHECK (VALUE ~ '^[a-zA-Z]+$'::text) | |
(1 row)
laetitia=# \dDS
List of domains
Schema | Name | Type | Collation | Nullable | Default | Check
--------+---------------+------+-----------+----------+---------+-------------------------------------
public | one_word_only | text | | | | CHECK (VALUE ~ '^[a-zA-Z]+$'::text)
(1 row)S
modifier and in Postgres 9.2 to add the + modifier.
Psql Tip #077
\ddp will display a list of default access privilege
settings. An entry is shown for each role (and schema, if applicable) for
which the default privilege settings have been changed from the built-in
defaults. If pattern is specified, only entries whose role name or schema
name matches the pattern are listed.
laetitia=# alter default privileges for role test revoke all on tables from test;
ALTER DEFAULT PRIVILEGES
laetitia=# \ddp
Default access privileges
Owner | Schema | Type | Access privileges
-------+--------+-------+-------------------
test | | table |
(1 row) Psql Tip #078
\dE will display a list of foreign table. \dE pattern will list
foreign tables matching the pattern.
The + modifier will also display persistence, size and description.
The S modifier will also display the system foreign tables. There is
currently no system foreign table provided with Postgres.
laetitia=# \dE
List of relations
Schema | Name | Type | Owner
--------+------+---------------+----------
public | logs | foreign table | laetitia
(1 row)
laetitia=# \dE+
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+------+---------------+----------+-------------+---------+-------------
public | logs | foreign table | laetitia | permanent | 0 bytes |
(1 row)
laetitia=# \dES
List of relations
Schema | Name | Type | Owner
--------+------+---------------+----------
public | logs | foreign table | laetitia
(1 row)Psql Tip #079
\di will display the list of user created visible indexes. \di pattern will list
indexes matching the pattern.
The + modifier will also display persistence, access methode,
size and description.
The S modifier will also display the system indexes.
laetitia=# \di
List of relations
Schema | Name | Type | Owner | Table
--------+----------------+-------+----------+-------
public | test_value_key | index | laetitia | test
(1 row)
laetitia=# \di+
List of relations
Schema | Name | Type | Owner | Table | Persistence | Access Method | Size | Description
--------+----------------+-------+----------+-------+-------------+---------------+-------+-------------
public | test_value_key | index | laetitia | test | permanent | btree | 16 kB |
(1 row)
laetitia=# \diS
List of relations
Schema | Name | Type | Owner | Table
------------+-----------------------------------------------+-------+----------+-------------------------
pg_catalog | pg_aggregate_fnoid_index | index | postgres | pg_aggregate
pg_catalog | pg_am_name_index | index | postgres | pg_am
pg_catalog | pg_am_oid_index | index | postgres | pg_ami
[...]
pg_catalog | pg_user_mapping_user_server_index | index | postgres | pg_user_mapping
public | test_value_key | index | laetitia | test
(118 rows) S
modifier and inPostgres 8.4 to add the + modifier.
Psql Tip #080
\dm will display the list of user created and visible
materialized views. \dm pattern will list
materialized views matching the pattern.
The + modifier will also display persistence, access methode,
size and description.
The S modifier will also display the system materialized
views. There is currently no materialized views provided by Postgres.
laetitia=# \dm
List of relations
Schema | Name | Type | Owner
--------+------+-------------------+----------
public | mymv | materialized view | laetitia
(1 row)
laetitia=# \dm+
List of relations
Schema | Name | Type | Owner | Persistence | Access Method | Size | Description
--------+------+-------------------+----------+-------------+---------------+-------+-------------
public | mymv | materialized view | laetitia | permanent | heap | 16 kB |
(1 row)
laetitia=# \dmS
List of relations
Schema | Name | Type | Owner
--------+------+-------------------+----------
public | mymv | materialized view | laetitia
(1 row) Psql Tip #081
\ds will display the list of user created and visible
sequences. \ds pattern will list
sequences matching the pattern.
The + modifier will also display persistence,
size and description.
The S modifier will also display the system sequences.
laetitia=# \ds
List of relations
Schema | Name | Type | Owner
--------+-----------------------+----------+----------
public | mytest_id_seq | sequence | test
public | partition_test_id_seq | sequence | laetitia
public | test_id_seq | sequence | laetitia
(3 rows)
(3 rows)
laetitia=# \ds+
Schema | Name | Type | Owner | Persistence | Size | Description
--------+-----------------------+----------+----------+-------------+------------+-------------
public | mytest_id_seq | sequence | test | permanent | 8192 bytes |
public | partition_test_id_seq | sequence | laetitia | permanent | 8192 bytes |
public | test_id_seq | sequence | laetitia | permanent | 8192 bytes |
List of relations
laetitia=# \dsS
List of relations
Schema | Name | Type | Owner
--------+-----------------------+----------+----------
public | mytest_id_seq | sequence | test
public | partition_test_id_seq | sequence | laetitia
public | test_id_seq | sequence | laetitia
(3 rows)S
modifier and in Postgres 8.4 to add the + modifier.
Psql Tip #082
\dt will display the list of user created and visible
tables. \dt pattern will list
tables matching the pattern.
The + modifier will also display persistence,
size and description.
The S modifier will also display the system tables.
laetitia=# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------------+-------------------+----------
public | animal | table | laetitia
public | first_partition | table | laetitia
public | mytest | table | test
public | partition_test | partitioned table | laetitia
public | test | table | laetitia
(5 rows)
laetitia=# \dt+
List of relations
Schema | Name | Type | Owner | Persistence | Access Method | Size | Description
--------+-----------------+-------------------+----------+-------------+---------------+------------+---------------
public | animal | table | laetitia | permanent | heap | 16 kB |
public | first_partition | table | laetitia | permanent | heap | 8192 bytes |
public | mytest | table | test | permanent | heap | 0 bytes |
public | partition_test | partitioned table | laetitia | permanent | | 0 bytes |
public | test | table | laetitia | permanent | heap | 16 kB | table comment
(5 rows)
laetitia=# \dtS
List of relations
Schema | Name | Type | Owner
------------+-------------------------+-------------------+----------
pg_catalog | pg_aggregate | table | postgres
pg_catalog | pg_am | table | postgres
[...]
public | partition_test | partitioned table | laetitia
public | test | table | laetitia
(67 rows)S
modifier and in Postgres 8.4 to add the + modifier.
Psql Tip #083
\dv will display the list of user created and visible
views. \dv pattern will list
views matching the pattern.
The + modifier will also display persistence,
size and description.
The S modifier will also display the system views.
laetitia=# \dv
List of relations
Schema | Name | Type | Owner
--------+--------+------+----------
public | myview | view | laetitia
(1 row)
laetitia=# \dv+
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+--------+------+----------+-------------+---------+-------------
public | myview | view | laetitia | permanent | 0 bytes |
(1 row)
laetitia=# \dvS
List of relations
Schema | Name | Type | Owner
------------+---------------------------------+------+----------
pg_catalog | pg_available_extension_versions | view | postgres
pg_catalog | pg_available_extensions | view | postgres
[...]
pg_catalog | pg_views | view | postgres
public | myview | view | laetitia
(72 rows)S
modifier and in Postgres 8.4 to add the + modifier.
Psql Tip #084
\des will display a list of user create and visible foreign
servers. \des pattern will list
foreign servers matching the pattern.
The + modifier will also display access privileges, type, version, FDW option and description.
laetitia=# \des
List of foreign servers
Name | Owner | Foreign-data wrapper
-------+----------+----------------------
pglog | laetitia | file_fdw
(1 row)
laetitia=# \des+
List of foreign servers
Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW options | Description
-------+----------+----------------------+-------------------+------+---------+-------------+-------------
pglog | laetitia | file_fdw | | | | |
(1 row)Psql Tip #085
\det will display a list of user created and visible foreign
tables. \det pattern will list
foreign ables matching the pattern.
The + modifier will also display FDW option and description.
laetitia=# \det
List of foreign tables
Schema | Table | Server
--------+-------+--------
public | logs | pglog
(1 row)
laetitia=# \det+
List of foreign tables
Schema | Table | Server | FDW options | Description
--------+-------+--------+-----------------------------------------------+-------------
public | logs | pglog | (filename 'postgresql-Sun.csv', format 'csv') |
(1 row)Psql Tip #086
\df will display a list of user created and visible
functions. \df pattern will list
functions matching the pattern.
The + modifier will also display volatility, parallel safety,
owner, security, access privileges, language, source code and description.
The S modifier will also display the system functions.
laetitia=# \df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+--------------------+------------------+---------------------+------
public | file_fdw_handler | fdw_handler | | func
public | file_fdw_validator | void | text[], oid | func
(2 rows)
laetitia=# \df+
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
--------+--------------------+------------------+---------------------+------+------------+----------+----------+----------+-------------------+----------+--------------------+-------------
public | file_fdw_handler | fdw_handler | | func | volatile | unsafe | laetitia | invoker | | c | file_fdw_handler |
public | file_fdw_validator | void | text[], oid | func | volatile | unsafe | laetitia | invoker | | c | file_fdw_validator |
(2 rows)
laetitia=# \dfS xpath*
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+--------------+------------------+---------------------+------
pg_catalog | xpath | xml[] | text, xml | func
pg_catalog | xpath | xml[] | text, xml, text[] | func
pg_catalog | xpath_exists | boolean | text, xml | func
pg_catalog | xpath_exists | boolean | text, xml, text[] | func
(4 rows)+ modifier and in Postgres 8.4 to add the S modifier.
Psql Tip #087
You can use the
a modifier to list only aggregate functions with the
\df meta command.
laetitia=# \dfa variance
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+----------+------------------+---------------------+------
pg_catalog | variance | numeric | bigint | agg
pg_catalog | variance | double precision | double precision | agg
pg_catalog | variance | numeric | integer | agg
pg_catalog | variance | numeric | numeric | agg
pg_catalog | variance | double precision | real | agg
pg_catalog | variance | numeric | smallint | agg
(6 rows)Psql Tip #088
You can use the
n modifier to list only "normal" functions with the
\df meta command.
laetitia=# \dfn
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+--------------------+------------------+---------------------+------
public | file_fdw_handler | fdw_handler | | func
public | file_fdw_validator | void | text[], oid | func
(2 rows)Psql Tip #089
You can use the
p modifier to list only procedures with the
\df meta command.
laetitia=# \dfp
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+------------+------------------+---------------------+------
public | inset_data | | a text | proc
(1 row)Psql Tip #090
You can use the
t modifier to list only "trigger" functions with the
\df meta command.
laetitia=# \dftS
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+------------------------------------+------------------+---------------------+------
pg_catalog | RI_FKey_cascade_del | trigger | | func
pg_catalog | RI_FKey_cascade_upd | trigger | | func
pg_catalog | RI_FKey_check_ins | trigger | | func
pg_catalog | RI_FKey_check_upd | trigger | | func
pg_catalog | RI_FKey_noaction_del | trigger | | func
pg_catalog | RI_FKey_noaction_upd | trigger | | func
pg_catalog | RI_FKey_restrict_del | trigger | | func
pg_catalog | RI_FKey_restrict_upd | trigger | | func
pg_catalog | RI_FKey_setdefault_del | trigger | | func
pg_catalog | RI_FKey_setdefault_upd | trigger | | func
pg_catalog | RI_FKey_setnull_del | trigger | | func
pg_catalog | RI_FKey_setnull_upd | trigger | | func
pg_catalog | suppress_redundant_updates_trigger | trigger | | func
pg_catalog | trigger_in | trigger | cstring | func
pg_catalog | tsvector_update_trigger | trigger | | func
pg_catalog | tsvector_update_trigger_column | trigger | | func
pg_catalog | unique_key_recheck | trigger | | func
(17 rows)Psql Tip #091
You can use the
w modifier to list only "window" functions with the
\df meta command.
laetitia=# \dfwS
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+--------------+------------------+---------------------------------------+--------
pg_catalog | cume_dist | double precision | | window
pg_catalog | dense_rank | bigint | | window
pg_catalog | first_value | anyelement | anyelement | window
pg_catalog | lag | anycompatible | anycompatible, integer, anycompatible | window
pg_catalog | lag | anyelement | anyelement | window
pg_catalog | lag | anyelement | anyelement, integer | window
pg_catalog | last_value | anyelement | anyelement | window
pg_catalog | lead | anycompatible | anycompatible, integer, anycompatible | window
pg_catalog | lead | anyelement | anyelement | window
pg_catalog | lead | anyelement | anyelement, integer | window
pg_catalog | nth_value | anyelement | anyelement, integer | window
pg_catalog | ntile | integer | integer | window
pg_catalog | percent_rank | double precision | | window
pg_catalog | rank | bigint | | window
pg_catalog | row_number | bigint | | window
(15 rows)Psql Tip #092
The description column displayed when using the
+ modifier of a \d
metacommand can be filled using the comment SQL command.
laetitia=# \d+ test
Table "public.test"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+------------------------------+----------+-------------+--------------+----------------
id | integer | | not null | generated always as identity | plain | | |
value | text | | not null | | extended | pglz | | column comment
Indexes:
"test_value_key" UNIQUE CONSTRAINT, btree (value)
Access method: heap
laetitia=# comment on column test.id IS 'My awesome comment';
COMMENT
laetitia=# \d+ test
Table "public.test"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+------------------------------+----------+-------------+--------------+--------------------
id | integer | | not null | generated always as identity | plain | | | My awesome comment
value | text | | not null | | extended | pglz | | column comment
Indexes:
"test_value_key" UNIQUE CONSTRAINT, btree (value)
Access method: heapcomment SQL command. the + modifier
appeared in different version, depending on the metacommand.
Psql Tip #093
\dg will display a list of user created and visible
roles. \dg pattern will list
roles matching the pattern.
The + modifier will also display description.
The S modifier will also display the system roles.
laetitia=# \dg
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
laetitia | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test | | {}
laetitia=# \dg+
List of roles
Role name | Attributes | Member of | Description
-----------+------------------------------------------------------------+-----------+-------------
laetitia | Superuser | {} |
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} |
test | | {} |
laetitia=# \dgS
List of roles
Role name | Attributes | Member of
---------------------------+------------------------------------------------------------+--------------------------------------------------------------
laetitia | Superuser | {}
pg_execute_server_program | Cannot login | {}
pg_monitor | Cannot login | {pg_read_all_settings,pg_read_all_stats,pg_stat_scan_tables}
pg_read_all_settings | Cannot login | {}
pg_read_all_stats | Cannot login | {}
pg_read_server_files | Cannot login | {}
pg_signal_backend | Cannot login | {}
pg_stat_scan_tables | Cannot login | {}
pg_write_server_files | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test | | {} + modifier and in Postgres 9.6 to add the S modifier.
Psql Tip #094
\dl will list the large objects in this database.
laetitia=# \dl
Large objects
ID | Owner | Description
----+-------+-------------
(0 rows)Psql Tip #095
\dL will display a list of user created and visible
procedural languages. \df pattern will list
functions matching the pattern.
The + modifier will also display if it's an internal language,
the call handler, the validator, the inline handler,
access privileges and description.
The S modifier will also display the system procedural languages.
laetitia=# \dL
List of languages
Name | Owner | Trusted | Description
---------+----------+---------+------------------------------
plpgsql | postgres | t | PL/pgSQL procedural language
(1 row)
laetitia=# \dL+
List of languages
Name | Owner | Trusted | Internal language | Call handler | Validator | Inline handler | Access privileges | Description
---------+----------+---------+-------------------+------------------------+------------------------+----------------------------------+-------------------+------------------------------
plpgsql | postgres | t | f | plpgsql_call_handler() | plpgsql_validator(oid) | plpgsql_inline_handler(internal) | | PL/pgSQL procedural language
(1 row)
laetitia=# \dLS
List of languages
Name | Owner | Trusted | Description
----------+----------+---------+--------------------------------
c | postgres | f | dynamically-loaded C functions
internal | postgres | f | built-in functions
plpgsql | postgres | t | PL/pgSQL procedural language
sql | postgres | t | SQL-language functions
(4 rows)Psql Tip #096
\dp will display a list of tables, views and sequences
with their associated access privileges.
\dp pattern will list
only tables, views and sequences whose names match the pattern are listed.
The access privileges column signification is explained in the
Postgres documentation.
ilaetitia=# \dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+-----------------------+-------------------+---------------------------+-------------------+----------
public | animal | table | | |
public | first_partition | table | | |
public | logs | foreign table | | |
public | mymv | materialized view | | |
public | mytest | table | | |
public | mytest_id_seq | sequence | | |
public | myview | view | | |
public | partition_test | partitioned table | | |
public | partition_test_id_seq | sequence | | |
public | test | table | laetitia=arwdDxt/laetitia | |
public | test_id_seq | sequence | | |
(11 rows)Psql Tip #097
\dP will display a list of partitioned tables and indexes.
\dP pattern will list partitioned tables and indexes
matching the pattern.
The + modifier will also display the size and the
description.
laetitia=# \dP
List of partitioned relations
Schema | Name | Owner | Type | Table
--------+-----------------------+----------+-------------------+----------------
public | partition_test | laetitia | partitioned table |
public | partition_test_id_idx | laetitia | partitioned index | partition_test
(2 rows)
laetitia=# \dP+
List of partitioned relations
Schema | Name | Owner | Type | Table | Total size | Description
--------+-----------------------+----------+-------------------+----------------+------------+-------------
public | partition_test | laetitia | partitioned table | | 8192 bytes |
public | partition_test_id_idx | laetitia | partitioned index | partition_test | 8192 bytes |
(2 rows)Psql Tip #098
the
i modifier appended to the dP metacommand will list only
partitioned indexes.
ilaetitia=# \dPi
List of partitioned indexes
Schema | Name | Owner | Table
--------+-----------------------+----------+----------------
public | partition_test_id_idx | laetitia | partition_test
(1 row)Psql Tip #099
the
t modifier appended to the dP metacommand will list only
partitioned tables.
laetitia=# \dPt
List of partitioned tables
Schema | Name | Owner
--------+----------------+----------
public | partition_test | laetitia
(1 row)
laetitia=# \dPt+
List of partitioned tables
Schema | Name | Owner | Total size | Description
--------+----------------+----------+------------+-------------
public | partition_test | laetitia | 8192 bytes |
(1 row)Psql Tip #100
the
n modifier appended to the dP metacommand will list also
non-root partitioned tables and indexes. A column displaying also the
parent name is added to the result.
laetitia=# \dPn
List of partitioned relations
Schema | Name | Owner | Type | Parent name | Table
--------+-----------------------+----------+-------------------+-------------+----------------
public | partition_test | laetitia | partitioned table | |
public | partition_test_id_idx | laetitia | partitioned index | | partition_test
(2 rows)
laetitia=# \dPn+
List of partitioned relations
Schema | Name | Owner | Type | Parent name | Table | Leaf partition size | Total size | Description
--------+-----------------------+----------+-------------------+-------------+----------------+---------------------+------------+-------------
public | partition_test | laetitia | partitioned table | | | 8192 bytes | 8192 bytes |
public | partition_test_id_idx | laetitia | partitioned index | | partition_test | 8192 bytes | 8192 bytes |
(2 rows)Psql Tip #101
\copy ... to ... will allow you to copy data to a file on the client
host.
laetitia=# \copy (select * from test) to mydata.dmp
COPY 2
laetitia=# \! cat mydata.dmp
1 test
5 blablaPsql Tip #102
\copy will allow you to copy data from something into a table (something
might be the result of a command, a program, stdin...)
laetitia=# \copy test(value) 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.
>> value1
>> value2
>> \.
COPY 2
laetitia=# select * from test;
id | value
----+--------
1 | test
5 | blabla
6 | value1
7 | value2
(4 rows)Psql Tip #103
\copy is different than the SQL command copy in that the \copy
meta-command will be played from the client side without superuser
permissions while the copy SQL command needs database superuser or users
users who are granted one of the default roles pg_read_server_files,
pg_write_server_files, or pg_execute_server_program.
laetitia=# \c laetitia test
You are now connected to database "laetitia" as user "test".
laetitia=> \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
laetitia | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test | | {}
laetitia=> \copy (select * from test) to mydata.dmp
COPY 4
laetitia=> \! cat mydata.dmp
1 test
5 blabla
6 value1
7 value2Psql Tip #104
\copy is less efficient than the SQL command copy because all data
must pass through the client/server connection. For large amount of data,
the SQL command is better.
This feature is available since at least Postgres 7.1.
Psql Tip #105
The default delimiter for text entry in stdin (either using the
\copy
meta-command or the copy SQL command) is a tab character.
laetitia=> create table data(d1 integer, d2 date);
CREATE TABLE
laetitia=> \copy data 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 '2021-04-12'
>> 2 '2021-04-13'
>> \.
ERROR: invalid input syntax for type integer: "2 '2021-04-13'"
CONTEXT: COPY data, line 2, column d1: "2 '2021-04-13'"Psql Tip #106
\crosstabview is a meta-command that will create a crosstabview from the
query in the current query buffer.
By default the first column will become the column header while the second
column will become the horizontal header.
The data from the third column will fill the table.
laetitia=> select * from music ;
artist | album | year
-----------------------+-------------------+------
Foo Fighters | Concrete and Gold | 2017
Red Hot Chili Peppers | The Getaway | 2016
Green Day | ¡Uno! | 2012
(3 rows)
laetitia=> \crosstabview
artist | Concrete and Gold | The Getaway | ¡Uno!
-----------------------+-------------------+-------------+-------
Foo Fighters | 2017 | |
Red Hot Chili Peppers | | 2016 |
Green Day | | | 2012
(3 rows)Psql Tip #107
You can pass the name of the columns to use as Vertical and Horizontal
headers to the
\crosstabview meta-command.
laetitia=> select * from music ;
artist | album | year
-----------------------+-------------------+------
Foo Fighters | Concrete and Gold | 2017
Red Hot Chili Peppers | The Getaway | 2016
Green Day | ¡Uno! | 2012
(3 rows)
laetitia=> laetitia=> \crosstabview artist year
artist | 2017 | 2016 | 2012
-----------------------+-------------------+-------------+-------
Foo Fighters | Concrete and Gold | |
Red Hot Chili Peppers | | The Getaway |
Green Day | | | ¡Uno!
(3 rows)Psql Tip #108
You can pass the number of the columns to use as Vertical and Horizontal
headers to the
\crosstabview meta-command as you would use number of
columns in an order by SQL clause.
laetitia=> select * from music ;
artist | album | year
-----------------------+-------------------+------
Foo Fighters | Concrete and Gold | 2017
Red Hot Chili Peppers | The Getaway | 2016
Green Day | ¡Uno! | 2012
(3 rows)
laetitia=> laetitia=> \crosstabview 1 3
artist | 2017 | 2016 | 2012
-----------------------+-------------------+-------------+-------
Foo Fighters | Concrete and Gold | |
Red Hot Chili Peppers | | The Getaway |
Green Day | | | ¡Uno!
(3 rows)Psql Tip #109
The
\drds meta-command will display the specific role based or database
based settings.
laetitia=# \drds
List of settings
Role | Database | Settings
----------+----------+--------------------
laetitia | | work_mem=250MB
| laetitia | search_path=public+
| | work_mem=500MB
(2 rows)
laetitia=# \drds * laetitia
List of settings
Role | Database | Settings
------+----------+--------------------
| laetitia | search_path=public+
| | work_mem=500MB
(1 row)
laetitia=# \drds laetitia
List of settings
Role | Database | Settings
----------+----------+----------------
laetitia | | work_mem=250MB
(1 row)Psql Tip #110
\dT will display a list of data types. The S modifier will allow to
list system data types too. \dT pattern will list data types matching
the pattern. the + Internal name, size, Elements, Owner and Access
privileges.
laetitia=# \dT
List of data types
Schema | Name | Description
--------+---------------+-------------
public | one_word_only |
(1 row)
laetitia=# \dTS line
List of data types
Schema | Name | Description
------------+------+----------------
pg_catalog | line | geometric line
(1 row)
laetitia=# \dT+
List of data types
Schema | Name | Internal name | Size | Elements | Owner | Access privileges | Description
--------+---------------+---------------+------+----------+----------+-------------------+-------------
public | one_word_only | one_word_only | var | | laetitia | |
(1 row)Psql Tip #111
\da will display a list of user created and visible
aggregate functions, together with their result data type, their
argument data types and a description. When there is no user created
aggregate, the list is empty.
The S modifier will also display the system aggregate
functions and a pattern can be appended to filter them.
laetitia=# \da
List of aggregate functions
Schema | Name | Result data type | Argument data types | Description
--------+------+------------------+---------------------+-------------
(0 rows)
laetitia=# \daS sum
List of aggregate functions
Schema | Name | Result data type | Argument data types | Description
------------+------+------------------+---------------------+--------------------------------------------------
pg_catalog | sum | numeric | bigint | sum as numeric across all bigint input values
pg_catalog | sum | double precision | double precision | sum as float8 across all float8 input values
pg_catalog | sum | bigint | integer | sum as bigint across all integer input values
pg_catalog | sum | interval | interval | sum as interval across all interval input values
pg_catalog | sum | money | money | sum as money across all money input values
pg_catalog | sum | numeric | numeric | sum as numeric across all numeric input values
pg_catalog | sum | real | real | sum as float4 across all float4 input values
pg_catalog | sum | bigint | smallint | sum as bigint across all smallint input values
(8 rows)Psql Tip #112
\du will display a list of users. Remember that since Postgres 8.1,
users and roles are the same thing (see Postgres
documentation).The
S modifier will allow to display system roles too.
du pattern will match only roles macthing the specified pattern. The
+ modifier will display the description for each role.
laetitia=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
laetitia | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test | | {}
laetitia=# \duS
List of roles
Role name | Attributes | Member of
---------------------------+------------------------------------------------------------+--------------------------------------------------------------
laetitia | Superuser | {}
pg_database_owner | Cannot login | {}
pg_execute_server_program | Cannot login | {}
pg_monitor | Cannot login | {pg_read_all_settings,pg_read_all_stats,pg_stat_scan_tables}
pg_read_all_data | Cannot login | {}
pg_read_all_settings | Cannot login | {}
pg_read_all_stats | Cannot login | {}
pg_read_server_files | Cannot login | {}
pg_signal_backend | Cannot login | {}
pg_stat_scan_tables | Cannot login | {}
pg_write_all_data | Cannot login | {}
pg_write_server_files | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test | | {}
laetitia=# \du+
List of roles
Role name | Attributes | Member of | Description
-----------+------------------------------------------------------------+-----------+-------------
laetitia | Superuser | {} |
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} |
test | | {} | + modifier and in Postgres 9.6 to add the S modifier.
Psql Tip #113
Since Postgres 8.1, groups and users are implemented as roles. It means there is not difference between\dg and \du.
laetitia=# \dg
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-------------
grouptest | Cannot login | {}
laetitia | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test | | {grouptest}
laetitia=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-------------
grouptest | Cannot login | {}
laetitia | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test | | {grouptest}Psql Tip #114
The
\dx metacommand will display a list of installed
extensions. \dx pattern will display only extensions matching the given
pattern. The + modifier will allow to display a list of all the objects
belonging to each extension.
laetitia=# \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(1 row)
laetitia=# \dx+
Objects in extension "plpgsql"
Object description
-------------------------------------------
function plpgsql_call_handler()
function plpgsql_inline_handler(internal)
function plpgsql_validator(oid)
language plpgsql
(4 rows)Psql Tip #115
The
If none of those variables are set, the default editor is
This feature is available since at least Postgres 7.1 for Unix system and since Postgres run under Windows for Windows version.
\e (or \edit) will open the current querry buffer
into the default editor (or the editor set by the PSQL_EDITOR, the
EDITOR or the VISUAL variables.If none of those variables are set, the default editor is
vi on Unix systems
and notepad.exe under Windows. This feature is available since at least Postgres 7.1 for Unix system and since Postgres run under Windows for Windows version.
Psql Tip #116
You can open a file under psql using the
If the modifications are not saved before the editor is quitted, the query buffer is cleared.
If a query ends by a semi-colon, it will be immediately executed. The file can contain several queries. psql will play the file as a single line command meaning that anything right after a psql's metacoomand will be considered as an argument to the metacommand even though there are some line breaks.
This feature is available since at least Postgres 7.1.
Before Postgres 14, even if you didn't save the modifications before exiting, they were sent to the query buffer (and immediately executed if ended by a semi-colon).
\e filename.
The file will open in the editor and after saving and exiting the editor, the content
of the file will be copied into the query buffer.If the modifications are not saved before the editor is quitted, the query buffer is cleared.
If a query ends by a semi-colon, it will be immediately executed. The file can contain several queries. psql will play the file as a single line command meaning that anything right after a psql's metacoomand will be considered as an argument to the metacommand even though there are some line breaks.
This feature is available since at least Postgres 7.1.
Before Postgres 14, even if you didn't save the modifications before exiting, they were sent to the query buffer (and immediately executed if ended by a semi-colon).
Psql Tip #117
You can use the metacommand
If the filename is omitted, psql will open the current querry buffer (if empty the last executed query will be copied into the current querry buffer) and position the cursor on the specified line number. It means that if a single all-digits argument is given, psql will assume it's a line number.
If the line number specified is higher than the number of lines in the querry buffer or in the file, the cursor will be positionned on the last line.
This feature is available since Postgres 9.1.
\e filename linenumber to
open a file and position the cursor directly on the specified
line.If the filename is omitted, psql will open the current querry buffer (if empty the last executed query will be copied into the current querry buffer) and position the cursor on the specified line number. It means that if a single all-digits argument is given, psql will assume it's a line number.
If the line number specified is higher than the number of lines in the querry buffer or in the file, the cursor will be positionned on the last line.
This feature is available since Postgres 9.1.
Psql Tip #118
Use
You can get rid of the trailing new line by using
\echo to display text or evaluated argument to the
standard output.You can get rid of the trailing new line by using
-n as a first argument.
laetitia=# \echo test
test
laetitia=# \echo `date`
Fri Jul 30 10:13:41 CEST 2021
laetitia=# \echo -n `date`
Fri Jul 30 10:13:47 CEST 2021laetitia=#Psql Tip #119
While
\echo will send the result in the standard output,
use \qecho if you'd like the result to be written inside a file you
specified using the \o metacommand.
laetitia=# \o /tmp/output.out
laetitia=# \echo `date`
Fri Jul 30 10:23:58 CEST 2021
laetitia=# \! cat /tmp/output.out
laetitia=# \qecho `date`
laetitia=# \! cat /tmp/output.out
Fri Jul 30 10:24:25 CEST 2021Psql Tip #120
The
If there are several functions with the same name, you will need to add the signature of the function so psql knows which one needs modification.
\ef function metacommand will open the create or
replace function (or create or replace procedure) containing the code of
the function (or procedure) you named.If there are several functions with the same name, you will need to add the signature of the function so psql knows which one needs modification.
laetitia=# \df add_em
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+--------+------------------+----------------------------------------+------
public | add_em | double precision | x double precision, y double precision | func
public | add_em | integer | x integer, y integer | func
(2 rows)
laetitia=# \ef add_em
ERROR: more than one function named "add_em"
laetitia=# \ef add_em(integer, integer)
No changesPsql Tip #121
The
\ef function linenumber metacommand will open the create or
replace function (or create or replace procedure) containing the code of
the function (or procedure) you named and will position the cursor directly
to the line number specified.
This feature is available since Postgres 9.1. Procedures were added with Postgres 11.
Psql Tip #122
The
If no function name is specified, a
\ef function metacommand will open the create or
replace function (or create or replace procedure) containing the code of
the function (or procedure) you named.If no function name is specified, a
create function template is created.
This feature is available
since Postgres 9.1. Procedures were added with Postgres 11.
Psql Tip #123
The
\ev view metacommand will open the create or
replace view containing the SQL code of
the view you named.laetitia=# \dv connection_settings
List of relations
Schema | Name | Type | Owner
--------+---------------------+------+----------
public | connection_settings | view | laetitia
(1 row)
laetitia=# \ev connection_settings
laetitia=# CREATE OR REPLACE VIEW public.connection_settings AS
SELECT pg_settings.name,
pg_settings.setting,
pg_settings.unit
FROM pg_settings
WHERE pg_settings.name ~ 'connection'::textPsql Tip #124
The
If no view name is specified, a
\ev view metacommand will open the create or
replace view containing the SQL code of
the view you named.If no view name is specified, a
create view template is created.
This feature is available since Postgres 9.6.
Psql Tip #125
The
\ev view linenumber metacommand will open the create or
replace view containing the code of the view you named and will position the cursor directly
to the line number specified.
This feature is available since Postgres 9.6.
Psql Tip #126
The
\g metacommand will execute the last query in the
query buffer.
laetitia=# select * from test;
id | value
----+-------
1 | bla
2 | bla
3 | bla
4 | bla
5 | bla
6 | bla
(6 rows)
laetitia=# \g
id | value
----+-------
1 | bla
2 | bla
3 | bla
4 | bla
5 | bla
6 | bla
(6 rows)Psql Tip #127
You can use the
\g metacommand to execute a query as an
alternative to the semicolon character (;).
laetitia=# select * from test\g
id | value
----+-------
1 | bla
2 | bla
3 | bla
4 | bla
5 | bla
6 | bla
(6 rows)Psql Tip #128
You can use the
\g filename metacommand to execute a query
and store the result in the file named filename. Unless the complete path
is given, the file will be stored in the current directory (that you can
display with \! pwd
laetitia=# select * from test;
id | value
----+-------
1 | bla
2 | bla
3 | bla
4 | bla
5 | bla
6 | bla
(6 rows)
laetitia=# \g output.log
laetitia=# \! cat output.log
id | value
----+-------
1 | bla
2 | bla
3 | bla
4 | bla
5 | bla
6 | bla
(6 rows)
laetitia=# \! pwd
/Users/laetitia/tech/laetitia/psql-tips/toolsPsql Tip #129
You can use the
\g | command metacommand to send the
query result to a shell command.
laetitia=# select setting
laetitia-# from pg_settings
laetitia-# where name= 'data_directory';
setting
-----------------------
/usr/local/pgsql/data
(1 row)
laetitia=# \g |grep 'data'
/usr/local/pgsql/dataPsql Tip #130
You can use format options combined with the
\g
metacommand to re-execute the previous query with formatting options.
laetitia=# select * from test;
id | value
----+-------
1 | bla
2 | bla
3 | bla
4 | bla
5 | bla
6 | bla
(6 rows)
laetitia=# \g (footer=off format=csv)
id,value
1,bla
2,bla
3,bla
4,bla
5,bla
6,blaPsql Tip #131
The
\gdesc
metacommand will show the description (column names and datatypes) of the
current query buffer. The query is not actually executed.
laetitia=# select * from test;
id | value
----+-------
1 | bla
2 | bla
3 | bla
4 | bla
5 | bla
6 | bla
(6 rows)
laetitia=# \gdesc
Column | Type
--------+---------
id | integer
value | text
(2 rows)
Psql Tip #132
The
\gexec
metacommand will execute the result of the current query or the last query
if the current query buffer is empty. This is a very useful feature to
generate DDL and execute it in one go.
laetitia=*# select 'drop table ' || table_name
laetitia-*# from information_schema.tables
laetitia-*# where table_schema = 'public'
laetitia-*# and table_name ~ 'test';
?column?
------------------
drop table test
drop table test2
drop table test3
(3 rows)
laetitia=*# \gexec
DROP TABLE
DROP TABLE
DROP TABLEPsql Tip #133
The
\gset
metacommand will execute the result of the current query or the last query
if the current query buffer is empty and assign variables named as the
column names with the value. It only works if the query returns only one
row.
laetitia=# select *
laetitia-# from test
laetitia-# where id=5;
id | value
----+-------
5 | bla
(1 row)
laetitia=# \gset
laetitia=# \echo 'id: ' :id ', value: ' :value
id: 5 , value: blaPsql Tip #134
The
\gset prefix
metacommand will execute the result of the current query or the last query
if the current query buffer is empty and assign variables named as the
column names with the given prefix and the value. It only works if the query returns only one
row.
laetitia=# laetitia=# select *
from test
where id=5;
id | value
----+-------
5 | bla
(1 row)
laetitia=# \gset test_
laetitia=# \echo 'id: ' :test_id ', value: ' :test_value
id: 5 , value: blaPsql Tip #135
The
\gx
metacommand will execute the result of the current query or the last query
if the current query buffer but will force the expanded output mode.
laetitia=# select * from pg_settings where name = 'log_directory';
name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val | enumvals | boot_val | reset_val | sourcefile | sourceline | pending_restart
---------------+---------+------+--------------------------------------+-----------------------------------------------+-------------------------------------------------------------------------+---------+---------+---------+---------+---------+----------+----------+-----------+------------+------------+-----------------
log_directory | log | | Reporting and Logging / Where to Log | Sets the destination directory for log files. | Can be specified as relative to the data directory or as absolute path. | sighup | string | default | | | | log | log | | | f
(1 row)
laetitia=# \gx
-[ RECORD 1 ]---+------------------------------------------------------------------------
name | log_directory
setting | log
unit |
category | Reporting and Logging / Where to Log
short_desc | Sets the destination directory for log files.
extra_desc | Can be specified as relative to the data directory or as absolute path.
context | sighup
vartype | string
source | default
min_val |
max_val |
enumvals |
boot_val | log
reset_val | log
sourcefile |
sourceline |
pending_restart | fPsql Tip #136
As the
\g metacommand, the \gx
metacommand can send the result of a query in a file.
laetitia=# select * from pg_settings where name = 'log_directory';
name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val | enumvals | boot_val | reset_val | sourcefile | sourceline | pending_restart
---------------+---------+------+--------------------------------------+-----------------------------------------------+-------------------------------------------------------------------------+---------+---------+---------+---------+---------+----------+----------+-----------+------------+------------+-----------------
log_directory | log | | Reporting and Logging / Where to Log | Sets the destination directory for log files. | Can be specified as relative to the data directory or as absolute path. | sighup | string | default | | | | log | log | | | f
(1 row)
laetitia=# \gx log_directory.output
laetitia=# \! cat log_directory.output
-[ RECORD 1 ]---+------------------------------------------------------------------------
name | log_directory
setting | log
unit |
category | Reporting and Logging / Where to Log
short_desc | Sets the destination directory for log files.
extra_desc | Can be specified as relative to the data directory or as absolute path.
context | sighup
vartype | string
source | default
min_val |
max_val |
enumvals |
boot_val | log
reset_val | log
sourcefile |
sourceline |
pending_restart | fPsql Tip #137
As the
\g metacommand, the \gx
metacommand can send the result of a query to a command.
laetitia=# select * from pg_settings where name = 'log_directory';
name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val | enumvals | boot_val | reset_val | sourcefile | sourceline | pending_restart
---------------+---------+------+--------------------------------------+-----------------------------------------------+-------------------------------------------------------------------------+---------+---------+---------+---------+---------+----------+----------+-----------+------------+------------+-----------------
log_directory | log | | Reporting and Logging / Where to Log | Sets the destination directory for log files. | Can be specified as relative to the data directory or as absolute path. | sighup | string | default | | | | log | log | | | f
(1 row)
laetitia=# \gx | grep -i 'log'
name | log_directory
setting | log
category | Reporting and Logging / Where to Log
short_desc | Sets the destination directory for log files.
boot_val | log
reset_val | logPsql Tip #138
The
\h metacommand will give the syntax of any SQL
command.
laetitia=# \h
Available help:
ABORT ALTER SYSTEM CREATE FOREIGN DATA WRAPPER CREATE USER MAPPING DROP ROUTINE NOTIFY
ALTER AGGREGATE ALTER TABLE CREATE FOREIGN TABLE CREATE VIEW DROP RULE PREPARE
ALTER COLLATION ALTER TABLESPACE CREATE FUNCTION DEALLOCATE DROP SCHEMA PREPARE TRANSACTION
ALTER CONVERSION ALTER TEXT SEARCH CONFIGURATION CREATE GROUP DECLARE DROP SEQUENCE REASSIGN OWNED
ALTER DATABASE ALTER TEXT SEARCH DICTIONARY CREATE INDEX DELETE DROP SERVER REFRESH MATERIALIZED VIEW
ALTER DEFAULT PRIVILEGES ALTER TEXT SEARCH PARSER CREATE LANGUAGE DISCARD DROP STATISTICS REINDEX
ALTER DOMAIN ALTER TEXT SEARCH TEMPLATE CREATE MATERIALIZED VIEW DO DROP SUBSCRIPTION RELEASE SAVEPOINT
ALTER EVENT TRIGGER ALTER TRIGGER CREATE OPERATOR DROP ACCESS METHOD DROP TABLE RESET
ALTER EXTENSION ALTER TYPE CREATE OPERATOR CLASS DROP AGGREGATE DROP TABLESPACE REVOKE
ALTER FOREIGN DATA WRAPPER ALTER USER CREATE OPERATOR FAMILY DROP CAST DROP TEXT SEARCH CONFIGURATION ROLLBACK
ALTER FOREIGN TABLE ALTER USER MAPPING CREATE POLICY DROP COLLATION DROP TEXT SEARCH DICTIONARY ROLLBACK PREPARED
ALTER FUNCTION ALTER VIEW CREATE PROCEDURE DROP CONVERSION DROP TEXT SEARCH PARSER ROLLBACK TO SAVEPOINT
ALTER GROUP ANALYZE CREATE PUBLICATION DROP DATABASE DROP TEXT SEARCH TEMPLATE SAVEPOINT
ALTER INDEX BEGIN CREATE ROLE DROP DOMAIN DROP TRANSFORM SECURITY LABEL
ALTER LANGUAGE CALL CREATE RULE DROP EVENT TRIGGER DROP TRIGGER SELECT
ALTER LARGE OBJECT CHECKPOINT CREATE SCHEMA DROP EXTENSION DROP TYPE SELECT INTO
ALTER MATERIALIZED VIEW CLOSE CREATE SEQUENCE DROP FOREIGN DATA WRAPPER DROP USER SET
ALTER OPERATOR CLUSTER CREATE SERVER DROP FOREIGN TABLE DROP USER MAPPING SET CONSTRAINTS
ALTER OPERATOR CLASS COMMENT CREATE STATISTICS DROP FUNCTION DROP VIEW SET ROLE
ALTER OPERATOR FAMILY COMMIT CREATE SUBSCRIPTION DROP GROUP END SET SESSION AUTHORIZATION
ALTER POLICY COMMIT PREPARED CREATE TABLE DROP INDEX EXECUTE SET TRANSACTION
ALTER PROCEDURE COPY CREATE TABLE AS DROP LANGUAGE EXPLAIN SHOW
ALTER PUBLICATION CREATE ACCESS METHOD CREATE TABLESPACE DROP MATERIALIZED VIEW FETCH START TRANSACTION
ALTER ROLE CREATE AGGREGATE CREATE TEXT SEARCH CONFIGURATION DROP OPERATOR GRANT TABLE
ALTER ROUTINE CREATE CAST CREATE TEXT SEARCH DICTIONARY DROP OPERATOR CLASS IMPORT FOREIGN SCHEMA TRUNCATE
ALTER RULE CREATE COLLATION CREATE TEXT SEARCH PARSER DROP OPERATOR FAMILY INSERT UNLISTEN
ALTER SCHEMA CREATE CONVERSION CREATE TEXT SEARCH TEMPLATE DROP OWNED LISTEN UPDATE
ALTER SEQUENCE CREATE DATABASE CREATE TRANSFORM DROP POLICY LOAD VACUUM
ALTER SERVER CREATE DOMAIN CREATE TRIGGER DROP PROCEDURE LOCK VALUES
ALTER STATISTICS CREATE EVENT TRIGGER CREATE TYPE DROP PUBLICATION MERGE WITH
ALTER SUBSCRIPTION CREATE EXTENSION CREATE USER DROP ROLE MOVEPsql Tip #139
The
\help metacommand will give the syntax of any SQL
command.
laetitia=# \help
Available help:
ABORT ALTER SYSTEM CREATE FOREIGN DATA WRAPPER CREATE USER MAPPING DROP ROUTINE NOTIFY
ALTER AGGREGATE ALTER TABLE CREATE FOREIGN TABLE CREATE VIEW DROP RULE PREPARE
ALTER COLLATION ALTER TABLESPACE CREATE FUNCTION DEALLOCATE DROP SCHEMA PREPARE TRANSACTION
ALTER CONVERSION ALTER TEXT SEARCH CONFIGURATION CREATE GROUP DECLARE DROP SEQUENCE REASSIGN OWNED
ALTER DATABASE ALTER TEXT SEARCH DICTIONARY CREATE INDEX DELETE DROP SERVER REFRESH MATERIALIZED VIEW
ALTER DEFAULT PRIVILEGES ALTER TEXT SEARCH PARSER CREATE LANGUAGE DISCARD DROP STATISTICS REINDEX
ALTER DOMAIN ALTER TEXT SEARCH TEMPLATE CREATE MATERIALIZED VIEW DO DROP SUBSCRIPTION RELEASE SAVEPOINT
ALTER EVENT TRIGGER ALTER TRIGGER CREATE OPERATOR DROP ACCESS METHOD DROP TABLE RESET
ALTER EXTENSION ALTER TYPE CREATE OPERATOR CLASS DROP AGGREGATE DROP TABLESPACE REVOKE
ALTER FOREIGN DATA WRAPPER ALTER USER CREATE OPERATOR FAMILY DROP CAST DROP TEXT SEARCH CONFIGURATION ROLLBACK
ALTER FOREIGN TABLE ALTER USER MAPPING CREATE POLICY DROP COLLATION DROP TEXT SEARCH DICTIONARY ROLLBACK PREPARED
ALTER FUNCTION ALTER VIEW CREATE PROCEDURE DROP CONVERSION DROP TEXT SEARCH PARSER ROLLBACK TO SAVEPOINT
ALTER GROUP ANALYZE CREATE PUBLICATION DROP DATABASE DROP TEXT SEARCH TEMPLATE SAVEPOINT
ALTER INDEX BEGIN CREATE ROLE DROP DOMAIN DROP TRANSFORM SECURITY LABEL
ALTER LANGUAGE CALL CREATE RULE DROP EVENT TRIGGER DROP TRIGGER SELECT
ALTER LARGE OBJECT CHECKPOINT CREATE SCHEMA DROP EXTENSION DROP TYPE SELECT INTO
ALTER MATERIALIZED VIEW CLOSE CREATE SEQUENCE DROP FOREIGN DATA WRAPPER DROP USER SET
ALTER OPERATOR CLUSTER CREATE SERVER DROP FOREIGN TABLE DROP USER MAPPING SET CONSTRAINTS
ALTER OPERATOR CLASS COMMENT CREATE STATISTICS DROP FUNCTION DROP VIEW SET ROLE
ALTER OPERATOR FAMILY COMMIT CREATE SUBSCRIPTION DROP GROUP END SET SESSION AUTHORIZATION
ALTER POLICY COMMIT PREPARED CREATE TABLE DROP INDEX EXECUTE SET TRANSACTION
ALTER PROCEDURE COPY CREATE TABLE AS DROP LANGUAGE EXPLAIN SHOW
ALTER PUBLICATION CREATE ACCESS METHOD CREATE TABLESPACE DROP MATERIALIZED VIEW FETCH START TRANSACTION
ALTER ROLE CREATE AGGREGATE CREATE TEXT SEARCH CONFIGURATION DROP OPERATOR GRANT TABLE
ALTER ROUTINE CREATE CAST CREATE TEXT SEARCH DICTIONARY DROP OPERATOR CLASS IMPORT FOREIGN SCHEMA TRUNCATE
ALTER RULE CREATE COLLATION CREATE TEXT SEARCH PARSER DROP OPERATOR FAMILY INSERT UNLISTEN
ALTER SCHEMA CREATE CONVERSION CREATE TEXT SEARCH TEMPLATE DROP OWNED LISTEN UPDATE
ALTER SEQUENCE CREATE DATABASE CREATE TRANSFORM DROP POLICY LOAD VACUUM
ALTER SERVER CREATE DOMAIN CREATE TRIGGER DROP PROCEDURE LOCK VALUES
ALTER STATISTICS CREATE EVENT TRIGGER CREATE TYPE DROP PUBLICATION MERGE WITH
ALTER SUBSCRIPTION CREATE EXTENSION CREATE USER DROP ROLE MOVEPsql Tip #140
The
\h command metacommand will give the syntax of the
specified SQL command.
laetitia=# \h cluster
Command: CLUSTER
Description: cluster a table according to an index
Syntax:
CLUSTER [VERBOSE] table_name [ USING index_name ]
CLUSTER ( option [, ...] ) table_name [ USING index_name ]
CLUSTER [VERBOSE]
where option can be one of:
VERBOSE [ boolean ]
URL: https://www.postgresql.org/docs/15/sql-cluster.htmlPsql Tip #141
The
\h command in multiple words metacommand will give the syntax of the
specified SQL command even if the command consists of multiple words.
laetitia=# \h create trigger
Command: CREATE TRIGGER
Description: define a new trigger
Syntax:
CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
ON table_name
[ FROM referenced_table_name ]
[ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
[ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
[ FOR [ EACH ] { ROW | STATEMENT } ]
[ WHEN ( condition ) ]
EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )
where event can be one of:
INSERT
UPDATE [ OF column_name [, ... ] ]
DELETE
TRUNCATE
URL: https://www.postgresql.org/docs/15/sql-createtrigger.htmlPsql Tip #142
The
\H or \html metacommand will turn on the HTML query output
format.
laetitia=# \H
Output format is html.
laetitia=# select *
laetitia-# from test
laetitia-# where id = 1;
id
value
1
bla
(1 row)
Psql Tip #143
The
\i filename or \include filename metacommand will
read the input from the file filename and execute it.
laetitia=# \! cat test.sql
select * from test;
laetitia=# \i test.sql
id | value
----+-------
1 | bla
2 | bla
3 | bla
4 | bla
5 | bla
6 | bla
(6 rows)Psql Tip #144
Using the \l metacommand without a pattern will show a list
of all visible databases.
laetitia=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+---------+-------+------------+-----------------+-----------------------
laetitia | laetitia | UTF8 | C | UTF-8 | | libc |
postgres | postgres | UTF8 | C | UTF-8 | | libc |
template0 | postgres | UTF8 | C | UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(4 rows)Psql Tip #145
\l pattern will display for each database matching the
pattern, their names, owners access privileges and encoding and
collation details.
laetitia=# \l laetitia
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
----------+----------+----------+---------+-------+------------+-----------------+-------------------
laetitia | laetitia | UTF8 | C | UTF-8 | | libc |
(1 row)Psql Tip #146
The
+ modifier to the \l pattern metacommand will, on top of displaying
database names, owners, encoding, collation and access privileges
details for databases matching the pattern, display size, default
tablespace and comments.
laetitia=# \l+ laetitia
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges | Size | Tablespace | Description
----------+----------+----------+---------+-------+------------+-----------------+-------------------+-------+------------+-------------
laetitia | laetitia | UTF8 | C | UTF-8 | | libc | | 10 MB | pg_default |
(1 row)Psql Tip #147
The
\o meta command will reset query output to the standard output.
laetitia=# \o out.out
laetitia=# select * from test limit 5;
laetitia=# \o
laetitia=# select * from test limit 5;
id | value
----+-------
1 | bla
2 | bla
3 | bla
4 | bla
5 | bla
(5 rows)
Psql Tip #148
The
\o filename meta command will redirect all query results into the
file specified.
“Query results” includes all tables, command responses, and notices
obtained from the database server, as well as output of various
backslash commands that query the database (such as \d); but not error
messages.
laetitia=# \o out.out
laetitia=# select * from test limit 5;
laetitia=# \! cat out.out
id | value
----+-------
1 | bla
2 | bla
3 | bla
4 | bla
5 | bla
(5 rows)Psql Tip #149
The
\o |command meta command will redirect all query results into the
command specified.
In that case, the entire remainder of the line is taken to be the
command to execute, and neither variable interpolation nor backquote
expansion are performed in it. The rest of the line is simply passed
literally to the shell.
laetitia=# \o |grep -i 'bla'
laetitia=# select * from test limit 5;
laetitia=# 1 | bla
2 | bla
3 | bla
4 | bla
5 | blaPsql Tip #150
The
\p will print the current query buffer to the standard output.
If the current query buffer is empty, the most recently executed query
is printed instead.
laetitia=# \p
select * from test limit 5;Psql Tip #151
The
\password username will change the password of the specified user.
This command prompts for the new password, encrypts it, and sends it to
the server as an ALTER ROLE command. This makes sure that the new password
does not appear in cleartext in the command history, the server log, or
elsewhere. That's the secure way to change passwords in Postgres.
laetitia=# \password test
Enter new password for user "test":
Enter it again:
laetitia=#Psql Tip #152
Use
\password without any username to change securely the current user
password.
This command prompts for the new password, encrypts it, and sends it to
the server as an ALTER ROLE command. This makes sure that the new password
does not appear in cleartext in the command history, the server log, or
elsewhere. That's the secure way to change passwords in Postgres.
laetitia=# \password
Enter new password for user "laetitia":
Enter it again:Psql Tip #153
Use
\watch to re-run the current query (as a terminator,
like \g) repeatedly, until interrupted, until the query
fails, or until a limit is reached. By default the query is re-run every
two seconds. The i= option changes the interval, c=
limits the number of executions, and m= stops the loop as
soon as the query returns fewer than m rows. Each run is preceded by a
header with the time as of query start and the delay interval (this
header was added in Postgres 9.6).
laetitia=# select 1 as alive \watch i=1 c=2
Mon 10 Aug 2026 01:37:30 PM UTC (every 1s)
alive
-------
1
(1 row)
Mon 10 Aug 2026 01:37:31 PM UTC (every 1s)
alive
-------
1
(1 row)i= and c= options added in
Postgres 16 and the m= option in Postgres 17.
Psql Tip #154
Use
\pset format unaligned to switch query output to a
field-separated format instead of aligned columns. Every field is then
printed on one line separated by the current field separator (the pipe,
|, by default), with no borders or padding. This is handy
when piping results into other tools such as awk or
cut.
laetitia=# \pset format unaligned
Output format is unaligned.
laetitia=# SELECT id, name FROM widgets ORDER BY id;
id|name
1|Widget A
2|Widget B
3|Widget C
(3 rows)Psql Tip #155
The
\timing meta-command toggles the display of how long each SQL
command takes to execute. When enabled, psql shows the elapsed time
below the query result. The display is in milliseconds; intervals
longer than 1 second are also shown in minutes:seconds format.
laetitia=# \timing on
Timing is on.
laetitia=# SELECT count(*) FROM pg_class;
count
-------
399
(1 row)
Time: 1.845 ms
laetitia=# \timing off
Timing is off.Psql Tip #156
The conditional meta-commands
\if, \elif,
\else, and \endif let a psql script execute
parts of itself only when a condition holds. They are meant for script
files rather than interactive typing, so you usually place them in a
.sql file and run it with \i. The condition can
branch on a psql variable's value or even on the result of a query
captured with \gset.
laetitia=# \! cat check.sql
SELECT EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'test') AS has_table \gset
\if :has_table
\echo table test exists
\else
\echo table test does not exist
\endif
laetitia=# \i check.sql
table test existsPsql Tip #157
When you interpolate a psql variable into SQL, the plain
:var
form pastes the value verbatim. Depending on the context, that value is then
parsed as part of the SQL, which can produce the completely wrong result
(or an error). Use :'var' to have the value treated as a
quoted SQL string literal, and :"var" to have it treated as a
quoted SQL identifier. With neither, the value is interpreted literally:
a bare value ends up being read as an identifier, not as a literal.
laetitia=# \set who Bob
laetitia=# SELECT id FROM emp WHERE ename = :who;
ERROR: column "bob" does not exist
LINE 1: SELECT id FROM emp WHERE ename = Bob;
^
laetitia=# SELECT id FROM emp WHERE ename = :'who';
id
----
1
(1 row)
laetitia=# \set t Cases
laetitia=# SELECT id FROM :t;
ERROR: relation "cases" does not exist
LINE 1: SELECT id FROM Cases;
^
laetitia=# SELECT id FROM :"t";
id
----
5
(1 row)Psql Tip #158
\errverbose repeats the most recent server error message at maximum
verbosity. Where an error was first printed at the current verbosity
(default default, which may hide details), \errverbose
re-prints it in full — the same output you would get with
\set VERBOSITY verbose. This is handy when a terse error message
concealed hints, details, or position information.
laetitia=# CREATE TABLE t (id integer);
CREATE TABLE
laetitia=# \d t
Table "public.t"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
laetitia=# INSERT INTO t VALUES ('nope');
ERROR: invalid input syntax for type integer: "nope"
LINE 1: INSERT INTO t VALUES ('nope');
^
laetitia=# \errverbose
ERROR: 22P02: invalid input syntax for type integer: "nope"
LINE 1: INSERT INTO t VALUES ('nope');
^
LOCATION: pg_strtoint32_safe, numutils.c:615Psql Tip #159
Use
\setenv to set (or unset) an operating-system environment
variable from within a psql session, and \getenv to load the
current value of an environment variable into a psql variable. Both are
handy when you want psql to hand data to external processes or to react to
the surrounding shell environment without leaving your session.
laetitia=# \setenv PAGER less
laetitia=# \getenv shell_path SHELL
laetitia=# \echo :shell_path
/bin/bash
laetitia=# \setenv PAGER\setenv and since Postgres 15 for \getenv.
Psql Tip #160
The
\w meta-command writes the current query buffer (the SQL text
you have typed but not yet executed) to a file. This is handy for saving a query
you built up interactively, so you can reuse it later.
Be careful not to confuse it with \o or \g filename,
which redirect the query RESULT to a file: \w saves the query text
itself, not its result.
laetitia=# SELECT * FROM users WHERE active;
laetitia=# \w query.sql
laetitia=# \q
$ cat query.sql
SELECT * FROM users WHERE active;Psql Tip #161
Use
\! command to run any shell command from within psql without
leaving the prompt. This is handy for quick actions like listing files, reading
a log, or paging through output, then returning straight to SQL. Invoke
\! alone (with no command) to spawn an interactive subshell; type
exit (or press Ctrl-D) to drop back to psql.
laetitia=# \! ls -l
total 8
-rw-r--r-- 1 laetitia laetitia 97 Jul 10 09:00 notes.sql
drwxr-xr-x 2 laetitia laetitia 4096 Jul 10 09:00 data
laetitia=# \!
$ echo "inside subshell"
inside subshell
$ exit
laetitia=#Psql Tip #162
Use
\s to print your psql command history, and
\s filename to save that history into a file. Unlike
\p, which only shows the current query buffer, \s
shows the full history of the commands you have typed in this session,
which is handy for recalling or recording what you have run.
laetitia=# \s
\password
SELECT 1;
\s
laetitia=# \s history.sql
laetitia=# \! head -3 history.sql
\password
SELECT 1;Psql Tip #163
Use
\encoding without an argument to display the current client
encoding, or with an encoding name to change it, e.g. \encoding UTF8.
Setting the encoding issues SET CLIENT_ENCODING to the server, so the bytes sent
are interpreted as the server expects. After changing, psql prints nothing; run
\encoding again to confirm, and note Postgres aliases, e.g.
ISO_8859_1 is reported as LATIN1.
laetitia=# \encoding
UTF8
laetitia=# \encoding ISO_8859_1
laetitia=# \encoding
LATIN1Psql Tip #164
Use
\unset variable to remove a psql variable completely, so
that it no longer exists and is no longer considered "set" by psql.
Variables come from many places — \set, \gset,
\getenv, psql -v, the psqlrc startup file — and
\unset is the way to make them disappear from the session.
After unsetting, :var is no longer substituted (the literal
text stays), and the :{?var} set-test yields FALSE, which
scripts can rely on regardless of whether they use it in a
\if. Note that \set var (with no value)
clears the value but the variable still counts as "set", so
\unset var is what truly deletes it.
laetitia=# \set who Bob
laetitia=# \echo hello :who
hello Bob
laetitia=# \unset who
laetitia=# \echo hello :who
hello :who
laetitia=# \echo :{?who}
FALSEPsql Tip #165
The
\q (or its more explicit alias \quit) meta-command
exits the psql session and returns you to the shell prompt.
You can also exit by pressing Ctrl-D on an empty line, which
behaves the same way. While \q has existed since the earliest
psql versions, the \quit spelling was added later as the
self-documenting, full-word form of the same command.
laetitia=# \q
$ \q and since Postgres 8.2 for \quit.
Psql Tip #166
psql reads a start-up file stored in your home directory
(~/.psqlrc) every time a session begins, so any SQL command or
\meta-command it contains is run automatically. That gives you a
personalized default session, for example \pset pager off,
\timing on and \set VERBOSITY verbose. Use the
-X/--no-psqlrc switch to skip the file entirely, or set the
PSQLRC environment variable to point psql at a different file.
$ cat ~/.psqlrc
\pset pager off
\timing on
\set VERBOSITY verbose
$ psql
Pager usage is off.
Timing is on.
laetitia=# SELECT 1;
?column?
----------
1
(1 row)
Time: 0.351 ms
laetitia=# \q
$ psql -X
laetitia=# SELECT 1;
?column?
----------
1
(1 row)
laetitia=# \qPsql Tip #167
Use
\parse and \bind to run server-side prepared
statements from psql. \parse name takes the current query buffer
and creates a named prepared statement from it, while \bind
supplies the parameter values. Build a statement once, then execute it
repeatedly with different parameters by varying \bind each time,
followed by \g to run it.
laetitia=# CREATE TEMP TABLE products(id int, name text);
CREATE TABLE
laetitia=# INSERT INTO products VALUES (1,'a'),(2,'b');
INSERT 0 2
laetitia=# SELECT * FROM products WHERE id=$1 \parse myq
laetitia=# \bind 1
laetitia=# \g
id | name
----+------
1 | a
(1 row)
laetitia=# \bind 2
laetitia=# \g
id | name
----+------
2 | b
(1 row)
\bind and since Postgres 18 for \parse.
Psql Tip #168
Use
\dA to list the index (and table) access methods
available in the cluster, such as btree, hash,
gin, and gist. The variant \dA+
additionally shows the handler function and a description for each access
method.
laetitia=# \dA
List of access methods
Name | Type
--------+-------
brin | Index
btree | Index
gin | Index
gist | Index
hash | Index
heap | Table
spgist | Index
(7 rows)Psql Tip #169
Use
\dRp to list all logical replication publications and
\dRs to list all subscriptions. With no replication set up
these return empty lists, which is itself a truthful snapshot of the
configuration.
laetitia=# \dRp
List of publications
Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
------+-------+------------+---------+---------+---------+-----------+-------------------+----------
(0 rows)
laetitia=# \dRs
List of subscriptions
Name | Owner | Enabled | Publication
------+-------+---------+-------------
(0 rows)Psql Tip #170
Use
\lo_import filename to store a file from the client machine
as a large object; it prints the new object's oid. \lo_export oid filename
writes a stored large object back to a client-side file, and
\lo_list shows the oid of each large object.
Large objects are a legacy facility — the documentation describes them as
"partially obsolete" — and generally not the right choice for new work.
A bytea column is simpler and covers the same need up to 1 GB;
large objects keep a niche only for values up to 4 TB or when you need
efficient partial reads and updates.
laetitia=# \! echo "hello large object" > /tmp/lo_test.bin
laetitia=# \lo_import /tmp/lo_test.bin
lo_import 16483
laetitia=# \lo_list
Large objects
ID | Owner | Description
-------+----------+-------------
16483 | postgres |
(1 row)
laetitia=# \lo_export 16483 /tmp/lo_out.bin
lo_export
laetitia=# \! cat /tmp/lo_out.bin
hello large object
laetitia=# Psql Tip #171
The cousin
\ir, or its long form \include_relative, reads a
file just like \i/\include, but resolves relative paths against
the directory of the script file currently being processed. A plain \i
would instead resolve them against the working directory of psql. This makes
nested includes in scripts that live in their own directory work no matter from where
the outer script is invoked.
laetitia=# \! cat /tmp/demo/outer.sql
\echo running from CWD /root, script lives in /tmp/demo
\i sub/inner.sql
\ir sub/inner.sql
laetitia=# \cd /root
laetitia=# \i /tmp/demo/outer.sql
running from CWD /root, script lives in /tmp/demo
psql:/tmp/demo/outer.sql:2: error: sub/inner.sql: No such file or directory
?column?
---------------------
inner file executed
(1 row)\i looks for sub/inner.sql
under the current working directory (/root), where it does not exist; the
second one succeeds because \ir looks for it under /tmp/demo,
the directory of the script being processed.
This feature is available
since Postgres 9.2.
Psql Tip #172
With
\r (or \reset) you clear the whole current query
buffer: psql forgets whatever SQL you have typed so far, without sending
anything to the server — handy when you want to abort a half-typed query
(typo, change of mind) instead of executing or backspacing through it.
If you then run \g on the now-empty buffer,
psql re-executes the last previously-run query instead of failing.
laetitia=# select count(*) from pg_tables;
count
-------
70
(1 row)
laetitia=# select * from pg_tables
laetitia-# where hasindexes
laetitia-# \r
Query buffer reset (cleared).
laetitia=# \g
count
-------
70
(1 row)\r and since Postgres 9.0 for \reset.
Psql Tip #173
Use
\dO to list the collations defined in the current
database, one column per attribute (Schema, Name, Provider, Collate,
Ctype, Locale, ICU Rules, Deterministic?). By default the built-in
collations such as C, POSIX,
C.utf8 or en_US.utf8 live in the
pg_catalog schema and are therefore hidden; only your own
user-defined collations show up. Add a + to also get the
per-object description column.
laetitia=# CREATE COLLATION public.en_US_copy (LOCALE = 'en_US.utf8');
CREATE COLLATION
laetitia=# CREATE COLLATION public.my_german (PROVIDER = icu, LOCALE = 'de-DE');
CREATE COLLATION
laetitia=# \dO
List of collations
Schema | Name | Provider | Collate | Ctype | Locale | ICU Rules | Deterministic?
--------+------------+----------+------------+------------+--------+-----------+----------------
public | en_us_copy | libc | en_US.utf8 | en_US.utf8 | | | yes
public | my_german | icu | | | de-DE | | yes
(2 rows)Psql Tip #174
Use
\do to list the operators (like =, <,
+) known to Postgres, showing their operand types.
With no argument it lists every operator; with a pattern argument like
\do || it only lists operators whose name matches that pattern.
The columns show the schema, operator name, left and right argument types, the
result type, and a short description.
laetitia=# \do ||
List of operators
Schema | Name | Left arg type | Right arg type | Result type | Description
------------+------+--------------------+--------------------+--------------------+-------------------------------------
pg_catalog | || | anycompatible | anycompatiblearray | anycompatiblearray | prepend element onto front of array
pg_catalog | || | anycompatiblearray | anycompatible | anycompatiblearray | append element onto end of array
pg_catalog | || | anycompatiblearray | anycompatiblearray | anycompatiblearray | concatenate
pg_catalog | || | bytea | bytea | bytea | concatenate
pg_catalog | || | jsonb | jsonb | jsonb | concatenate
pg_catalog | || | text | text | text | concatenate
pg_catalog | || | tsquery | tsquery | tsquery | OR-concatenate
pg_catalog | || | tsvector | tsvector | tsvector | concatenate
...Psql Tip #175
Use
\dd to list objects that carry a comment/description, showing
the Object type and the Description text for each one.
With \dd objname you see the comment on a single object, a handy
way to uncover documented triggers, constraints, rules, operator classes, and
operator families. Note that only those object types are reported, not plain
tables, indexes, or views.
laetitia=# COMMENT ON TRIGGER trg_orders ON orders
IS 'Fires before any UPDATE on orders';
COMMENT
laetitia=# \dd trg_orders
Object descriptions
Schema | Name | Object | Description
--------+------------+---------+-----------------------------------
public | trg_orders | trigger | Fires before any UPDATE on orders
(1 row)Psql Tip #176
Use
\dX to list extended statistics objects created with
CREATE STATISTICS. It shows the schema, name, the columns or
expression used for the statistics, and which kinds of statistics are
defined. Use \dX+ for additional details such as expressions.
laetitia=# \dX
List of extended statistics
Schema | Name | Definition | Ndistinct | Dependencies | MCV
--------+------+-------------+-----------+--------------+-----
public | st | a, b FROM t | defined | defined |
(1 row)Psql Tip #177
Use
\dc to list character-set conversions in the current
database.
Row info includes the schema, the conversion name, the source and
destination encodings, and whether the conversion is the default.
Unless you used the CREATE CONVERSION statement to register
your own conversions, this list is empty — and that emptiness is a
truthful demonstration that nothing has been defined.
laetitia=# \dc
List of conversions
Schema | Name | Source | Destination | Default?
--------+------+--------+-------------+----------
(0 rows)Psql Tip #178
Use
\dew to list the foreign-data wrappers (FDW) installed on the
server, showing for each its name, owner, handler, and validator function.
The \dew name is a mnemonic: like the rest of the \d
family it "describes", and the documentation's mnemonic for the ew
letters is "external wrappers" — a wrapper to reach data stored outside the
database.
With \dew+ the access privileges, FDW options, and description
are shown as extra columns. Unless an FDW such as postgres_fdw
has been installed, the list is normally empty.
laetitia=# \dew
List of foreign-data wrappers
Name | Owner | Handler | Validator
------+-------+---------+-----------
(0 rows)Psql Tip #179
Use
\deu to list user mappings (for foreign data wrappers /
foreign servers). Because Postgres creates no user mappings by default,
this meta-command typically returns an empty result, which is a truthful
demonstration: the emptiness reflects reality, not a failure.
laetitia=# \deu
List of user mappings
Server | User name
--------+-----------
(0 rows)Psql Tip #180
The
\dy meta-command lists the event triggers defined in the
current database. Event triggers are created with CREATE EVENT TRIGGER
and fire on database-wide events such as ddl_command_start.
Since almost no database creates any by default, this list is usually empty.
laetitia=# \dy
List of event triggers
Name | Event | Owner | Enabled | Function | Tags
------+-------+-------+---------+----------+------
(0 rows)Psql Tip #181
Use
\dC to list all casts (type conversions) available in the
database, along with the function used and whether conversion is implicit.
Pass a pattern such as \dC text to narrow the list to casts
to or from a given type.
laetitia=# \dC text
List of casts
Source type | Target type | Function | Implicit?
-------------------+-------------------+--------------------+---------------
boolean | text | text | in assignment
"char" | text | text | yes
character | text | text | yes
character varying | text | (binary coercible) | yes
cidr | text | text | in assignment
inet | text | text | in assignment
name | text | text | yes
pg_dependencies | text | (binary coercible) | yes
pg_mcv_list | text | (binary coercible) | yes
pg_ndistinct | text | (binary coercible) | yes
pg_node_tree | text | (binary coercible) | yes
text | "char" | char | in assignment
text | character | (binary coercible) | yes
text | character varying | (binary coercible) | yes
text | name | name | yes
text | regclass | regclass | yes
text | xml | xml | no
xml | text | (binary coercible) | in assignment
(18 rows)Psql Tip #182
The
\dFp meta-command lists the full-text-search parsers known to
the server. A parser is the component that tokenizes text into lexemes for
full-text search.
laetitia=# \dFp
List of text search parsers
Schema | Name | Description
------------+---------+---------------------
pg_catalog | default | default word parser
(1 row)Psql Tip #183
Use
\dFt to list the full-text-search templates defined in the
database.
By default Postgres ships only the simple template; seeing an
empty list for a fresh setup is actually a truthful demonstration that no
templates exist yet.
laetitia=# \dFt
List of text search templates
Schema | Name | Description
------------+-----------+-----------------------------------------------------------
pg_catalog | ispell | ispell dictionary
pg_catalog | simple | simple dictionary: just lower case and check for stopword
pg_catalog | snowball | snowball stemmer
pg_catalog | synonym | synonym dictionary: replace word by its synonym
pg_catalog | thesaurus | thesaurus dictionary: phrase by phrase substitution
(5 rows)Psql Tip #184
Use
\prompt varname [text] to ask the user interactively for a
value and store it in a psql variable. The optional text is
shown as the prompt. Once set you can use the variable, for example with
quoted substitution :"tbl", inside a query or a script.
In an actual terminal psql reads the answer line by line and echoes it,
giving a convenient way to build interactive reports and WHERE clauses.
laetitia=# \prompt tbl Table name?
Table name? pg_catalog
laetitia=# SELECT count(*) FROM :"tbl";Psql Tip #185
Use
\sf function to print the source text of a function or
procedure without opening an editor. Add + as in \sf+ to
also show the object's line number and size, or supply the argument types
to disambiguate overloads, e.g. \sf add2(int,int). Unlike
\ef (which opens the definition in an editor), \sf simply
prints the definition to the terminal.
laetitia=# CREATE OR REPLACE FUNCTION add2(int,int) RETURNS int AS $$ SELECT $1+$2 $$ LANGUAGE sql;
CREATE FUNCTION
laetitia=# \sf add2
CREATE OR REPLACE FUNCTION public.add2(integer, integer)
RETURNS integer
LANGUAGE sql
AS $function$ SELECT $1+$2 $function$
laetitia=#Psql Tip #186
Use
\sv view to print the SQL source code used to create a
view, directly to the terminal. Unlike \ev, which opens the
view definition in an editor for editing, \sv simply shows
the CREATE VIEW statement. Adding a plus sign with
\sv+ also displays the view's storage parameters and other
extra information.
laetitia=# \sv myview
CREATE OR REPLACE VIEW public.myview AS
SELECT 1 AS x
laetitia=#Psql Tip #187
The
\t meta-command toggles "tuples only" mode, hiding the
column header row and the (N rows) footer so that only the
data is printed.
Running it once (or \t on) enables the mode, and running it
again or \t off restores the normal output.
laetitia=# VALUES (1),(2);
column1
---------
1
2
(2 rows)
laetitia=# \t
Tuples only is on.
laetitia=# VALUES (1),(2);
1
2
laetitia=# \t offPsql Tip #188
The
\a meta-command toggles between aligned and unaligned query
output mode.
It is the quick interactive shorthand for \pset format aligned and
\pset format unaligned, printing a status line to show the new mode.
Ideal for flipping an output to tab-separated values while you're inside
psql, without restarting the session.
laetitia=# SELECT * FROM (VALUES (1),(2)) t(x);
x
---
1
2
(2 rows)
laetitia=# \a
Output format is unaligned.
laetitia=# SELECT * FROM (VALUES (1),(2)) t(x);
x
1
2
(2 rows)
laetitia=# \a
Output format is aligned.Psql Tip #189
Use the
\x meta-command to toggle the expanded output
mode. In expanded mode, each column of a row is displayed on its own line,
prefixed with the column name, instead of as a row in a table; this is
especially handy for wide results or rows with many columns. Calling
\x again turns expanded mode off. You can also use
\x auto to let psql expand only the results that would
otherwise overflow the screen width.
laetitia=# SELECT * FROM (VALUES (1,'a')) t(id,n);
id | n
----+---
1 | a
(1 row)
laetitia=# \x
Expanded display is on.
laetitia=# SELECT * FROM (VALUES (1,'a')) t(id,n);
-[ RECORD 1 ]
id | 1
n | a
laetitia=# \x
Expanded display is off.Psql Tip #190
Use
\dF to list all full-text-search (tsvector / tsquery)
configurations available on the server, such as the built-in
english, french or simple ones.
With a name or pattern (e.g. \dF+ english) and the plus
sign, psql also shows the parser used and the list of dictionaries (filters) applied to each token type.
laetitia=# \dF
List of text search configurations
Schema | Name | Description
------------+------------+---------------------------------------
pg_catalog | arabic | configuration for arabic language
pg_catalog | catalan | configuration for catalan language
pg_catalog | english | configuration for english language
pg_catalog | french | configuration for french language
pg_catalog | german | configuration for german language
pg_catalog | simple | simple configuration
pg_catalog | spanish | configuration for spanish language
...
(30 rows)
laetitia=# \dF+ english
Text search configuration "pg_catalog.english"
Parser: "pg_catalog.default"
Token | Dictionaries
-----------------+--------------
asciihword | english_stem
asciiword | english_stem
email | simple
file | simple
uint | simple
url | simple
version | simple
word | english_stem
...(19 rows)Psql Tip #191
Use
\copy's PROGRAM form to stream data through an external shell command,
client-side. With \copy (query) TO PROGRAM 'cmd' the result
rows are piped into the command's standard input, and psql echoes its
stdout. With \copy table FROM PROGRAM 'cmd' the command's
output is piped into the table as rows.
laetitia=# \copy (SELECT * FROM t) TO PROGRAM 'wc -l'
COPY 3
3
laetitia=# \copy t FROM PROGRAM 'echo 5'
COPY 1Psql Tip #192
Set the
psql variable ON_ERROR_STOP to on
so that psql stops as soon as a SQL error occurs instead of
continuing with the next statement.
This is the first line of a well-behaved script: put
\set ON_ERROR_STOP on at the top so that a failing step halts
processing and psql returns a non-zero exit code, failing the
whole pipeline. Without it, psql just reports the error and
keeps going with the following statements, and exits successfully. It can
also be turned on from the command line with
-v ON_ERROR_STOP=1.
laetitia:~$ cat check.sql
\set ON_ERROR_STOP on
SELECT 1;
SELECT no_such_column FROM pg_class;
SELECT 2;
laetitia:~$ psql -f check.sql
?column?
----------
1
(1 row)
psql:check.sql:2: ERROR: column "no_such_column" does not exist
LINE 1: SELECT no_such_column FROM pg_class;
^
laetitia:~$ echo $?
3Psql Tip #193
The
VERBOSITY variable controls how much detail psql prints
for error messages. Its values are default, terse,
verbose, and sqlstate: verbose adds a leading SQLSTATE
code before the message plus extra DETAIL, SCHEMA,
TABLE, CONSTRAINT NAME, and LOCATION
lines, which is invaluable for diagnosing what failed.
laetitia=# \set VERBOSITY verbose
laetitia=# INSERT INTO t VALUES (1);
ERROR: 23505: duplicate key value violates unique constraint "t_pkey"
DETAIL: Key (id)=(1) already exists.
SCHEMA NAME: public
TABLE NAME: t
CONSTRAINT NAME: t_pkey
LOCATION: _bt_check_unique, nbtinsert.c:666Psql Tip #194
Suppress psql's informational chatter so that only the actual query output
is printed. Setting the psql variable
QUIET to on
does this within a session, and the -q/--quiet
command-line flag (tip 030) does it for the whole invocation. This is what
makes scripts presentable: put \set QUIET on at the top of a
script so that command tags such as CREATE TABLE or
INSERT 0 2 are silenced — without it they would clutter the
output — while SELECT results keep printing as usual.
laetitia:~$ cat script.sql
\set QUIET on
CREATE TEMPORARY TABLE t(id int);
INSERT INTO t VALUES (1),(2);
SELECT count(*) FROM t;
laetitia:~$ psql -f script.sql
count
-------
2
(1 row)Psql Tip #195
Use
\pset pager to declare whether psql sends its result to a
pager (like less) instead of dumping everything to the screen.
off disables the pager completely, on uses it only
when the output is bigger than the terminal, and always forces
the pager even for short results. This complements \pset format unaligned
for controlling how query output is presented.
laetitia=# \pset pager off
Pager usage is off.
laetitia=# \pset pager on
Pager is used for long output.
laetitia=# \pset pager always
Pager is always used.Psql Tip #196
Use
\pset null displaystring to set the string printed in place of
NULL values in query results. By default NULL is shown as an empty
(unprintable) field; giving it a visible marker like 'NULL' or
'<NULL>' makes NULLs obvious at a glance.
laetitia=# SELECT NULL AS x;
x
---
(1 row)
laetitia=# \pset null '🦄'
Null display is "🦄".
laetitia=# SELECT NULL AS x;
x
----
🦄
(1 row)Psql Tip #197
Use
\pset border to control which lines are drawn around the
query result table. border 0 draws no vertical bars, only the
header line and a dash separator. border 1 only keeps that
separator. border 2 draws the full grid with |,
+ and -, which is the default in psql.
laetitia=# \pset border 0
x
-
1
2
(2 rows)
laetitia=# \pset border 1
x
---
1
2
(2 rows)
laetitia=# \pset border 2
+---+
| x |
+---+
| 1 |
| 2 |
+---+
(2 rows)Psql Tip #198
The
\pset format wrapped option is the same as aligned, but
column content that doesn't fit the chosen width is wrapped onto multiple
lines instead of being truncated or stretched. Combine it with \pset columns N
to force a column width and see the wrapping in action. This complements
\pset format unaligned, which emits tab-separated lines with no decoration.
laetitia=# \pset format wrapped
Output format is wrapped.
laetitia=# \pset columns 20
Target width is 20.
laetitia=# SELECT 'some long text that will wrap onto multiple lines' AS long_text;
long_text
--------------------
some long text tha.
.t will wrap onto m.
.ultiple lines
(1 row)Psql Tip #199
To use a custom column separator in unaligned output,
\pset fieldsep
sets the character between columns (the default is a pipe |).
Combine it with \pset format unaligned, and optionally
\pset recordsep to change the separator between rows
(the default is a newline).
laetitia=# \pset format unaligned
Output format is unaligned.
laetitia=# \pset fieldsep ';'
Field separator is ";".
laetitia=# select 1 as a, 2 as b;
a;b
1;2
(1 row)
laetitia=# \pset recordsep '#'
Record separator is "#".
laetitia=# select 3 as c;
c#3#(1 row)Psql Tip #200
By default the psql variable
AUTOCOMMIT is on, so every
statement is committed immediately. Setting \set AUTOCOMMIT off switches
psql into manual transaction mode: psql issues an implicit BEGIN on
your behalf before the first statement, and the prompt shows a leading
* while the transaction stays open. Nothing is committed until you
issue COMMIT; any uncommitted change can be discarded with
ROLLBACK. Here even the CREATE TABLE is undone, so the
final query fails because the relation no longer exists.
laetitia=# \set AUTOCOMMIT off
laetitia=# CREATE TABLE t(id int);
CREATE TABLE
laetitia=*# INSERT INTO t VALUES (1);
INSERT 0 1
laetitia=*# ROLLBACK;
ROLLBACK
laetitia=# SELECT count(*) FROM t;
ERROR: relation "t" does not exist
LINE 1: SELECT count(*) FROM t
^Psql Tip #201
The
\dconfig meta-command lists server configuration
parameters, like SHOW ALL but in psql's familiar
tabular format. Without a pattern, only parameters whose value
differs from their default are shown; use \dconfig *
for the complete list. Adding + also displays the
data type, the context in which the parameter can be changed, and
its access privileges.
laetitia=# \dconfig work_mem
List of configuration parameters
Parameter | Value
-----------+-------
work_mem | 4MB
(1 row)
laetitia=# \dconfig+ work_mem
List of configuration parameters
Parameter | Value | Type | Context | Access privileges
-----------+-------+---------+---------+-------------------
work_mem | 4MB | integer | user |
(1 row)Psql Tip #202
Which text-search dictionaries does your database know? After
\dFp (parsers) and \dFt (templates), the
\dFd meta-command completes the full-text-search family:
it lists the dictionaries used to normalize lexemes. The
simple dictionary only lower-cases words and checks
stopwords, while the snowball stemmers reduce inflected words to
their stems, one dictionary per supported language.
The plain \dFd output already includes the system
dictionaries shipped in pg_catalog, and a dictionary you
create yourself appears in the very same list. The S
modifier forces system objects into the output, and a pattern
restricts it, e.g. \dFd en* narrows the result to
english_stem.
laetitia=# \pset pager off
Pager usage is off.
laetitia=# \dFd
List of text search dictionaries
Schema | Name | Description
------------+-----------------+-----------------------------------------------------------
pg_catalog | arabic_stem | snowball stemmer for arabic language
pg_catalog | armenian_stem | snowball stemmer for armenian language
pg_catalog | basque_stem | snowball stemmer for basque language
pg_catalog | catalan_stem | snowball stemmer for catalan language
pg_catalog | danish_stem | snowball stemmer for danish language
pg_catalog | dutch_stem | snowball stemmer for dutch language
pg_catalog | english_stem | snowball stemmer for english language
pg_catalog | estonian_stem | snowball stemmer for estonian language
pg_catalog | finnish_stem | snowball stemmer for finnish language
pg_catalog | french_stem | snowball stemmer for french language
pg_catalog | german_stem | snowball stemmer for german language
pg_catalog | greek_stem | snowball stemmer for greek language
pg_catalog | hindi_stem | snowball stemmer for hindi language
pg_catalog | hungarian_stem | snowball stemmer for hungarian language
pg_catalog | indonesian_stem | snowball stemmer for indonesian language
pg_catalog | irish_stem | snowball stemmer for irish language
pg_catalog | italian_stem | snowball stemmer for italian language
pg_catalog | lithuanian_stem | snowball stemmer for lithuanian language
pg_catalog | nepali_stem | snowball stemmer for nepali language
pg_catalog | norwegian_stem | snowball stemmer for norwegian language
pg_catalog | portuguese_stem | snowball stemmer for portuguese language
pg_catalog | romanian_stem | snowball stemmer for romanian language
pg_catalog | russian_stem | snowball stemmer for russian language
pg_catalog | serbian_stem | snowball stemmer for serbian language
pg_catalog | simple | simple dictionary: just lower case and check for stopword
pg_catalog | spanish_stem | snowball stemmer for spanish language
pg_catalog | swedish_stem | snowball stemmer for swedish language
pg_catalog | tamil_stem | snowball stemmer for tamil language
pg_catalog | turkish_stem | snowball stemmer for turkish language
pg_catalog | yiddish_stem | snowball stemmer for yiddish language
(30 rows)
Psql Tip #203
The
\drg meta-command lists role membership grants: for each
grant it shows the member role, the group it belongs to, the assigned
options (ADMIN, INHERIT and/or SET)
and the grantor. By default only grants to user-created roles are shown;
the S modifier (\drgS) adds system roles, and a
pattern filters by role name. Here a role is granted
membership in a group with the ADMIN option, making the
grant appear. Roles are cluster-wide and not session-scoped, so both are
dropped right away.
laetitia=# CREATE ROLE tip203_dev;
CREATE ROLE
laetitia=# CREATE ROLE tip203_group;
CREATE ROLE
laetitia=# GRANT tip203_group TO tip203_dev WITH ADMIN OPTION;
GRANT ROLE
laetitia=# \drg
List of role grants
Role name | Member of | Options | Grantor
------------+--------------+---------------------+----------
tip203_dev | tip203_group | ADMIN, INHERIT, SET | postgres
(1 row)
laetitia=# DROP ROLE tip203_dev;
DROP ROLE
laetitia=# DROP ROLE tip203_group;
DROP ROLEPsql Tip #204
Use
\lo_unlink oid to delete a large object, given the oid
printed by \lo_import or listed by \lo_list
(see tip 170 for those commands). The example stores the new oid in
the psql variable oid with SELECT lo_import(...) AS
oid \gset and unlinks by variable, so the number is never
hardcoded. On success psql echoes the resolved command back, with no
confirmation; unlinking an oid that does not exist raises an error.
Large objects are a legacy facility, so prefer a bytea column for new
work.
laetitia=# \! echo "hello" > /tmp/tip204.bin
laetitia=# SELECT lo_import('/tmp/tip204.bin') AS oid \gset
laetitia=# \lo_list
Large objects
ID | Owner | Description
-------+----------+-------------
24768 | postgres |
(1 row)
laetitia=# \lo_unlink :oid
lo_unlink 24768
laetitia=# \lo_list
Large objects
ID | Owner | Description
----+-------+-------------
(0 rows)
laetitia=# \lo_unlink 1
ERROR: large object 1 does not exist
laetitia=# Psql Tip #205
\warn is the script companion of \echo:
it does exactly the same, but writes to standard error instead
of standard output. Keeping the query results on the standard
output lets you pipe or redirect them, while the diagnostics
(progress messages, warnings) carried by \warn go
to standard error.
laetitia:~$ cat warn_demo.sql
select 'result' as output;
\warn loading data...
laetitia:~$ psql -f warn_demo.sql >script.out 2>script.err
laetitia:~$ cat script.out
output
--------
result
(1 row)
laetitia:~$ cat script.err
loading data...Psql Tip #206
Pipeline mode lets psql send several statements to the server without
waiting for the result of each one, which reduces round trips on
high-latency connections. A pipeline starts with
\startpipeline
and ends with \endpipeline; \syncpipeline
sends a sync message without ending the pipeline, while
\sendpipeline appends the current query buffer to it.
\getresults reads the pending results (\getresults n
reads at most n), \flushrequest appends a flush command so
results can be read mid-pipeline, and \flush manually
pushes unsent data. In the example the two SELECTs produce no output
until \endpipeline synchronizes the pipeline, so
\getresults finds nothing pending yet. Statements in a
pipeline all use the extended query protocol, so they complement the
\bind/\parse tricks of tip 167. The prompt
variable %P reports the current pipeline status (off, on
or abort).
laetitia=# \startpipeline
laetitia=# SELECT 1 AS a;
laetitia=*# SELECT 2 AS b;
laetitia=*# \getresults
No pending results to get
laetitia=*# \endpipeline
a
---
1
(1 row)
b
---
2
(1 row)
Psql Tip #207
Use
\parse to store a query as a named prepared statement,
then \bind_named to supply its parameter values, naming the
statement explicitly as the first argument. While \bind
targets the unnamed statement, \bind_named can pick any
previously prepared statement, so several queries can be prepared in the
same session and executed on demand, each with its own values. An empty
string as first argument targets the unnamed statement instead.
laetitia=# CREATE TEMPORARY TABLE t(id int, name text);
CREATE TABLE
laetitia=# INSERT INTO t VALUES ($1,$2) \parse ins
laetitia=# \bind_named ins 1 'one'
laetitia=# \g
INSERT 0 1
laetitia=# \bind_named ins 2 'two'
laetitia=# \g
INSERT 0 1
laetitia=# SELECT * FROM t ORDER BY id \parse sel
laetitia=# \bind_named sel
laetitia=# \g
id | name
----+------
1 | one
2 | two
(2 rows)
Psql Tip #208
\close_prepared name closes the server-side prepared statement
created with \parse (an empty name targets the unnamed
statement). Closing a name that does not exist is a silent no-op. Once
closed, the statement disappears from pg_prepared_statements.
Note that a later \bind/\g pair simply prepares
the query again, so the close releases the stored plan but does not make
the query unusable.
laetitia=# SELECT $1 AS v \parse stmt1
laetitia=# \bind 1
laetitia=# \g
v
---
1
(1 row)
laetitia=# \close_prepared stmt1
laetitia=# SELECT name FROM pg_prepared_statements;
name
------
(0 rows)
laetitia=# \close_prepared stmt1
Psql Tip #209
Use
\dAc to list the operator classes defined in the
database. An operator class provides the operators and support functions
an access method needs to index a data type, like btree
using the int4_ops class for integer. The two
optional patterns filter by access method and input type, and +
adds the operator family and owner of each class while the x
variant switches to expanded output. The sibling commands \dAf,
\dAo, and \dAp list operator families,
operators, and support functions.
laetitia=# \dAc btree integer
List of operator classes
AM | Input type | Storage type | Operator class | Default?
-------+------------+--------------+----------------+----------
btree | integer | | int4_ops | yes
(1 row)
laetitia=# \dAc+ btree integer
List of operator classes
AM | Input type | Storage type | Operator class | Default? | Operator family | Owner
-------+------------+--------------+----------------+----------+-----------------+----------
btree | integer | | int4_ops | yes | integer_ops | postgres
(1 row)Psql Tip #210
\? is psql's built-in help command: it lists every backslash
command, grouped into thematic sections. The list is long, so only its
beginning is shown here, the middle being elided with [...].
\?
also accepts a category as its argument — commands,
options, variables or topics — to
print only one part of the help; and as the Help section above shows,
\? options documents the command-line options while
\? variables documents the special variables.
laetitia=# \?
General
\copyright show PostgreSQL usage and distribution terms
\crosstabview [COLUMNS] execute query and display result in crosstab
\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
\gdesc describe result of query, without executing it
\gexec execute query, then execute each value in its result
\gset [PREFIX] execute query and store result in psql variables
\gx [(OPTIONS)] [FILE] as \g, but forces expanded output mode
\q quit psql
\restrict RESTRICT_KEY
enter restricted mode with provided key
\unrestrict RESTRICT_KEY
exit restricted mode if key matches
\watch [[i=]SEC] [c=N] [m=MIN]
execute query every SEC seconds, up to N times,
stop if less than MIN rows are returned
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
[...]
Extended Query Protocol
\bind [PARAM]... set query parameters
\bind_named STMT_NAME [PARAM]...
set query parameters for an existing prepared statement
\close_prepared STMT_NAME
close an existing prepared statement
\endpipeline exit pipeline mode
\flush flush output data to the server
\flushrequest send request to the server to flush its output buffer
\getresults [NUM_RES] read NUM_RES pending results, or all if no argument
\parse STMT_NAME create a prepared statement
\sendpipeline send an extended query to an ongoing pipeline
\startpipeline enter pipeline mode
\syncpipeline add a synchronisation point to an ongoing pipelinePsql Tip #211
The
\T meta-command sets options to be placed within
the HTML <table> tag, and is equivalent to
\pset tableattr. Once the html output
format is active (see the \H tip), a query produces
a <table border="1"> opening tag. Setting
\T 'border="2" bgcolor="lightgray"' adds these
attributes to that tag. Quote the whole argument when it contains
spaces; \T with no argument resets the attributes.
laetitia=# \T 'border="2" bgcolor="lightgray"'
Table attributes are "border="2" bgcolor="lightgray"".
laetitia=# SELECT 2 AS b;
<table border="1" border="2" bgcolor="lightgray">
<tr>
<th align="center">b</th>
</tr>
<tr valign="top">
<td align="right">2</td>
</tr>
</table>
<p>(1 row)<br />
</p>
laetitia=# \T
Table attributes unset.
Psql Tip #212
\z and \dp both list tables, views
and sequences with their associated access privileges, and both
accept an optional pattern to restrict the listing
to matching names. Why the odd letter? \z is the
historical name, present since the earliest psql releases;
\dp ("display privileges") was added later as a
more descriptive alias, and the documentation eventually made
it the primary spelling, keeping \z as an alias.
The S modifier also lists system objects and
x switches the output to expanded mode. You may
encounter either spelling, the rendered result is identical.
Here SELECT has been granted to PUBLIC
on a temporary table: the owner's privileges and the grant both
appear in the listing.
laetitia=# CREATE TEMP TABLE mytmp (id int);
CREATE TABLE
laetitia=# GRANT SELECT ON mytmp TO PUBLIC;
GRANT
laetitia=# \z mytmp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
------------+-------+-------+----------------------------+-------------------+----------
pg_temp_60 | mytmp | table | postgres=arwdDxtm/postgres+| |
| | | =r/postgres | |
(1 row)Psql Tip #213
For unaligned output,
\f sets the field separator, the
character between columns (the default is a pipe |). It is the
short form of \pset fieldsep; with no argument, it prints the
current separator.
laetitia=# \pset format unaligned
Output format is unaligned.
laetitia=# \f ';'
Field separator is ";".
laetitia=# select 1 as a, 2 as b;
a;b
1;2
(1 row)Psql Tip #214
Use
\dAf to list the operator families defined in the
database. An operator family groups several operator classes
so that cross-type operators work, like the btree
integer_ops family covering smallint,
integer, and bigint. The two optional
patterns filter by access method and applicable type, and the
+ modifier adds the owner of each family. The
sibling commands \dAc, \dAo, and
\dAp list operator classes, operators, and support
functions.
laetitia=# \dAf btree integer
List of operator families
AM | Operator family | Applicable types
-------+-----------------+---------------------------
btree | integer_ops | smallint, integer, bigint
(1 row)
laetitia=# \dAf+ btree integer
List of operator families
AM | Operator family | Applicable types | Owner
-------+-----------------+---------------------------+----------
btree | integer_ops | smallint, integer, bigint | postgres
(1 row)Psql Tip #215
The
\dAo meta-command lists the operators that belong to
operator families. Its first argument filters on the access method,
the second one on the operator family; here for the bool_ops
family of btree. Each operator is shown with its strategy number and
purpose: for btree, strategies 1 to 5 stand for <, <=, =, >=
and >, all used for search. Sibling commands \dAc and
\dAf list the operator classes and the families themselves.
laetitia=# \dAo btree bool_ops
List of operators of operator families
AM | Operator family | Operator | Strategy | Purpose
-------+-----------------+---------------------+----------+---------
btree | bool_ops | <(boolean,boolean) | 1 | search
btree | bool_ops | <=(boolean,boolean) | 2 | search
btree | bool_ops | =(boolean,boolean) | 3 | search
btree | bool_ops | >=(boolean,boolean) | 4 | search
btree | bool_ops | >(boolean,boolean) | 5 | search
(5 rows)Psql Tip #216
Use
\dAp to list the support functions associated with
operator families: the functions an access method calls on indexed
values, for example the comparison functions used by btree.
The two optional patterns filter by access method and operator family,
as for the sibling commands \dAc, \dAf and
\dAo, which respectively list operator classes, operator
families and family operators. Here bool_ops, the family
for boolean values, has three support functions:
btboolcmp (number 1) compares two booleans,
btequalimage (number 4) tells whether two values can match,
and btboolskipsupport (number 6) helps the skip scan
optimization. Appending + shows the signature of each
function; the x variant switches to expanded output.
laetitia=# \dAp btree bool_ops
List of support functions of operator families
AM | Operator family | Registered left type | Registered right type | Number | Function
-------+-----------------+----------------------+-----------------------+--------+-------------------
btree | bool_ops | boolean | boolean | 1 | btboolcmp
btree | bool_ops | boolean | boolean | 4 | btequalimage
btree | bool_ops | boolean | boolean | 6 | btboolskipsupport
(3 rows)
laetitia=# \dAp+ btree bool_ops
List of support functions of operator families
AM | Operator family | Registered left type | Registered right type | Number | Function
-------+-----------------+----------------------+-----------------------+--------+-----------------------------
btree | bool_ops | boolean | boolean | 1 | btboolcmp(boolean,boolean)
btree | bool_ops | boolean | boolean | 4 | btequalimage(oid)
btree | bool_ops | boolean | boolean | 6 | btboolskipsupport(internal)
(3 rows)Psql Tip #217
\restrict restrict_key enters "restricted" mode: the
only meta-command that still works is \unrestrict with
the very same key, while plain SQL keeps running normally, as seen
with SELECT 1. Any other meta-command is rejected with
an error message, and an \unrestrict given the wrong
key fails, so the session stays locked. Restricted mode is used by
pg_dump, pg_dumpall and pg_restore to protect plain-text dump files
against malicious servers injecting psql meta-commands; per the
documentation, it "may be useful elsewhere" too.
laetitia=# \restrict secret
laetitia=# \echo blocked test
backslash commands are restricted; only \unrestrict is allowed
laetitia=# SELECT 1;
?column?
----------
1
(1 row)
laetitia=# \unrestrict wrong
\unrestrict: wrong key
laetitia=# \unrestrict secret
laetitia=# \echo freed again
freed again
laetitia=# \qPsql Tip #218
\dti will display the list of user created and visible
tables and indexes in a single listing. \dti pattern will
list the tables and indexes matching the pattern.
The letters E, i, m,
s, t and v stand for foreign
table, index, materialized view, sequence, table and view and can be
combined in any order, \dts listing the tables and the
sequences for example. The single letter variants are individually
described in tips 079 (\di), 080 (\dm),
081 (\ds), 082 (\dt) and 083
(\dv).
The + modifier will also display the persistence,
physical size and description of each object, and the S
modifier will also display the system tables and indexes.
laetitia=# CREATE TEMP TABLE demo_tmp(id int);
CREATE TABLE
laetitia=# CREATE INDEX demo_tmp_idx ON demo_tmp(id);
CREATE INDEX
laetitia=# \dti
List of relations
Schema | Name | Type | Owner | Table
------------+--------------+-------+----------+----------
pg_temp_92 | demo_tmp | table | postgres |
pg_temp_92 | demo_tmp_idx | index | postgres | demo_tmp
public | t | table | postgres |
public | t193 | table | postgres |
public | t193_pkey | index | postgres | t193
public | t_pkey | index | postgres | t
(6 rows)
laetitia=# \dti+
List of relations
Schema | Name | Type | Owner | Table | Persistence | Access method | Size | Description
------------+--------------+-------+----------+----------+-------------+---------------+------------+-------------
pg_temp_92 | demo_tmp | table | postgres | | temporary | heap | 0 bytes |
pg_temp_92 | demo_tmp_idx | index | postgres | demo_tmp | temporary | btree | 8192 bytes |
public | t | table | postgres | | permanent | heap | 8192 bytes |
public | t193 | table | postgres | | permanent | heap | 8192 bytes |
public | t193_pkey | index | postgres | t193 | permanent | btree | 16 kB |
public | t_pkey | index | postgres | t | permanent | btree | 16 kB |
(6 rows)Psql Tip #219
Used without a pattern argument,
The letters are combinable:
\d is equivalent to
\dtvmsE and shows a list of all visible tables, views,
materialized views, sequences and foreign tables. According to the psql
documentation, this is purely a convenience measure.The letters are combinable:
\dt lists the tables,
\dv the views, \dm the materialized views,
\ds the sequences and \dE the foreign tables.
\dtvmsE is the complete set of letters, hence its
equivalence with the bare \d.
laetitia=# \dtvmsE
List of relations
Schema | Name | Type | Owner
--------+--------+-------------------+----------
public | ft219 | foreign table | postgres
public | mv219 | materialized view | postgres
public | myview | view | postgres
public | s219 | sequence | postgres
public | t | table | postgres
public | t193 | table | postgres
(6 rows)Psql Tip #220
The
-A or --no-align flag switches psql
to unaligned output mode: instead of padding columns so that they
line up, every field is printed as-is and columns are separated
by a pipe. It is the command-line twin of
\pset format unaligned.
laetitia:~$ psql -c 'select * from animals;'
id | name
----+------
1 | cat
2 | dog
(2 rows)
laetitia:~$ psql -A -c 'select * from animals;'
id|name
1|cat
2|dog
(2 rows)
Psql Tip #221
When a SQL command fails, the
-b or
--echo-errors flag makes psql echo the exact failing
statement to standard error, in addition to the error message.
This is handy in scripts to see what actually failed.
laetitia:~$ cat demo.sql
select 1;
select * from nonexistent;
laetitia:~$ psql -f demo.sql
?column?
----------
1
(1 row)
psql:demo.sql:2: ERROR: relation "nonexistent" does not exist
LINE 1: select * from nonexistent;
^
laetitia:~$ psql -b -f demo.sql
?column?
----------
1
(1 row)
psql:demo.sql:2: ERROR: relation "nonexistent" does not exist
LINE 1: select * from nonexistent;
^
psql:demo.sql:2: STATEMENT: select * from nonexistent;
Psql Tip #222
In unaligned mode, columns are separated by a pipe by default.
The
-F or --field-separator flag changes
that separator, e.g. to a semicolon to build a CSV-like output.
It is the command-line twin of \pset fieldsep.
laetitia:~$ psql -A -F ';' -c 'select * from animals;'
id;name
1;cat
2;dog
(2 rows)
Psql Tip #223
The
-P or --pset flag sets a
\pset option right from the command line, and can be
repeated for several options. Here border=2 draws a
full frame, and null=NULL changes what is printed
for NULL values.
laetitia:~$ psql -P border=2 -c 'select * from animals;'
+----+------+
| id | name |
+----+------+
| 1 | cat |
| 2 | dog |
+----+------+
(2 rows)
laetitia:~$ psql -P null=NULL -P format=unaligned -c 'select null;'
?column?
NULL
(1 row)
Psql Tip #224
In unaligned mode, records are separated by a newline by default.
The
-R or --record-separator flag
replaces it with any other string, like a caret here. It is the
command-line twin of \pset recordsep.
laetitia:~$ psql -A -R '^' -c 'select * from animals;'
id|name^1|cat^2|dog^(2 rows)
Psql Tip #225
The
-S or --single-line flag turns on
single-line mode, where a newline ends a SQL command just like a
semicolon would. This is handy when feeding psql with a stream
that has no semicolons.
laetitia:~$ printf 'select 1 as one\nselect 2 as two\n' | psql -S
one
-----
1
(1 row)
two
-----
2
(1 row)
Psql Tip #226
The
-z or --field-separator-zero flag
uses the NUL byte as the field separator in unaligned mode,
which makes parsing unambiguous when the data itself contains
pipes or newlines. The NUL bytes are visible with od -c.
laetitia:~$ psql -A -z -c 'select * from animals;' | od -c
0000000 i d \0 n a m e \n 1 \0 c a t \n 2 \0
0000020 d o g \n ( 2 r o w s ) \n
0000035
Psql Tip #227
The
-0 or --record-separator-zero flag
uses the NUL byte as the record separator in unaligned mode,
recording exactly where one record ends and the next begins, as
shown by the NUL bytes in od -c output.
laetitia:~$ psql -A -t -0 -c 'select * from animals;' | od -c
0000000 1 | c a t \0 2 | d o g \0
0000014
Psql Tip #228
The
-? or --help flag prints a summary
of every psql option. Newer flags keep being added, so this is
the best way to discover what the current version supports. Only
the first lines are shown here, the rest being elided with
[...].
laetitia:~$ psql -?
psql is the PostgreSQL interactive terminal.
Usage:
psql [OPTION]... [DBNAME [USERNAME]]
General options:
-c, --command=COMMAND run only single command (SQL or internal) and exit
-d, --dbname=DBNAME database name to connect to
-f, --file=FILENAME execute commands from file, then exit
-l, --list list available databases, then exit
-v, --set=, --variable=NAME=VALUE
set psql variable NAME to VALUE
[...]
Psql Tip #229
The
-n or --no-readline flag disables
command-line editing: no history, no arrow-key or tab
completion. This avoids readline overhead in scripts, where
queries are piped in rather than typed.
laetitia:~$ printf 'select 2;\n' | psql -n -t
2
Psql Tip #230
The
--expanded flag is the long form of -x:
each column of a row is printed on its own line, prefixed with
the column name. This is especially readable for wide rows.
laetitia:~$ psql --expanded -c 'select * from animals;'
-[ RECORD 1 ]
id | 1
name | cat
-[ RECORD 2 ]
id | 2
name | dog
See Postgres
documentation for more information.
Try a new tipSee them all