Skip to content
Tutorials Center
My learning

Topics

Lesson 4 of 90 ยท Getting Started with PostgreSQL

psql: The Command-Line Client

Learn psql, PostgreSQL's command-line client: meta-commands like \d+ and \x, running scripts with -f and variables, \copy, \watch and a .psqlrc setup.

psql is PostgreSQL's official terminal client, and it is far more than a place to type SQL. It has its own set of commands for exploring a database, a small scripting language with variables and conditions, import and export, and enough configuration to make daily work pleasant. Most PostgreSQL users spend a large part of their career inside it, and the more you know about it, the faster you will debug, explore and automate.

A server and psql itself cannot run in your browser, so the psql sessions in this lesson are read-only. To follow along, install a server as shown in the previous lesson. The live examples here are SQLite stand-ins: the closest queries to \dt and \d, and a small Python program that reproduces how psql substitutes variables into SQL.

Two kinds of input

At the psql prompt you type two different things:

  • SQL statements, which end with a semicolon and are sent to the server.
  • Meta-commands, which begin with a backslash and are handled by psql itself. They need no semicolon and run to the end of the line.
TEXT
shop=# SELECT count(*) FROM books;
 count
-------
    10
(1 row)

shop=# \dt

The prompt shows the database (shop) and =# when you are a superuser or => for an ordinary role; -# means psql is waiting for the rest of a multi-line statement. Press the up arrow to recall history, and use Tab to complete keywords, table names and even file names.

The meta-commands you will use daily

Find your way around

\l and \c List databases, connect to another one.
\dt List the tables. \dt+ adds sizes.
\d name Columns, indexes, constraints and triggers.
\du List the roles.

\dn lists schemas, \df functions, \di indexes, \dv views.

1 / 2

Here is a typical exploration, with the kind of output you would see:

TEXT
shop=# \dt
         List of relations
 Schema |   Name    | Type  |  Owner
--------+-----------+-------+----------
 public | authors   | table | app_user
 public | books     | table | app_user
 public | customers | table | app_user
 public | orders    | table | app_user
(4 rows)

shop=# \d books
                                  Table "public.books"
  Column   |     Type     | Collation | Nullable |              Default
-----------+--------------+-----------+----------+-----------------------------------
 id        | integer      |           | not null | nextval('books_id_seq'::regclass)
 title     | text         |           | not null |
 author_id | integer      |           | not null |
 genre     | text         |           | not null |
 published | integer      |           | not null |
 price     | numeric(6,2) |           | not null |
 stock     | integer      |           | not null | 0
Indexes:
    "books_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "books_author_id_fkey" FOREIGN KEY (author_id) REFERENCES authors(id)

\d+ books adds storage details and column descriptions, \d * describes everything, and \dt public.* filters by pattern. Many commands accept patterns with * and ?, and a + suffix means "more detail". Use \? for the full list of meta-commands and \h CREATE INDEX to see the syntax of any SQL command without leaving the terminal.

For wide rows, switch to expanded display with \x, which prints each column on its own line:

TEXT
shop=# \x
Expanded display is on.
shop=# SELECT * FROM books WHERE id = 1;
-[ RECORD 1 ]------------
id        | 1
title     | The Salt Road
author_id | 1
genre     | Fiction
published | 2015
price     | 14.99
stock     | 12

\x auto is the best of both: psql chooses expanded output only when the normal table would be wider than your terminal.

The SQLite counterpart of these commands is querying the catalog. These queries run live and show the idea:

SQL The SQLite version of \dt and \d ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

PostgreSQL's \dt and \d are conveniences built on top of its system catalogs (pg_class, pg_attribute and friends). You can see the SQL that a meta-command runs by starting psql with -E, a good way to learn the catalogs; the monitoring lesson returns to them.

Time, editing and watching

TEXT
shop=# \timing
Timing is on.
shop=# SELECT count(*) FROM orders;
 count
-------
    10
(1 row)

Time: 0.612 ms

\timing is the quick way to compare two versions of a query. \e opens the current query buffer in the editor named by the EDITOR environment variable, so long statements are easy to fix, and \ef my_function edits a function's definition. \watch 2 repeats the last query every two seconds, which turns psql into a poor man's monitor:

SQL
-- PostgreSQL
SELECT count(*) AS active FROM pg_stat_activity WHERE state = 'active';
\watch 2

\g runs the query buffer (like ;), and \gx runs it once in expanded mode without changing your \x setting.

Moving data with \copy

