Everyday Work with psql: Meta-Commands, \x, \timing, \copy, \watch, \gexec, .psqlrc, Schemas and search_path

Version note: The examples in this post were run on PostgreSQL 18.7 and the psql 18 that ships with it (the official postgres:18 Docker image), right after loading the Bookstore schema.

Guess First

Bookstore, the online bookstore used as the shared example across this site's series, lives in the lab we set up in post 02. Two developers, connected with the application role in psql, want to write the top five books to a file. Emre writes COPY, Ayşe writes \copy; both give the same query and the same file path:

COPY (SELECT book_id, title, price FROM books ORDER BY book_id LIMIT 5) TO '/tmp/top5.csv' WITH (FORMAT csv, HEADER);
\copy (SELECT book_id, title, price FROM books ORDER BY book_id LIMIT 5) TO '/tmp/top5.csv' WITH (FORMAT csv, HEADER)

Do both work? If so, where is the file written?

ERROR:  permission denied to COPY to a file
DETAIL:  Only roles with privileges of the "pg_write_server_files" role may COPY to a file.
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.
COPY 5

COPY is a SQL command: the server process writes the file to the server machine's disk and needs a special privilege (pg_write_server_files) for it, which the application role does not have. \copy is a psql meta-command: it takes the same data from the server over stdin/stdout and writes the file on the machine where psql runs, with your privileges. So Emre's command gave a permission error, and Ayşe's wrote a five-row CSV on her own machine. On a managed database (Azure Database for PostgreSQL, Amazon RDS) you cannot reach the server disk at all; there the only way is \copy. In this post we look at using psql efficiently in everyday work: meta-commands, \x and \timing, \copy, \watch, \gexec, .psqlrc, schemas and search_path. This is the last post of the PostgreSQL series for now.

Meta-Commands and the \d Family

psql is the counterpart of sqlcmd on the SQL Server side, but much more. Meta-commands starting with a backslash are run by psql itself without going to the server; the \d family is the most used of them, and each is really a ready-made query over the catalog. In post 02 we looked at \dt and \d+; the whole family lists one kind of object:

\dt
\di books*
\df public.book_available
\dv
\dn

                 List of tables
 Schema |    Name     | Type  |      Owner
--------+-------------+-------+-----------------
 public | authors     | table | bookstore_owner
 public | books       | table | bookstore_owner
 public | customers   | table | bookstore_owner
 public | order_items | table | bookstore_owner
 public | orders      | table | bookstore_owner
 public | payments    | table | bookstore_owner
 public | shipments   | table | bookstore_owner
 public | stock_items | table | bookstore_owner

                 List of indexes
 Schema |    Name    | Type  |      Owner      | Table
--------+------------+-------+-----------------+-------
 public | books_pkey | index | bookstore_owner | books

                            List of functions
 Schema |      Name      | Result data type | Argument data types | Type
--------+----------------+------------------+---------------------+------
 public | book_available | integer          | p_book_id integer   | func

                 List of views
 Schema |    Name     | Type |      Owner
--------+-------------+------+-----------------
 public | v_book_list | view | bookstore_owner

      List of schemas
  Name  |       Owner
--------+-------------------
 public | pg_database_owner

\dt lists tables, \di indexes, \df functions, \dv views, \dn schemas, \du roles; \l databases. Each takes a pattern: \di books* shows only indexes whose name starts with books, \df public.* the functions in the public schema. Adding + increases the detail (\dt+ also prints the table size). \d <object> (without a letter) gives a table's or view's columns, indexes, constraints and dependent objects in one place; \sf book_available prints the definition of a function, \sv v_book_list that of a view. To see which query is behind a meta-command, run psql with -E (or \set ECHO_HIDDEN on in the session); that is the fastest way to learn the catalog while writing your own management queries.

\x, \timing and NULL Display

When a row with many columns does not fit on screen, the table format becomes unreadable. \x shows the output vertically (one column per line), \x auto does this only when a row does not fit. \timing prints the time of each command:

\timing on
\x on
SELECT book_id, title, price, published_year FROM books WHERE book_id = 1;

-[ RECORD 1 ]--+--------
book_id        | 1
title          | Emma #1
price          | 32.77
published_year | 1885

