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. Install psql 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 not to 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. Store the file in your home directory and make it readable only by you. Usee 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 matches 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. Press Tab at the start of a line to display a list of available SQL commands.

The psql tool also has built-in commands that can help you manage your databases and tables.

\d is a 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 can use \watch 5 to run a query every 5 seconds. The query that's rerun is the last query you entered.