PSQL for busy developers

The PSQL command line tool is essential in a developer's toolkit as it provides full command-line access to PostgreSQL databases.

Getting psql installed

The psql command is a powerful tool for interacting with PostgreSQL databases. You should install it on your local machine to interact with the PostgreSQL database. Unless you've just installed Postgres natively on your machine, you'll need to install the psql client.

macOS:

You can install the PostgreSQL client using Homebrew:

brew install libpq

Windows:

Download the PostgreSQL client from the official website.

Linux:

Use your distribution’s package manager to install the PostgreSQL client. For example, on Ubuntu:

sudo apt-get install postgresql-client

Connecting to a database

Connection strings

The psql client can use a connection string to connect to a database. The connection string is a single string that contains all the information needed to connect to a database.

PGPASSWORD environment variable

Best practice is to not have your password in your connection string or in your command history. Instead, you can use the PGPASSWORD environment variable to store your Postgres password. This is simple, but not very secure because some Unix systems allow other users to see the environment variables of other users.

.pgpass file

Creating a .pgpass file is a more secure way to store your password. The .pgpass file is a plain text file that contains the connection information for your databases. The file should be stored in your home directory and should be readable only by you. The file should have the following format:

hostname:port:database:username:password

Hostname, port, database and username can all be set to wildcards to match any value. For example, *:*:*:postgres:password would match any database on any host for the user postgres.

Read more about the .pgpass file in the Postgres documentation.

The PSQL command line

You can enter SQL commands directly into the PSQL command line. Be sure to end each command with a semicolon, otherwise PSQL doesn't execute the command.

You can use tab-completion in many situations to help you complete commands and table names. Pressing tab at the start of a line will show you a list of available SQL commands.

PSQL also has a number of built-in commands that can help you manage your databases and tables.

\d is a very useful command that shows a range of different information when followed by another character. Think of it as d for display. For example, \dt shows all the tables in the current database, \dv shows all the views, and \ds shows all the sequences.

\watch is useful when you want to repeat running a query at regular intervals. For example, you could use \watch 5 to run a query every 5 seconds. The query that will be re-run is the last query you entered.