Time: 1.292 ms

The time \timing prints is measured on the client side, including the network round trip; for the real cost of the query on the server you need EXPLAIN (ANALYZE, BUFFERS) from post 07. \pset null '(null)' tells a NULL apart from an empty string: without the setting both look empty, which leads to misreadings especially in outer join results. \pset also changes the output format: \pset format csv, \pset border 2, \a turns off alignment. To change the format after running a query, \g runs the last query again.

Variables, \gset and \gexec

psql has its own variables. They are defined with \set and embedded with :name as text, :'name' as a quoted string, :"name" as a quoted identifier:

\set low_stock 10
SELECT count(*) AS below FROM stock_items WHERE quantity_on_hand < :low_stock;
SELECT count(*) AS total_books, max(book_id) AS max_id FROM books \gset
\echo total books: :total_books max id: :max_id

 below
-------
  2021

total books: 40000 max id: 40000

\gset writes a query's result into variables by column name; we used it in the WAL measurement in post 01. It must return a single row: with several rows it gives more than one row returned for \gset. \gexec is stronger: it runs each row returned by the query as a SQL command. It is the cleanest way to generate a batch of commands from a template. ANALYZE-ing the order tables without writing them out one by one (as bookstore_owner):

SELECT format('ANALYZE %I;', tablename) FROM pg_tables WHERE schemaname = 'public' AND tablename LIKE 'order%' \gexec

ANALYZE order_items;
ANALYZE
ANALYZE orders;
ANALYZE

Run the query without \gexec first, see the commands it would produce, then apply them with \gexec; because the generated SQL runs directly, give the table/column names in the template through format(..., %I) from post 18. The same pattern helps with jobs like "add the same index to every table", "DROP the tables matching a pattern", "generate one row per customer". Seeing the SELECT on its own before writing a multi-row \gset or a dangerous \gexec should be a habit.

Moving Data: \copy and pg_dump

The \copy in "Guess First" writes the file on the machine where psql runs. An export and an import:

\copy (SELECT book_id, title, price FROM books ORDER BY book_id LIMIT 5) TO 'top5.csv' WITH (FORMAT csv, HEADER)
\copy staging_books FROM 'top5.csv' WITH (FORMAT csv, HEADER)

book_id,title,price
1,Emma #1,32.77
2,The Hobbit #2,39.00
3,Middlemarch #3,35.69
4,Frankenstein #4,42.80
5,Foundation #5,15.34

The path is relative: top5.csv is written in the directory where you started psql. \copy ... TO exports the result of a query or a table, \copy ... FROM imports a CSV into a table; both go row by row over stdin/stdout, so they are slower than server-side COPY on large data but work everywhere. The fastest way to bulk-load on the application side was the binary COPY in post 11; \copy is its manual, one-off counterpart. The most common mistake is mixing the two and looking for a file you wrote with server-side COPY on your own machine; the file is on the server's (in this lab, the container's) disk.

Bringing a Table into a Development Environment

To reproduce a bug locally you usually need a few of production's tables, not all of it. The pg_dump/pg_restore in post 14 is detailed on the operations side; in everyday use, taking a single table is one line. Let us first look at a single table's schema, then move two tables into a dev database:

pg_dump -U bookstore_owner -d bookstore -t public.stock_items --schema-only
pg_dump -U bookstore_owner -d bookstore -t public.authors -t public.books --no-owner --no-privileges | psql -X -q -U bookstore_owner -d bookstore_dev

CREATE TABLE public.stock_items (
    book_id integer NOT NULL,
    quantity_on_hand integer NOT NULL,
    quantity_reserved integer NOT NULL,
    updated_at timestamp with time zone NOT NULL,
    CONSTRAINT ck_stock_items_quantity CHECK (((quantity_on_hand >= 0) AND (quantity_reserved >= 0)))
);
ALTER TABLE public.stock_items OWNER TO bookstore_owner;
ALTER TABLE ONLY public.stock_items
    ADD CONSTRAINT stock_items_pkey PRIMARY KEY (book_id);
ALTER TABLE ONLY public.stock_items
    ADD CONSTRAINT fk_stock_items_books FOREIGN KEY (book_id) REFERENCES public.books(book_id);
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE public.stock_items TO bookstore_app;