PostgreSQL has a COPY command that reads and writes files, but it runs on the server, so it needs file access on that machine and special privileges. psql's \copy does the same job on your machine, streaming through the client:

SQL
-- PostgreSQL
\copy books TO 'books.csv' WITH (FORMAT csv, HEADER)
\copy books FROM 'new_books.csv' WITH (FORMAT csv, HEADER)
\copy (SELECT title, price FROM books WHERE stock = 0) TO 'sold_out.csv' CSV HEADER

This is the everyday way to export a report or load a CSV. Bulk loading gets its own lesson, Bulk Loading and COPY.

Scripting with psql

Put SQL in a file and run it:

Terminal
psql -h localhost -U app_user -d shop -f setup.sql
psql -d shop -c "SELECT count(*) FROM books"

Two options make scripts safe. By default psql keeps going after an error, which can leave a half-applied script. Add -v ON_ERROR_STOP=1 to stop at the first error, and -1 (--single-transaction) to wrap the whole file in one transaction, so a failure undoes everything:

Terminal
psql -d shop -v ON_ERROR_STOP=1 -1 -f migration.sql

For machine-readable output, --csv prints CSV, and -A -t ("unaligned, tuples only") prints bare values that a shell script can capture:

Terminal
count=$(psql -d shop -At -c "SELECT count(*) FROM orders")
echo "orders: $count"

Use -X to skip your .psqlrc when you need predictable behavior in automation.

Variables

psql has its own variables, separate from SQL. Set them with \set or on the command line with -v, and use them with a colon:

Terminal
psql -d shop -v genre=Poetry -f report.sql
SQL
-- report.sql (PostgreSQL)
SELECT title, price FROM books WHERE genre = :'genre' ORDER BY price;

There are three forms, and the quotes matter: :name pastes the raw text, :'name' pastes it as a safely quoted string literal, and :"name" pastes it as a quoted identifier. This stand-in reproduces the substitution so you can watch it happen, including a value with an apostrophe:

Python How psql substitutes :variables

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Notice that the apostrophes in the tricky value are doubled, so the value cannot break out of the string. That is why :'name' is the safe form for text. psql can also capture a query result into a variable with \gset, and \if, \elif and \endif give scripts conditions, but plain substitution covers most needs. For untrusted input, use real parameters in your application code; see SQL Injection and Safe Queries.

Making psql yours: .psqlrc

psql reads ~/.psqlrc at startup (on Windows, %APPDATA%\postgresql\psqlrc.conf). A good starting file:

TEXT
\set QUIET 1
\pset null '(null)'
\x auto
\timing on
\set COMP_KEYWORD_CASE upper
\set HISTSIZE 5000
\set ON_ERROR_ROLLBACK interactive
\set QUIET 0

\pset null makes NULL visible instead of an empty cell, \x auto picks expanded output when needed, COMP_KEYWORD_CASE completes keywords in upper case, and ON_ERROR_ROLLBACK interactive keeps a transaction usable after a typo by wrapping each statement in a hidden savepoint. The QUIET lines silence the startup noise.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the difference between SQL statements and meta-commands in psql?

    psql interprets backslash commands locally, which is why they need no semicolon. Plain SQL is sent to the server.
  2. 2. Which flags make a script stop at the first error and undo everything if it fails?

    ON_ERROR_STOP=1 aborts at the first error, and -1 (--single-transaction) wraps the file in one transaction so nothing is half applied.
  3. 3. In a psql script, how should you insert a text variable named genre into a WHERE clause safely?

    :'genre' quotes the value as a string literal, doubling any apostrophes. :genre pastes raw text and :"genre" quotes it as an identifier.
  4. 4. Why use \copy instead of COPY to load a CSV from your laptop?

    COPY runs on the server and needs file access and privileges there. \copy streams the file from the client machine.

Summary

  • psql accepts SQL (ending with ;) and backslash meta-commands that it handles itself.
  • Explore with \l, \c, \dt, \d table, \d+, \dn, \du, \df; read help with \? and \h.
  • \x auto for wide rows, \timing for durations, \e to edit, \watch 2 to repeat a query.
  • \copy moves CSV data between your machine and a table; server-side COPY needs file access on the server.
  • In scripts use -f, -c, -v name=value, -v ON_ERROR_STOP=1 -1 and -At or --csv for output; use :'name' for safely quoted values.
  • Put your preferences in .psqlrc.

Next: Clusters, Databases and Schemas, how PostgreSQL organizes everything you create.