In-depth with psql

psql is a terminal-based front-end to PostgreSQL.

There are many actions which can be performed with OS ‘psql’ command itself without explicitly switching to ‘psql’ prompt. To know all such possible actions, run ‘psql --help‘ which will display all options and details.

psql --help

postgres@MKM-HP-ENVY:/etc/postgresql/13/mars$ psql --help
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: "postgres")
  -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: "/var/run/postgresql")
  -p, --port=PORT          database server port (default: "5432")
  -U, --username=USERNAME  database user name (default: "postgres")
  -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 <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>
postgres@MKM-HP-ENVY:/etc/postgresql/13/mars$

As seen from the above output, psql is a very capable program. Some of the important things it can do are as below:

• Connect to a database with various options as covered here and here using options -d, -h, -p, -U, -w, -W

psql -p 5433 -d appdb -h localhost -U app_user -W

• Run one or more commands (SQL or internal) and exit, using option -c

psql -c "\l+"or
psql -c '\l+'

psql -c "select current_catalog;"

In the below example, 3 commands are being run against the database. The first one is to set the expanded mode on and the other two are to select some details about the database.

psql postgresql://hr_user@127.0.0.1:5433/appdb?connect_timeout=10 -c '\x' -c "select current_database();" -c "select current_catalog;"

Alternate way:

echo '\x \\ select version();' | psql postgresql://hr_user@127.0.0.1:5433/appdb?connect_timeout=10

We can create a database too.

psql -c "create database testdb;"

• List databases and their details using option -l or --list

psql -l
OR
psql --list

• Run SQL file against the database in script mode

If you need to run ad-hoc SQL statements or commands against the database, then it can be done as part of ‘psql’ command itself. When done so, a connection is made to the database, the command is run, and the connection is closed, all in one go as shown below. Here, ‘-x’ option turns on expanded table output so that if the output is big, the ‘psql’ prompt exits after showing up the results.

• Connect to a database and run SQL file from psql prompt

Let’s see all that in action.

List databases, objects and perform other actions

All options of psql can be seen by running psql \?

psql
\?
or
psql -c "\?"

\d is used to list all table views etc while \d<option> is used to list many other aspects.

Frequently used options are listed here.