psql is a terminal based interface for PostgreSQL.
psql enables you to administer PostgreSQL from the command line interface (CLI) as an alternative to using a graphical user interface (GUI), such as pgAdmin, Postico, Azure Data Studio, etc.
Using psql, you can do things such as:
- Run SQL commands. For example, you can:
- Run commands from a file (instead of standard input).
- Enter meta-commands (also known as “slash commands” and “backslash commands”). Meta-commands can be useful for administration and scripting.
- Enter other shell like features to help with administration, scripting, etc.
- Pass options to psql. For example, you can specify a database to connect to, the user name, a file name to read, and much more.
Syntax
The syntax for psql goes like this:
psql [OPTION]... [DBNAME [USERNAME]]So you can simply open up a terminal window and type psql, or you can type psql, followed an option.
Connecting to PostgreSQL via psql
Here’s an example of using psql to connect to a PostgreSQL database:
psql -d music -U barneyResult:
psql (12.1) Type "help" for help. music=#
This connects to the music database using the user called barney.
Here it is again, but this time showing the full terminal input and output:
barney@Barneys-MacBook-Pro ~ % psql -d music -U barney
psql (12.1)
Type "help" for help.
music=#Run SQL Queries
Now that we’ve connected to the music database, we can run SQL queries against it.
Example:
barney@Barneys-MacBook-Pro ~ % psql -d music -U barney
psql (12.1)
Type "help" for help.
music=# SELECT * FROM Artists;
artistid | artistname | activefrom
----------+------------------------+------------
1 | Iron Maiden | 1975-12-25
2 | AC/DC | 1973-01-11
3 | Allan Holdsworth | 1969-01-01
4 | Buddy Rich | 1919-01-01
5 | Devin Townsend | 1993-01-01
6 | Jim Reeves | 1948-01-01
7 | Tom Jones | 1963-01-01
8 | Maroon 5 | 1994-01-01
9 | The Script | 2001-01-01
10 | Lit | 1988-06-26
11 | Black Sabbath | 1968-01-01
12 | Michael Learns to Rock | 1988-03-15
13 | Carabao | 1981-01-01
14 | Karnivool | 1997-01-01
15 | Birds of Tokyo | 2004-01-01
16 | Bodyjar | 1990-01-01
(16 rows)
music=#Getting Help
You can type the following to return the help list:
psql --helpResult:
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: "barney")
-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 (if non-interactive)
-?, --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 (no messages, only query output)
-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 turn on 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: "barney")
-w, --no-password never prompt for password
-W, --password force password prompt (should happen automatically)
For more information, type "\?" (for internal commands) or "\help" (for SQL
commands) from within psql, or consult the psql section in the PostgreSQL
documentation.
Report bugs to <[email protected]>.
As you can see, the help list contains further commands you can use to get help about specific areas.
For example, you can use psql --help=commands to return a list of commands, or psql -V to return the psql version.
And while you’re using psql, you can type \? to get a list of internal commands, or \help to return a list of SQL commands.