The psql client

The psql client is maintained by the PostgreSQL community, and it's part of the PostgreSQL binary distribution. psql has an overwhelming number of features, such as the following:

• psql is configurable: psql can be configured easily. The configuration might be applied to user-session behavior, such as commit and rollback, a psql prompt, history files, and even shortcuts for predefined SQL statements.
• Integration with editor and pager tools: The psql query result can be directed to your favorite pager, such as less or more. Also, psql doesn't come with an editor, but it can utilize several editors. The following example shows how to use the nano editor to edit a function:

postgres=# \setenv PSQL_EDITOR /bin/nano
postgres=# \ef

• Autocompletion and SQL syntax help: psql supports autocompletion for database object names and SQL constructs query result format control; psql supports different formats, such as HTML and latex. For example, when using the \ef meta command, a template is generated as follows:

CREATE FUNCTION ( )
 RETURNS
 LANGUAGE
 -- common options: IMMUTABLE STABLE STRICT SECURITY DEFINER
AS $function$

$function$

• The psql client tool is very handy in shell scripting, information retrieval, and learning the PostgreSQL internals. The following are some of the psql meta commands that are used daily:
  • \d+ [pattern]: This describes all the relevant information for a relation. In PostgreSQL, the term relation is used for a table, view, sequence, or index.
  • \df+ [pattern]: This describes a function.
  • \z [pattern]: This shows the relation access privileges.
  • \timing: Displays the execution time.
  • \h: Gives syntax help on the specified SQL command.
  • \c: Connects to a database.

For shell scripting, there are several options and environment variables that make psql very convenient:

• -A: The output isn't aligned; by default, the output is aligned.
• -q: This option forces psql not to write a welcome message or any other informational output.
• -t: This option tells psql to write the tuples only, without any header information.
• -X: This option tells psql to ignore the psql configuration that's stored in the ~/.psqlrc file.
• -o: This option tells psql to output the query result to a certain location.
• -F: This option determines the field separator between columns. It can be used to generate CSV, which is useful in exporting data.
• PGOPTIONS: psql can use PGOPTIONS to add command-line options to send to the server at runtime. This can be used to control statement behavior, such as to allow index scans only or to specify the statement timeout.

Let's imagine we want to write a bash script to check the number of opened connection systems:

#!/bin/bash
proc_number='PGOPTIONS='--statement_timeout=0'
psql -AqXt -c"SELECT count(*) FROM pg_stat_activity"'

The result of the psql -AqXt -c "SELECT count(*) FROM pg_stat_activity" command is assigned to a bash variable. PGOPTIONS='--statement_timeout=100' is used for demonstration only and to show how to change the default execution behavior. statement _timeout is used to time out the query after a certain time in milliseconds; this is often used if the query is blocking and requires a lot of time, as a safety measure, to not block other processes. The -AqXt options, as discussed previously, cause psql to return only the result without any decoration, as follows:

psql -AqXt -c "SELECT count(*) FROM pg_stat_activity"
1