How to work with a PostgreSQL database using psql commands

PostgreSQL includes a handy command-line tool called psql. This program opens an interactive text interface that gives access to your databases. You can use it to find various information about your database structures and metadata.

You can also use psql to run SQL commands to create tables, insert rows, and query existing data.

Using the psql program

The psql program works like an interactive terminal that allows you to send commands to a PostgreSQL database. If you have PostgreSQL installed, you should be able to run it on the command line by typing:

psql

If you get a psql: command not found error then PostgreSQL is not installed or not in your path. In the latter case, be sure to set your PATH environment variable correctly.

You can connect to a specific database by naming it after the psql ordered:

psql orders

By default, your PostgreSQL server is probably running on port 5432. If not, you can specify the port to connect to using the -p option:

psql -p 1234

List and connection to databases

Once you are in the interactive shell, you can run various psql commands. These include commands to list the available databases, change the output style, and more. Additionally, you can run all standard SQL commands supported by PostgreSQL.


The interactive shell prompt includes the name of the database you are connected to. Your psql command line should look like this:

postgres=

A good first command to test and get your bearings is I. This lists the databases available on the PostgreSQL server you are connected to:

l

The command produces output in the form of a table, showing the name of each database along with its owner and other attributes.

You can connect to another database using vs command, followed by the name of your target database:

c postgres

Type c alone to display a message telling you which database you are currently connected to.

Without any following arguments, the D The command lists tables, views, and sequences:

d

You can also use d to describe a specific table. Just include the table name after the d :

d employees

These commands produce different output, but both use a consistent tabular format.

You can list all the tables available in the current database with the dt ordered:

dt

The output presents each table on a single line, with minimal information.

There are several other similar commands for displaying various types of table information. For example, to list available views, use dv:

dv

Like the dt command, dv displays the schema, name, type, and owner of each view.

Miscellaneous commands

There are many, many psql commands available, far too many to cover in a short article. Perhaps the most useful command to remember is the one that tells you about all of these commands. Use ? and you’ll see a list of each command with a summary of how to use it and an explanation of what it does.

s shows you a history of what you’ve typed previously, including SQL statements and psql commands.

The e The command is very useful, especially if you are experimenting with long SQL statements. This command opens your default command line text editor with the latest SQL command. You can then modify this command, save and exit your editor, and psql will run it again with your changes.

psql commands are great for database inspection

The psql interactive shell is a versatile program. It allows you to run standard SQL commands and special psql commands. These give you access to all sorts of information, which is particularly useful if you are modifying performance or designing a complex database.

Remember to use the ? command to query the huge list of available commands.

Maria H. Underwood