Powering Up psql: An Essential Guide to PostgreSQL Meta-Commands

When you work with PostgreSQL from a terminal, psql meta-commands save you from writing catalog queries for everyday checks. These backslash commands help you list tables, describe objects, switch databases, format output, and get help without leaving your session.

This guide covers the psql commands you will use most often and shows where they fit in real database work.

Understanding psql Meta-Commands

Meta-commands in psql are directives that are processed by psql itself, rather than being sent to the PostgreSQL server for execution. They are easily identifiable by their leading backslash (\). This distinction is crucial because meta-commands have their own syntax and behavior, separate from standard SQL.

For instance, while you might write SELECT * FROM information_schema.tables; to list all tables, a psql meta-command can achieve the same result with a simple \dt.

Essential Meta-Commands for Database Inspection

Inspecting your database structure is a frequent task. psql provides several meta-commands to make this process effortless.

Listing Tables (\dt)

The \dt command is one of the most frequently used meta-commands. It lists all tables in the current database, along with their schema and owner.

• Example:

  \dt
  

• Output:

                         List of relations
  Schema |    Name    | Type  |  Owner   
  --------+------------+-------+----------
  public | accounts   | table | postgres 
  public | products   | table | postgres 
  sales  | orders     | table | dbadmin  
  (3 rows)
  

• Variations:

  • \dt+: Provides more detailed information, including table size and description.
  • \dt schema.*: Lists tables only in a specific schema.

Describing Tables and Columns (\d)

The \d command is invaluable for understanding the structure of a specific table, including its columns, data types, constraints, and indexes.

• Example: To describe the products table:

  \d products
  

• Output:

                                 Table "public.products"
   Column   |         Type          | Collation | Nullable | Default 
  ----------+-----------------------+-----------+----------+---------
   product_id | integer             |           | not null | 
   name       | character varying(100)|           |          | 
   price      | numeric(10, 2)        |           |          | 
  Indexes:
      "products_pkey" PRIMARY KEY, btree (product_id)
  

• Variations:

  • \d+ table_name: Provides even more detailed information, including storage parameters and comments.
  • \dn: Lists all schemas.
  • \df: Lists all functions.
  • \dv: Lists all views.

Inspecting Views (\dv)

Similar to tables, you can list and describe views.

• Example (List views):

  \dv
  

• Example (Describe a view):

  \dv my_view
  

Listing Schemas (\dn)

To see all the schemas present in your database, use the \dn command.

• Example:

  \dn
  

Meta-Commands for Query Management and Output Control

psql offers powerful options for controlling how query results are displayed and for managing your query history.

Query Timing (\timing)

The \timing command toggles the display of elapsed time for each SQL query. It is useful for quick comparisons while tuning a query, though you should use EXPLAIN (ANALYZE, BUFFERS) when you need a real execution plan.

• Example (Turn timing on):

  \timing on
  

  Now, every query you run will be followed by its execution time.

• Example (Turn timing off):

  \timing off
  

Output Formatting (\a, \H, \t)

• \a: Toggles between aligned (table) and unaligned (CSV-like) output. Unaligned output is often easier to parse programmatically.

  \a
  SELECT * FROM products;
  

• \H: Switches the output format to HTML.

  \H
  SELECT * FROM products;
  

• \t: Toggles the display of column headers and row count footers. This is useful when you want output that is easier to pipe into another command.

  \t
  SELECT name, price FROM products;
  

Paging (\pset pager)

If your query results are long, psql may use a pager such as less, depending on your environment and pager settings. Control that behavior with \pset pager.

• Example (Use the pager for long output):

  \pset pager on
  

• Example (Disable pager):

  \pset pager off
  

Administrative Meta-Commands

Beyond inspection and output control, psql provides commands for common administrative tasks.

Listing Databases (\l)

To see all available databases on the connected PostgreSQL server, use \l.

• Example:

  \l
  

Connecting to a Different Database (\c)

If you are already connected to a psql session and want to switch to another database, use the \c command.

• Example: To connect to the mydatabase database:

  \c mydatabase
  

Running \? for Help

When in doubt, psql provides its own help system. The \? command lists all available meta-commands, and \! allows you to run shell commands.

• Example (List all meta-commands):

  \?
  

• Example (Run a shell command, e.g., ls):

  \! ls -l
  

Best Practices and Tips

• Use shell aliases for full psql commands: Shell aliases cannot run \dt inside an already-open psql session, but they can shorten common startup commands. For example, alias pprod='psql "postgresql://[email protected]/prod"'.
• Combine with SQL: Meta-commands are not mutually exclusive with SQL. You can use them to quickly inspect schema before writing a SELECT statement or use \timing to compare query runtimes.
• Understand the Scope: Remember that meta-commands operate within the context of your current psql session and connection. Commands like \c will change the active database for that session.
• Use \? Generously: The built-in help is comprehensive and an excellent resource when you need to recall a command or discover new ones.

Takeaway

Start with \dt, \d, \dn, \l, \c, \timing, \pset pager, and \?. Those few commands cover most day-to-day inspection work and make psql feel much less like a raw SQL prompt.