PostgreSQL | Connecting to PostgreSQL with psql | How to Use psql Options and Option List
When running psql, several options are available in addition to the options used to specify the user name and database for the connection. This page explains how to use the options that can be specified when running psql and lists the available options.
The options most often used when running psql are -h and -U, which are used when connecting to PostgreSQL, but several other options are also available.
Checking the Version
The -V option displays the PostgreSQL version.
psql -V
C:\>psql -V
psql (PostgreSQL) 12.2
C:\>
Displaying the Option List and Help
The -? option displays the option list and help.
C:\>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 (default: "devkuma")
-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
(e.g., -v ON_ERROR_STOP=1)
-V, --version output version information, then exit
-X, --no-psqlrc do not read startup file (~/.psqlrc)
-1 ("one"), --single-transaction
execute as a single transaction
-?, --help[=options] show this help, then exit
--help=commands list backslash commands, then exit
--help=variables list special variables, then exit
Input and output options:
-a, --echo-all echo all input from script
-b, --echo-errors echo failed commands
-e, --echo-queries echo commands sent to server
-E, --echo-hidden display queries that internal commands generate
-L, --log-file=FILENAME send session log to file
-n, --no-readline disable enhanced command line editing (readline)
-o, --output=FILENAME send query results to file (or |pipe)
-q, --quiet run quietly (only query output, no messages)
-s, --single-step single-step mode (confirm each query)
-S, --single-line single-line mode (end of line terminates SQL command)
Output format options:
-A, --no-align unaligned table output mode
--csv CSV (comma-separated values) table output mode
-F, --field-separator=STRING
field separator for unaligned output (default: "|")
-H, --html HTML table output mode
-P, --pset=VAR[=ARG] set printing option VAR to ARG (see \pset command)
-R, --record-separator=STRING
record separator for unaligned output
(default: newline)
-t, --tuples-only print rows only
-T, --table-attr=TEXT set HTML table tag attributes (e.g., width, border)
-x, --expanded expanded table output
-z, --field-separator-zero
set field separator for unaligned output to zero byte
-0, --record-separator-zero
set record separator for unaligned output to zero byte
Connection options:
-h, --host=HOSTNAME database server host or socket directory
(default: "local socket")
-p, --port=PORT database server port (default: "5432")
-U, --username=USERNAME database user name (default: "devkuma")
-w, --no-password never prompt for password
-W, --password force password prompt (should happen automatically)
For more information, type "\?" (internal commands) or "\help" (SQL
commands) from within psql, or see the psql section in the PostgreSQL
documentation.
Report bugs to <pgsql-bugs@lists.postgresql.org>.
C:\>
Using HTML Table Tags for Result Display
Some options affect how results are displayed after connecting to PostgreSQL with psql. For example, after connecting to PostgreSQL, listing databases displays output like this:
C:\>psql -U postgres
Password for user postgres:
psql (12.2)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------------+------------------+-----------------------
postgres | postgres | UTF8 | Korean_Korea.949 | Korean_Korea.949 |
template0 | postgres | UTF8 | Korean_Korea.949 | Korean_Korea.949 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | Korean_Korea.949 | Korean_Korea.949 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
postgres=#
If you use the -H option when starting psql and connecting to PostgreSQL, the same database list can be output in a format that uses HTML table tags.
C:\>psql -H -U postgres
Password for user postgres:
psql (12.2)
Type "help" for help.
postgres=# \l
<table border="1">
<caption>List of databases</caption>
<tr>
<th align="center">Name</th>
<th align="center">Owner</th>
<th align="center">Encoding</th>
<th align="center">Collate</th>
<th align="center">Ctype</th>
<th align="center">Access privileges</th>
</tr>
<tr valign="top">
<td align="left">postgres</td>
<td align="left">postgres</td>
<td align="left">UTF8</td>
<td align="left">Korean_Korea.949</td>
<td align="left">Korean_Korea.949</td>
<td align="left"> </td>
</tr>
<tr valign="top">
<td align="left">template0</td>
<td align="left">postgres</td>
<td align="left">UTF8</td>
<td align="left">Korean_Korea.949</td>
<td align="left">Korean_Korea.949</td>
<td align="left">=c/postgres<br />
postgres=CTc/postgres</td>
</tr>
<tr valign="top">
<td align="left">template1</td>
<td align="left">postgres</td>
<td align="left">UTF8</td>
<td align="left">Korean_Korea.949</td>
<td align="left">Korean_Korea.949</td>
<td align="left">=c/postgres<br />
postgres=CTc/postgres</td>
</tr>
</table>
<p>(3 rows)<br />
</p>
postgres=#
Not only the database list shown here, but other query results are also output in a format that uses HTML table tags.
postgres=# select now()
postgres-# ;
<table border="1">
<tr>
<th align="center">now</th>
</tr>
<tr valign="top">
<td align="left">2020-03-15 02:30:16.292241+09</td>
</tr>
</table>
<p>(1 row)<br />
</p>
postgres=#
Executing SQL from a File
You can use the -f option to execute SQL from a file.
First, create a SQL file as follows.
D:\>copy con file.sql
select now();^Z
1 file(s) copied.
Run the file as follows to see the output.
D:\>psql -U postgres -f file.sql
Password for user postgres:
now
-------------------------------
2020-03-15 02:39:17.327284+09
(1 row)
D:\>
–
Because psql options provide many useful features, it is helpful to check the option list later and see what you can do with them.