PostgreSQL: Up and Running, 2nd Edition

Chapter 3. psql

psql is the de rigueur command-line utility packaged with PostgreSQL. Aside from its most common use of running queries, you can use psql as an automated scripting tool; as a tool for importing or exporting data, restoring tables, and database administration; and even as a minimalistic reporting tool. As with other command-line tools, you have to be familiar with a myriad of options. If you have access only to a server’s command line with no GUI, psql is pretty much your only choice for querying and managing PostgreSQL. If you fall into this category, we suggest that you print out the dump of psql help from the psql Interactive Commands and frame it right above your workstation.

Environment Variables

As in the other command-line tools packaged with PostgreSQL, you can forgo explicitly specifying your host, port, and user by setting the PGHOST, PGPORT, and PGUSER environment variables as described in Environment Variables. You can also set your password in PGPASSWORD or use a password file as described in The Password File. psql since version 9.2 accepts two new environment variables:

PSQL_HISTORY

Sets the name of the psql history file that lists all commands executed in the recent past. The default is ~/.psql_history.

PSQLRC

Sets the location and name of the configuration file.

If you omit the parameters without having set the environment variables, psql will use the standard defaults. In the examples in this chapter, we’ll assume you are using default values or have these ...