PostgreSQL includes a handy command line tool named psql. This program opens an interactive text interface that provides access to your databases. You can use it to find out various information about your database structures and metadata.
You can also use psql to execute any SQL commands to create tables, insert rows, and query existing data.
Using the psql Program
The psql program runs as 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 either not installed or not on your path. In the latter case, make sure to set your PATH environment variable appropriately.
You can connect to a specific database by naming it after the psql command:
psql orders
By default, your PostgreSQL server is probably running on port 5432. If it isn’t, you can specify the port to connect to using the -p option:
psql -p 1234
Listing and Connecting to Databases
Once you’re in the interactive shell, you can run various psql commands. These include commands to list available databases, change output style, etc. Additionally, you can execute all standard SQL commands that PostgreSQL supports.
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 \l. This lists the databases available on the PostgreSQL server you’re connected to:
\l
The command produces output in a tabular format, showing the name of each database along with its owner and other attributes.
You can connect to a different database using the \c command, followed by the name of your target database:
\c postgres
Type \c on its own to print a message telling you which database you’re currently connected to.
Fetching Table Metadata
Without any following arguments, the \d command lists tables, views, and sequences:
\d
You can also use \d to describe a specific table. Simply include the name of the table after the \d:
\d employees
These commands produce different outputs, but both use a consistent tabular format.
You can list all the available tables in the current database with the \dt command:
\dt
The output presents each table on a single row, with minimal information.
There are several other, similar commands to view various types of table information. For example, to list available views, use \dv:
\dv
Like the \dt command, \dv shows 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. Possibly the most useful command to remember is the one that tells you about all these commands. Use \? and you’ll see a list of each command with a synopsis of how to use it and an explanation of what it does.
\s shows you a history of what you’ve previously typed, including SQL statements and psql commands.
The \e command is highly useful, especially if you’re experimenting with long SQL statements. This command opens your default command-line text editor with the last SQL command. You can then edit that command, save and quit your editor, and psql will rerun it with your changes.
psql Commands Are Great For Database Inspection
The psql interactive shell is a versatile program. It allows you to execute standard SQL commands and special psql commands. The latter give you access to all sorts of information which is particularly useful if you’re performance tweaking or designing a complex database.
Remember to use the \? command to query the huge list of available commands.