PostgreSQL psql Commands

psql is the text-based terminal program that comes with PostgreSQL. You type a line, press Enter, and the server sends back an answer. Think of psql as a walkie-talkie between you and the database: you speak, the database replies, and nothing fancy sits in between.

  You (keyboard)  ---->  psql  ---->  PostgreSQL Server
                                            |
  Screen (results) <----  psql  <-----------+

Graphical tools exist, but psql runs on every server, every laptop, and every cloud machine. Learning it once gives you a skill that works everywhere.

Connecting to a Database

A connection needs four details: the host, the port, the user name, and the database name. The command below connects to a database named bakery on your own computer.

psql -h localhost -p 5432 -U postgres -d bakery

Flag	Meaning	Default
-h	Host name or address of the server	localhost
-p	Port number	5432
-U	User name	Your system user
-d	Database name	Same as the user name

After a successful login, the prompt changes to bakery=#. The name before the symbol tells you which database you are working in. A # means you are a superuser. A > means you are a regular user.

Two Kinds of Commands

psql understands two families of input. Mixing them up causes most beginner confusion.

SQL Statements

These go to the server. They must end with a semicolon. Without the semicolon, psql waits for more text and shows a prompt like bakery-#.

SELECT current_date;

Meta-Commands

These talk to psql itself. They start with a backslash and need no semicolon. The server never sees them.

  SELECT ...;     --> travels to the server
  \dt             --> stays inside psql

Everyday Meta-Commands

Command	What It Does
\l	Lists all databases
\c dbname	Switches to another database
\dt	Lists tables in the current schema
\d tablename	Shows columns, types, and indexes of one table
\dn	Lists schemas
\du	Lists roles and users
\df	Lists functions
\dv	Lists views
\timing	Turns query timing on or off
\x	Turns expanded display on or off
\i file.sql	Runs the commands stored in a file
\o file.txt	Sends query output to a file
\?	Shows help for all meta-commands
\h SELECT	Shows help for an SQL statement
\q	Quits psql

Using psql Effectively

Exploration, display options, and script files make psql a complete daily tool.

Exploring a Database Step by Step

Imagine you join a bakery business and receive access to its database. You know nothing about it. Four commands reveal the whole picture.

\l
\c bakery
\dt
\d orders

The first command lists every database. The second moves you into bakery. The third shows the tables, and the fourth shows the structure of the orders table. In under a minute, you know what the database holds.

Expanded Display

A table with many columns wraps across the screen and becomes unreadable. Expanded display turns each row into a small vertical card.

  Normal view:    id | name | price | stock | created_at | ...
  Expanded view:  id         | 1
                  name       | Sourdough
                  price      | 4.50

Type \x to switch it on. Type \x again to switch it off. Use \x auto to let psql pick the best style for each result.

Measuring Query Speed

Type \timing once. Every later statement reports its run time in milliseconds. This habit helps you spot slow queries long before users complain.

Running Script Files

Store long commands in a text file and run them with \i. A file named setup.sql that creates tables and inserts sample rows runs with one line.

\i /home/user/setup.sql

Run a file straight from the operating system shell without opening psql first.

psql -d bakery -f setup.sql

Saving Results to a File

The \o command redirects output. Run \o report.txt, execute a query, and run \o with no name to return output to the screen. The file now holds the query result.

Useful Habits

• Press the Up arrow to recall earlier commands.
• Press Tab to complete table and column names.
• Use \e to open the last query in a text editor.
• Check the prompt before running DELETE or DROP, so you know which database you are in.

Practice Exercise

Connect to any database you own. List all databases with \l. Switch to one with \c. List its tables with \dt. Pick one table, inspect it with \d, and turn on \timing before you run a SELECT query on it.