psql Command Line

psql is the official command-line client for PostgreSQL. It is a powerful interactive terminal that lets you run queries, inspect the database schema, import/export data, and manage the server — all without leaving the terminal. Mastering psql dramatically speeds up your PostgreSQL workflow.

Connecting to PostgreSQL

The basic connection syntax:

psql -U username -d database -h host -p port

# Connect to local postgres superuser
psql -U postgres

# Connect to a specific database
psql -U postgres -d myapp

# Connect to a remote server
psql -U myuser -d production -h db.example.com -p 5432

# Connection string format (URL)
psql "postgresql://myuser:mypassword@db.example.com:5432/production"

# Using environment variables (recommended for scripts)
export PGHOST=db.example.com
export PGUSER=myuser
export PGDATABASE=production
psql  # uses env vars automatically

The psql Prompt

When connected, you see a prompt like:

myapp=#

The # means you are a superuser. Regular users see =>. The database name before the prompt tells you which database you are connected to.

If a query spans multiple lines, the prompt changes:

myapp=# SELECT
myapp-#   name,    -- still collecting input
myapp-#   salary
myapp-# FROM employees;

Press q to exit a result pager, and \q to quit psql entirely.

Essential Meta-Commands

Meta-commands start with a backslash \ and are processed by psql itself (not sent to the server).

Database and Connection Info

\l          -- list all databases
\l+         -- list databases with size info
\c dbname   -- connect (switch) to a different database
\conninfo   -- show current connection details

myapp=# \conninfo
You are connected to database "myapp" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

Schema Inspection

\dt              -- list tables in current schema
\dt *.*          -- list all tables in all schemas
\dt public.*     -- list tables in public schema

\d tablename     -- describe a table (columns, types, indexes, constraints)
\d+ tablename    -- more detail (storage, comments)

\dv              -- list views
\dm              -- list materialized views
\df              -- list functions
\df+ funcname    -- details about a specific function

\dn              -- list schemas
\du              -- list users (roles)
\dp tablename    -- show privileges on a table

Example: \d employees

myapp=# \d employees
                        Table "public.employees"
   Column    |            Type             | Nullable |      Default
-------------+-----------------------------+----------+--------------------
 id          | integer                     | not null | nextval('emp_id_seq')
 name        | text                        | not null |
 dept        | text                        |          |
 salary      | numeric(10,2)               |          |
 hire_date   | date                        |          |
 created_at  | timestamp with time zone    |          | now()
Indexes:
    "employees_pkey" PRIMARY KEY, btree (id)
    "employees_name_idx" btree (name)

Running Queries and Scripts

\i /path/to/file.sql    -- execute a SQL file
\ir relative/path.sql   -- execute relative to current script

\e                      -- open current query buffer in $EDITOR
\ef funcname            -- edit a function in $EDITOR
\ev viewname            -- edit a view in $EDITOR

# Run a SQL file from the command line (no interactive session)
psql -U postgres -d myapp -f schema.sql

# Run a single command and exit
psql -U postgres -d myapp -c "SELECT COUNT(*) FROM employees;"

Output Formatting

\x          -- toggle expanded display (vertical layout for wide rows)
\x auto     -- auto-switch to expanded when rows are wide
\t          -- toggle tuples-only (hides column headers and row count)
\a          -- toggle aligned/unaligned output
\pset border 2   -- add borders to output table
\H          -- toggle HTML output mode
\o file.txt -- send output to a file
\o          -- stop sending to file (back to terminal)

Normal output:

 id | name  | salary
----+-------+--------
  1 | Alice | 60000
  2 | Bob   | 85000

After \x (expanded display):

-[ RECORD 1 ]------
id     | 1
name   | Alice
salary | 60000
-[ RECORD 2 ]------
id     | 2
name   | Bob
salary | 85000

Expanded display is invaluable for tables with many columns.

Timing

\timing         -- toggle query timing
\timing on      -- always show timing

myapp=# \timing on
Timing is on.
myapp=# SELECT COUNT(*) FROM orders;
 count
-------
 92451
(1 row)

Time: 12.345 ms

Copying Data

-- Export a table to CSV
\copy employees TO '/tmp/employees.csv' CSV HEADER;

-- Import from CSV
\copy employees FROM '/tmp/employees.csv' CSV HEADER;

-- Export a query result
\copy (SELECT id, name FROM employees WHERE dept = 'IT') TO '/tmp/it_team.csv' CSV HEADER;

Variables and Scripting

-- Set a variable
\set myvar 'IT'

-- Use it in a query (note: no space between : and the variable name)
SELECT * FROM employees WHERE dept = :'myvar';

-- Set a numeric variable
\set limit_rows 10
SELECT * FROM orders LIMIT :limit_rows;

Help

\?          -- list all psql meta-commands
\h          -- list all SQL commands
\h SELECT   -- show syntax help for SELECT

Setting Up ~/.psqlrc

The ~/.psqlrc file runs automatically every time you start psql. Use it to set your preferred defaults:

-- ~/.psqlrc

-- Always show timing
\timing on

-- Auto-expand wide results
\x auto

-- Set a nice prompt: show user@host:db
\set PROMPT1 '%n@%m:%/%R%# '

-- Show null values explicitly
\pset null '(null)'

-- Don't show welcome banner
\set QUIET 1

-- Useful abbreviations
\set version 'SELECT version();'
\set activity 'SELECT pid, state, wait_event_type, wait_event, query FROM pg_stat_activity;'

After saving, run \i ~/.psqlrc or simply restart psql to apply the settings.

Searching History

psql keeps a history of commands (stored in ~/.psql_history). Use:

• Up/Down arrows — cycle through history
• Ctrl+R — reverse search through history (like bash)
• Ctrl+A — go to beginning of line
• Ctrl+E — go to end of line

Quick Reference Card