Difficulty: beginner
Estimated Time: 10 minutes

Introduction to Using the Command Line with PostgreSQL

psql is a command line tool that is included with a PostgreSQL install. It lets you run queries and scripts, manage databases, and interact with the computer using a command-line client.

The advantage of using the command line is that it can save you time - entering short text commands might be even faster than clicking through a graphical user interface (GUI), especially as you become more and more comfortable with the command line. Additionally, there are some functions that you can perform only in the command line. You can even use psql within a shell script, allowing you to execute multiple commands together and automate repetitive tasks.

In this scenario, you'll use psql to interact with a PostgreSQL database. A database with pre-loaded data has already been added for you. You'll use the following credentials to connect to the workshop database:

  1. Username: groot
  2. Password: password

Final notes

The command line might seem daunting, but even an understanding of some basic commands can increase your confidence as a developer and help you become more effective in your work.

You've now gotten a taste of psql and some command line basics. Here are a couple of resources we recommend for learning more:

Enjoy learning about PostgreSQL? Sign up for our newsletter and get the latest tips from us each month.

Introduction to Using the Command Line with PostgreSQL

Step 1 of 5

The psql Command

Introduction to psql

psql is described in the docs as a "terminal-based front-end to PostgreSQL." This means that instead of having to use a GUI tool such as pgAdmin to create, access, or manipulate a database, you can use text commands via a command line interface (CLI).

We'll get started by connecting to a database.

Connect to the workshop database

The database contains storm events data, which is public domain data from the National Weather Service.

In your interactive terminal, you're currently logged in as root user, as indicated by the command line prompt:

Terminal prompt

Note

Depending on the system you're using and the shell (i.e. program you're in), the prompt might look a little different. You'll see this below when you're in the psql shell.

We want to connect to the PostgreSQL database as groot, so we'll enter the psql command, accompanied by some flags:

psql -h localhost -U groot -d workshop

Each command line flag or option begins with a - or --, and what follows after the space is the parameter you're passing in with the command.

  1. The -h option specifies the host server (e.g. localhost), or socket directory.
  2. -U specifies the user you're connecting as (groot).
  3. -d specifies the database you're connecting to (workshop).

The CLI shows a response whenever a command is run. In this case, since you are logging in to a database as the user groot, the command line will then prompt you for the password (enter password). You won't see the password displayed as you type, but the terminal is recording your keystrokes.

Terminal prompt for password

Once you're logged in to the database server, the prompt in the terminal should change. The psql prompt indicates the database you're connected to, along with =>.

Postgres prompt

The beauty of the command line is that there are often shortcuts or tips and tricks. For example, you could shorten the command used above even further to:

psql -h localhost workshop groot