Top psql Commands with Examples
PostgreSQL, often simply called Postgres, is a free and open-source relational database management system (RDBMS) known for its reliability, feature robustness, and performance. It has been actively developed for over 35 years and is considered one of the most advanced open-source RDBMSs available.
psql is the official CLI client to command the PostgreSQL database. It supports interactive and noninteractive use.
Below list the 10 most commonly used
psql commands with examples.
1. Connect to a database -
psql -U xxx -p -h xxx -d xxx
If you are using the default PostgreSQL username
postgres and have not set a password, you can connect to the database using the following command. You will be prompted to enter the password for the
If you are using a different username or have set a password for the
postgres user, you can connect to the database using the following command. You will be prompted to enter the password for the specified user.
If the PostgreSQL server is running on a different port than the default (5432), you can connect to the database using the following command. You will be prompted to enter the password for the specified user.
If the PostgreSQL server is running on a remote host, you can connect to the database using the following command. You will be prompted to enter the password for the specified user.
In all of these examples, you can also specify the name of the database you want to connect to using the
-d option. For example, to connect to the database named
my_database, you would use the following command:
2. Run a single command -
To run a single command in PostgreSQL using the
psql command-line interface, you can use the
-c option followed by the SQL command you want to execute.
3. List available databases -
This command will all of the databases that are currently available on the server. This can be useful for getting an overview of the databases that are available to you.
4. Switch to a specified database -
\c command switches to the specified database. This is useful for working with a specific database after you have listed the available databases.
5. List tables -
\dt command lists all of the tables that are currently available in the current database. This can be useful for getting an overview of the tables that are available to you in the current database.
6. Describe a specified table -
\d command describes the specified table. This provides information about the table's columns, data types, and constraints.
7. Listing users -
\du command lists all of the users that exist in the current database.
\du username command is used to list the roles (users) in the current database that have the specified username.
If the specified username does not match any roles in the current database, the output will be an empty table.
8. Show connections -
To list all active connections, execute the following SQL query.
You can also filter the results to specific connections based on criteria such as username, database name, or state. For example, to list only active connections to the database mydb, use the following query:
Similarly, to list connections currently executing queries, use the following query:
9. Kill connection -
Once you have identified the PID of the connection you want to kill, you can use the
pg_terminate_backend() function to terminate it. For example, the following command will kill the connection with PID 1234:
Using the kill command is a less common method for killing PostgreSQL connections, as it can be more dangerous and less reliable than using the pg_terminate_backend() function. However, it may be necessary in certain situations, such as when the connection is unresponsive or the pg_terminate_backend() function fails.
10. Quit -
\q command or the keyboard shortcut
Ctrl+Z on windows). This will immediately exit the psql command-line interface.