How to Use psql Command Line for PostgreSQL

Use the psql command line tool to connect to a PostgreSQL database by running psql -h host -U username -d database. Once connected, you can run SQL commands directly in the terminal to manage your database.

Syntax

The basic syntax to start psql is:

• psql: Starts the tool.
• -h host: Specifies the database server address (optional if local).
• -U username: Specifies the user to connect as.
• -d database: Specifies the database name to connect to.

After running this, you enter an interactive prompt where you can type SQL commands.

bash

psql -h host -U username -d database

Example

This example shows how to connect to a local PostgreSQL database named mydb as user postgres and run a simple query.

bash

psql -U postgres -d mydb

-- After connecting, run this SQL command:
SELECT version();

Output

                                                 version                                                  
----------------------------------------------------------------------------------------------------------
 PostgreSQL 15.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.3.0, 64-bit
(1 row)

Common Pitfalls

Common mistakes when using psql include:

• Not specifying the correct username or database name, causing connection failure.
• Forgetting to set the PGPASSWORD environment variable or not entering the password when prompted.
• Trying to run SQL commands directly in the system shell instead of inside the psql prompt.

Always ensure you are inside the psql prompt (it shows dbname=#) before typing SQL commands.

sql

Wrong:
SELECT * FROM users;

Right:
psql -U postgres -d mydb
mydb=# SELECT * FROM users;

Quick Reference

Command	Description
\q	Quit the psql prompt
\c database	Connect to a different database
\dt	List tables in the current database
\d tablename	Show table structure
\l	List all databases
\?	Show psql help commands

Key Takeaways

Use psql -h host -U username -d database to connect to PostgreSQL from the command line.

Run SQL commands inside the psql prompt, not in the system shell.

Use backslash commands like \q to quit and \dt to list tables.

Ensure correct credentials and database names to avoid connection errors.

Set the PGPASSWORD environment variable or enter your password when prompted.