The psql client

The psql client is maintained by the PostgreSQL community, and it is a part of the PostgreSQL binary distribution. psql has many overwhelming 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, 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 one's favorite pager such as less or more. Also, psql does not come with an editor, but it can utilize several editors. The following example shows how to use nano editor in editing a function as follows:

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

• Auto completion and SQL syntax help: psql supports auto completion 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 often 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.

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

• -A: The output is not aligned; by default, the output is aligned.
• -q (quiet): 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 informs psql to ignore the psql configuration that is stored in ~/.psqlrc file.
• -o: This option specifies psql to output the query result to a certain location.
• -F: This option determines the field separator between columns. This option can be used to generate CSV, which is useful to import data to Excel files.
• 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 scan only or to specify the statement timeout.

Let's assume that we would like to write a bash script to check the number of opened connection systems:

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

The result of the command psql -AqXt –d postgres -c "SELECT count(*) FROM pg_stat_activity" is assigned to a bash variable. The options -AqXt, 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