Using psql

What is it? #

psql is the command-line client that ships with PostgreSQL. You type a command, press Enter, and see the answer.

It understands two different kinds of input. Ordinary SQL — SELECT * FROM customers; — is sent to the server. And backslash commands such as \dt are handled by psql itself as shortcuts for inspecting the database.

Knowing about ten backslash commands covers almost everything you will do day to day. They are faster than any graphical tool once they are in your fingers, and they work over SSH on a server where no graphical tool is available.

The single most important habit: know which database you are connected to. psql prints it in the prompt for exactly this reason.

Think of it like this #

psql is a torch and a label-maker for your filing rooms.

The backslash commands are the torch: "show me what cabinets are in here", "show me what is printed on this cabinet's label", "whose keys open this room". They do not change anything, they let you see. The SQL you type is the actual filing work.

Simple example #

You have just installed PostgreSQL and want to create a database for a shop application, add a table, and confirm it exists.

You connect as the postgres user, create the database, switch into it, create a table, and list the tables to check. Six commands in total, and you now have somewhere to put data.

Code #

# ---------- Connecting ----------

sudo -u postgres psql                 # local, as the postgres OS user

# Connecting with explicit details (the form you use against a remote server):
psql -h localhost -p 5432 -U appuser -d shop
#   -h host   -p port   -U username   -d database name

# You will be asked for a password unless one is stored (see the backup lesson).

-- ---------- Databases ----------

CREATE DATABASE shop;        -- create a new database

\l                           -- list all databases  (short for \list)
\c shop                      -- connect to the "shop" database  (short for \connect)

-- After \c the prompt changes, e.g.  shop=#  — this is how you know where you are.

-- ---------- Tables and schemas ----------

CREATE TABLE customers (
    id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name  text NOT NULL,
    email text NOT NULL UNIQUE
);

\dt          -- list tables in the current schema
\dt *.*      -- list tables in every schema
\d customers -- describe the table: columns, types, indexes, constraints
\dn          -- list schemas
\df          -- list functions
\dv          -- list views
\di          -- list indexes

-- ---------- Users, roles and permissions ----------

\du                    -- list roles (users are roles that can log in)

-- What is allowed on which table:
\dp customers          -- "display privileges"  (\z does the same)

-- Who am I and where am I, right now?
SELECT current_user, current_database(), current_schema();

---------- The handful worth memorising ----------

\l          list databases
\c NAME     connect to database NAME
\dt         list tables
\d NAME     describe table NAME
\dn         list schemas
\du         list roles
\dp NAME    show privileges on NAME
\x          toggle expanded output (one column per line — great for wide rows)
\timing     toggle showing how long each query took
\?          help on backslash commands
\h SELECT   help on the SQL command SELECT
\q          quit

-- ---------- Two habits that save real time ----------

\timing on        -- now every query prints its duration; useful from day one
\x auto           -- switch to one-column-per-line only when a row is too wide

-- Run a single command without entering the psql prompt at all:
-- (useful in scripts and cron jobs)
-- psql -U appuser -d shop -c "SELECT count(*) FROM orders;"

How it works #

When you type something starting with a backslash, psql handles it locally. When you type SQL, psql sends it to the server and prints what comes back. That is the whole model.

SQL statements need a semicolon. Without one, psql assumes you have not finished typing and shows a continuation prompt like shop-# instead of running anything. Beginners regularly think psql has frozen when it is simply waiting for the semicolon. Backslash commands do not need a semicolon.

\l and \dt look similar but answer different questions: \l lists the databases on this server, \dt lists the tables inside the database you are currently connected to. If \dt says "Did not find any relations", the usual reason is that you are connected to the wrong database — check the prompt.

\c shop opens a new connection to a different database; it does not keep the old one. Anything session-specific, such as an open transaction or a temporary table, is gone afterwards.

\d customers is the one to reach for constantly. It shows every column with its type, whether it may be empty, its default, plus the indexes, primary key, foreign keys and check constraints. It answers "what does this table actually look like" in one line of typing.

\du lists roles. In PostgreSQL there is no separate concept of "user" — a user is simply a role that is allowed to log in. The roles-and-security lesson covers this properly.

\x is worth knowing early. A table with fifteen columns wraps unreadably in a terminal; expanded mode prints one column per line instead. \x auto picks whichever is readable.

Real-world use #

On a production server you will often be connected over SSH with no graphical tool available, so these commands are not a fallback — they are the normal way to look at a live database.

psql -c "..." runs one statement and exits, which makes it the building block for scripts, health checks and cron jobs. Most of the backup automation later in this track is built from exactly that form.

Because \dt is scoped to the current database, the first thing to do when something looks wrong is to check the prompt. "The table has disappeared" is almost always "I am connected to the wrong database".

\timing on is a small habit with a large payoff. Seeing that a query took 4 milliseconds today makes it obvious when the same query takes 900 milliseconds in three months, long before anyone files a complaint.

One caution: psql keeps a history file of everything you type, at ~/.psql_history. If you paste a password into a SQL statement, it is written to that file in plain text. Prefer the password-file approach covered in the automated-backup lesson.

Common mistakes #

• Forgetting the semicolon and thinking psql has frozen, when it is waiting for you to finish the statement.
• Running \dt in the wrong database and concluding the tables are gone — check the prompt first.
• Confusing \l (list databases) with \dt (list tables in the current database).
• Typing a password inside a SQL statement, which then sits in ~/.psql_history in plain text.
• Reading wide tables in normal mode instead of switching on \x and getting one column per line.

Practice #

Connect with psql and, using only backslash commands, answer these questions about your own server: How many databases exist? Which schemas are in one of them? What tables does it contain? What are the exact columns and constraints of one table? Which roles exist? Then turn on \timing and run the same query twice, and notice how much faster the second run is once the data is cached.