psql is the official command-line interface for PostgreSQL, the world's most advanced open-source relational database. It ships with every PostgreSQL installation, costs nothing extra, and has been quietly doing heavy lifting for developers and DBAs since PostgreSQL's earliest days in the 1990s. If you work with Postgres at all, learning psql properly is one of the highest-return investments you can make. This is that guide.

---

What Is psql, Exactly?

psql is an interactive terminal application. You type SQL (or psql-specific meta-commands), press Enter, and get results back immediately. It also runs scripts, exports data, formats output for reports, and connects to databases across networks. Think of it as the Swiss Army knife that lives next to your database at all times.

PostgreSQL was originally developed at UC Berkeley by Michael Stonebraker and his team. psql has been part of the project since the beginning and continues to be actively maintained by the PostgreSQL Global Development Group.

---

Installation and Connecting

psql is bundled with PostgreSQL itself. Install Postgres, and psql is already there.

# macOS
brew install postgresql@17

# Ubuntu / Debian
sudo apt install postgresql-client

# Windows
# Download the installer from https://www.postgresql.org/download/windows/
# psql.exe will be in the bin folder of the install directory

# Verify it worked
psql --version

Connecting to a database is straightforward:

# Local connection with your OS username
psql -d mydb

# Full connection string
psql -h localhost -p 5432 -U myuser -d mydb

# Using a connection URL (very convenient in scripts)
psql "postgresql://myuser:mypassword@localhost:5432/mydb"

psql will prompt for a password if the server requires one. You can also set the PGPASSWORD environment variable or use a .pgpass file to avoid typing it repeatedly.

---

The psql Prompt and Meta-Commands

Once connected, you will see a prompt like mydb=#. The suffix =# means you are a superuser; => means a regular user.

Everything that starts with a backslash (\) is a meta-command — a psql instruction rather than a SQL statement. These are the ones you will use constantly:

\l          -- list all databases
\c mydb     -- connect to a different database
\dt         -- list tables in the current schema
\dt *.*     -- list tables in all schemas
\d users    -- describe the users table (columns, types, indexes)
\di         -- list indexes
\dv         -- list views
\df         -- list functions
\dn         -- list schemas
\du         -- list roles and users
\x          -- toggle expanded display (great for wide rows)
\timing     -- toggle query execution time display
\e          -- open the last query in your $EDITOR
\i file.sql -- execute a SQL file
\o file.txt -- send output to a file
\watch N    -- re-execute the last query every N seconds (Ctrl+C to stop)
\conninfo   -- show current connection details (host, port, user, SSL)
\q          -- quit

The \d family of commands is particularly powerful. \d tablename gives you everything about a table — columns, data types, constraints, indexes, and foreign keys — in a single command. No more guessing.

---

Searching Your History and Editing Queries

psql keeps a persistent command history (in ~/.psql_history on Unix, %APPDATA%\postgresql\psql_history on Windows). Use the up and down arrow keys to navigate it. You can also search backwards with Ctrl+R, just like in Bash.

For longer queries, \e is your best friend. It opens whatever you last typed into your system's default editor ($EDITOR on Unix, Notepad on Windows by default). Edit the query, save and close the file, and psql runs it automatically. Set your editor once and forget it:

# In your shell profile
export EDITOR=vim        # or nano, code, etc.

---

Query Output Formatting

Raw psql output can be hard to read, especially with many columns. These settings make a significant difference:

-- Expanded mode: each column on its own line (great for wide tables)
\x

-- Or toggle it automatically based on terminal width
\x auto

-- Change the output format entirely
\pset format csv          -- CSV output
\pset format html         -- HTML table
\pset format latex        -- LaTeX tabular

-- Border style (0 = none, 1 = inner, 2 = full)
\pset border 2

-- Null display (empty by default, which is confusing)
\pset null '(null)'

For reports or sharing results over email, \pset format html combined with \o report.html produces a clean HTML table with no extra effort.

---

The .psqlrc File: Your Personal Configuration

psql reads ~/.psqlrc on startup (Windows: %APPDATA%\postgresql\psqlrc.conf). This is where you put settings you always want active. Here is a solid starting configuration:

-- Always show query timing
\timing on

-- Show NULLs clearly
\pset null '(null)'

-- Expanded mode when output is wider than terminal
\x auto

-- Cleaner border style
\pset border 2

-- Show line numbers in errors
\set VERBOSITY verbose

-- Useful shortcuts
\set version 'SELECT version();'
\set uptime 'SELECT now() - pg_postmaster_start_time() AS uptime;'
\set activity 'SELECT pid, usename, state, query FROM pg_stat_activity;'

