psql The PostgreSQL Command-Line Tool

Connect to and interact with PostgreSQL databases using the psql CLI tool.

Overview

psql is the official command-line interface for PostgreSQL. It ships with every PostgreSQL installation and is the fastest way to interact with your database no GUI needed. Once connected, you can run SQL queries, manage users, inspect tables, import files, and automate tasks via scripts. psql has two types of commands: SQL statements (sent to the server) and backslash meta-commands (handled by psql itself). Meta-commands start with a backslash for example \l lists databases and \dt lists tables. These meta-commands are shortcuts that would otherwise require querying system catalog tables. Understanding psql thoroughly pays dividends: it is available on every server where PostgreSQL runs, it works over SSH, and it is scriptable. Even if you prefer a GUI like pgAdmin or DBeaver day-to-day, psql is essential for production troubleshooting.

Key Points

•Connect with: psql -U username -d dbname -h host -p port
•\l list all databases; \c dbname switch database
•\dt list tables; \d tablename describe table columns and constraints
•\i file.sql execute a SQL file; \o file.txt send output to file
•End every SQL statement with a semicolon; meta-commands do not need one
•\q quit psql; Ctrl+D also works

Examples

Connect to a database

-- From terminal (not inside psql):
psql -U postgres -d mydb

-- Or connect to a remote server:
psql -U admin -d production -h db.example.com -p 5432

The -U flag specifies the user, -d the database, -h the host, -p the port.

Common meta-commands inside psql

\l              -- list all databases
\c mydb         -- connect to mydb
\dt             -- list tables in current schema
\d users        -- describe the users table
\dn             -- list schemas
\du             -- list roles/users
\timing         -- toggle query timing
\q              -- quit

Run a SQL file and write output to a file

\i /path/to/script.sql
\o /tmp/results.txt
SELECT * FROM orders WHERE total > 1000;
\o               -- stop writing to file

psql The PostgreSQL Command-Line Tool

Overview

Key Points

Examples

Exercises

Knowledge Check

Which psql meta-command describes the columns and constraints of a table named "orders"?