-t selects a table (or tables matching a pattern); several -t can be given. --schema-only dumps only the structure, --data-only only the data. In the second command we fed pg_dump's output straight into a psql connected to another database: books and authors, with 40,000 and 5,000 rows, moved into bookstore_dev. The thing to watch is foreign keys: if you take stock_items on its own and the books it references is not in dev, the restore fails on the constraint; take the related tables together, or loosen the order with --disable-triggers. We shortened the \restrict line that psql 18 adds to the dump output; it is a new mechanism that lets the dump be loaded safely without commands being injected into it.

\watch: Watching a Query

Instead of re-running the same query by hand while waiting for an order's status to change or a queue to drain, there is \watch. We ran the status distribution of the latest orders once a second, twice:

SELECT status, count(*) FROM orders WHERE order_id > 999990 GROUP BY status ORDER BY status \watch i=1 c=2

Sun 15 Nov 2026 12:58:54 AM UTC (every 1s)

 status  | count
---------+-------
 Pending |    10

Sun 15 Nov 2026 12:58:55 AM UTC (every 1s)

 status  | count
---------+-------
 Pending |    10

\watch re-runs the last query at a fixed interval; i (interval) is the interval in seconds, c (count) how many times it runs. Without a count it keeps going until Ctrl+C. In another session you can call reserve_stock from post 22 on an order and watch the Pending count drop live on this screen. The time in the output is the server's time and depends on the TimeZone setting (post 19). \watch is not a monitoring tool; it is not meant for a dashboard left open for minutes, but it is handy for watching a job finish or a migration progress.

Running Scripts and .psqlrc

psql is also used as a tool that runs SQL files. \i file.sql runs a script from within the session, \o out.txt redirects the output of the following queries to a file (closed with \o), \e opens the last query in an editor and runs it when you save. The most important setting when running scripts is ON_ERROR_STOP:

psql -X -v ON_ERROR_STOP=on -U bookstore_app -d bookstore
SELECT 1/0;
SELECT 'never reached';

ERROR:  division by zero

Without ON_ERROR_STOP, psql keeps running the rest of the script after an error; in a migration or data-load script this means a half-applied change that goes unnoticed. With it on, psql stops at the first error and exits with a nonzero exit code, so the shell script running it sees the error. Use -v ON_ERROR_STOP=on on the command line, or \set ON_ERROR_STOP on at the top of the script. That was also the line at the top of our schema files. To run a single command without an interactive session there is -c "SQL", and to run a file and exit there is -f file.sql; both are the form we used with docker exec ... psql from post 02 on.

Defaults with .psqlrc

So you do not type the same settings on every connection, psql reads a .psqlrc file at startup. The file we prepared for the lab in post 02:

\set QUIET 1
\pset null '(null)'
\set COMP_KEYWORD_CASE upper
\set HISTCONTROL ignoredups
\x auto
\timing on
\set PROMPT1 '%n@%/%R%# '
\unset QUIET

The file is read from ~/.psqlrc in the user's home directory; you can point to another file with the PSQLRC environment variable, or start psql with -X to read none at all. We ran all the examples in this post and all the series' measurements with -X so that nobody's .psqlrc would change the result; do the same when you take your own measurements. In PROMPT1, %n shows the role, %/ the database, %# whether the role is a superuser (# or >); seeing which one you are on from the prompt while connected to both the lab and production at once reduces the risk of mistakes. Keeping a different .psqlrc for the two environments and coloring the prompt red in production is a common habit.

Schemas and search_path

If you do not write a table fully as public.books in a query, PostgreSQL searches for it in the schemas of search_path in order. Keeping the same name in two schemas is a silent trap for that reason:

CREATE SCHEMA staging;
CREATE TABLE staging.books (book_id int, title text, note text);
INSERT INTO staging.books VALUES (1, 'Emma #1 (draft)', 'import test');
SHOW search_path;
SELECT title FROM books WHERE book_id = 1;
SET search_path = staging, public;
SELECT title FROM books WHERE book_id = 1;
SELECT title FROM public.books WHERE book_id = 1;

   search_path