Once this is in place, typing :version or :activity at the prompt runs those queries instantly. Building a personal library of these shortcuts over time is one of the things that separates fast database workers from slow ones.

---

Running Scripts and Automating Tasks

psql is fully scriptable, which makes it excellent for deployment pipelines, cron jobs, and data migrations.

# Run a SQL file and exit
psql -d mydb -f migration.sql

# Run a SQL command inline (no interactive prompt)
psql -d mydb -c "SELECT count(*) FROM orders;"

# Suppress all output except errors (good for scripts)
psql -d mydb -q -f migration.sql

# Exit with a non-zero code if an error occurs
psql -d mydb -v ON_ERROR_STOP=1 -f migration.sql

The -v ON_ERROR_STOP=1 flag is critical for scripts. Without it, psql continues running statements after an error and exits 0 (success), which can mask failures silently. Always use it in automated pipelines.

You can also pass variables into scripts from the command line:

psql -d mydb -v table_name=orders -f report.sql

Inside report.sql, reference it as :table_name:

SELECT * FROM :table_name WHERE created_at > now() - interval '7 days';

You can also capture query results as variables using \gset, which stores each column of the result as a named psql variable:

SELECT max(id) AS last_id FROM orders;
\gset
-- :last_id is now available in subsequent statements
SELECT * FROM orders WHERE id > :last_id;

This is particularly useful in migration scripts where later steps depend on values returned by earlier queries.

---

Importing and Exporting Data

The \copy meta-command handles CSV import and export directly from the psql client, without requiring superuser privileges (unlike the server-side COPY command):

-- Export a table to CSV
\copy orders TO '/tmp/orders_backup.csv' WITH (FORMAT CSV, HEADER);

-- Import CSV into a table
\copy orders FROM '/tmp/orders_data.csv' WITH (FORMAT CSV, HEADER);

-- Export query results (not just tables)
\copy (SELECT id, email FROM users WHERE active = true) TO '/tmp/active_users.csv' WITH (FORMAT CSV, HEADER);

The HEADER option writes column names as the first row on export and skips them on import. This is almost always what you want.

---

Inspecting Running Queries and Locks

psql is also a great tool for operational work. These queries go well in your .psqlrc as named shortcuts:

-- See all active queries
SELECT pid, usename, state, query_start, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

-- Find long-running queries (over 5 minutes)
SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > interval '5 minutes';

-- See current locks
SELECT pid, relation::regclass, mode, granted
FROM pg_locks
WHERE relation IS NOT NULL;

-- Kill a query by pid
SELECT pg_cancel_backend(12345);   -- polite cancel
SELECT pg_terminate_backend(12345); -- forceful terminate

For live monitoring, \watch re-runs the previous query on an interval. Write the query, then call \watch to start the loop:

SELECT pid, state, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;
\watch 5

Press Ctrl+C to stop. This is the fastest way to watch query counts, lock activity, or replication lag change in real time.

---

Connection Shortcuts with .pg_service.conf

Managing multiple databases across development, staging, and production environments gets messy fast. The pg_service.conf file lets you define named connection profiles:

# ~/.pg_service.conf (Unix)  or  %APPDATA%\postgresql\pg_service.conf (Windows)

[dev]
host=localhost
port=5432
dbname=myapp_dev
user=dev_user

[prod]
host=db.production.example.com
port=5432
dbname=myapp
user=app_user
sslmode=require

Connect by service name:

psql service=dev
psql service=prod

This keeps credentials out of shell history and scripts, and makes switching environments a one-word command.

---

Tips for Working With Large Result Sets

By default, psql pipes long output through a pager (usually less). This is useful, but you can control the behavior:

-- Disable the pager (output scrolls freely)
\pset pager off

-- Use a different pager
\setenv PAGER 'less -S'     -- -S disables line wrapping, good for wide tables

less -S with horizontal scrolling is ideal for wide tables. Scroll left and right with the arrow keys instead of watching columns wrap onto new lines.

---

Where to Go From Here

psql has a full \? help system built in:

\?              -- all meta-commands
\h SELECT       -- SQL syntax help for any command
\h ALTER TABLE

The official psql documentation is comprehensive and well-maintained. PostgreSQL's mailing lists and the active r/PostgreSQL community are good places to learn advanced tricks from practitioners.

For deeper dives into PostgreSQL internals, Bruce Momjian's presentations and Laurenz Albe's blog at Cybertec are consistently excellent resources.

---

psql is not flashy. It does not have a GUI, it does not surface completions in a floating dropdown, and it has no undo button. What it has is speed, scriptability, and a decades-long track record of reliability. Once you internalize a handful of meta-commands and build out a good .psqlrc, you will find yourself reaching for it over GUI clients more often than not. The terminal is fast, and psql makes Postgres fast to work with.