-----------------
 "$user", public

  title
---------
 Emma #1

      title
-----------------
 Emma #1 (draft)

  title
---------
 Emma #1

The default search_path is "$user", public: PostgreSQL first looks at the schema with the same name as the user (if any), then at public. With staging put first, books now showed the draft table; the same query, the same name, a different result. The fully qualified public.books always brings the right table. SET search_path affects only the session; to make it permanent use ALTER ROLE ... SET search_path or ALTER DATABASE .... In post 22 we saw why pinning search_path in SECURITY DEFINER functions is a security matter; the trap here is its everyday form.

One more detail connects to post 14. Since PostgreSQL 15, the privilege to create objects in the public schema is no longer open to everyone (PUBLIC):

SELECT nspname, pg_catalog.has_schema_privilege('bookstore_app', nspname, 'CREATE') AS app_can_create
FROM pg_namespace WHERE nspname = 'public';

 nspname | app_can_create
---------+----------------
 public  | f

The application role cannot create a table in public; this is consistent with the behavior we saw with \dt in post 02, and it is the secure schema usage recommended for CVE-2018-1058 becoming the default. When you upgrade an old cluster or restore a dump, the old (open) privileges are preserved; new databases come with it closed.

In Production

Two habits help when connecting to a production database with psql. First, connect with a read-only role (like pg_read_all_data from post 14); writing by accident becomes impossible. Second, wrap a command in a transaction before running it: type BEGIN;, run the command, see the result, and COMMIT; if it is right or ROLLBACK; if it is not. Running psql with -1 (single transaction) wraps a whole script in one transaction.

Queries with long output go through a pager (usually less) in psql. In a script, or when piping the output to a file or a command, \pset pager off or the PAGER environment variable turns it off; otherwise the command looks stuck while it waits for the pager.

psql keeps command history in ~/.psql_history. If you typed a command with a password or another sensitive value, it stays in the history; \set HISTFILE /dev/null in .psqlrc or a leading space on the command (with HISTCONTROL=ignorespace) prevents it. Never write a password in a command or a connection string; the ~/.pgpass file or environment variables exist for that (post 14).

Common Pitfalls

Mixing \copy and COPY: server-side COPY looks for the file you wrote on the server's disk and fails for unprivileged roles. A file job on the client side is always \copy.

Trusting search_path and querying a table in the wrong schema: if a staging or an old schema with the same name exists, wrong data comes back silently. Use fully qualified names in scripts and functions, or set search_path explicitly at the start.

Running a script without ON_ERROR_STOP: after the first error the remaining commands also run and a half-applied change goes unnoticed. Always turn it on in migration and data-load scripts.

Thinking the pager is stuck on long output, or not seeing which database you are connected to in production from the prompt. Use a meaningful PROMPT1 in .psqlrc and \pset pager off when needed.

A Small Experiment

★☆☆ Use \gexec to produce a report of the row count and total size for every table in the public schema: first write a template of the form SELECT 'SELECT ''' || tablename || ''' ...' from pg_tables, see its output without \gexec, then run it. Use pg_total_relation_size from post 08.

★★☆ Write your own .psqlrc: \x auto, \timing on, \pset null, and show the database name in the prompt. Then put a query that returns the top five books into a variable with \set as :top5 and run it by typing :top5 in the session (mv_top_books from post 22 or the bestseller query from post 07).

★★★ Watch an order's life cycle with \watch: in one session open a query that shows the status and reservations of a Pending order you pick with \watch i=2, and in another session call reserve_stock from post 22 on that order. On the first screen watch the transition to PaymentPending and the increase of quantity_reserved in stock_items live.

With this post we reach the last of the PostgreSQL series for now: starting from the architecture (post 01) through the lab, data types, JSONB, indexes, EXPLAIN, MVCC and VACUUM, transactions, full-text search, Npgsql and EF Core, extensions, security and operations, and then in this last block the string, date and aggregate functions, views and materialized views, functions and procedures, triggers and psql, we moved Bookstore onto PostgreSQL end to end. One post remains: what PostgreSQL 19 brings. 19 is in the release candidate stage right now; once it is GA we will add the "What's New in PostgreSQL 19" post to the series.