Mastering psql: Advanced Features Every PostgreSQL User Should Know

Ever since I started using psql almost every day, I’ve come to appreciate the depth and versatility of PostgreSQL and its, not quite user friendly, interactive terminal, psql. While most engineers are familiar with PostgreSQL, you might not have heard of or used psql. Therefore, I want to show you a treasure trove of advanced, cool, and powerful features that can elevate your workflow.

The basics

psql is included in the PostgresQL package, so you probably already have it installed if you’re reading this blog. You can try running psql in the terminal and you should see the version of the Postgres server and psql come up on you screen.

PostgresQL psql commands

psql keeps it simple, all the commands start with a \, including the command for help, \?

PostgresQL psql list databases

To list all your databases, use:

\l

PostgresQL psql connect to database

You might be asking yourself, how do I connect to a PostgresQL database using psql, well it’s simple:

\c database_name

SQL

You can execute SQL commands, not just psql commands. This opens up a whole new world. Maybe you haven’t created a database for the project you’re working on yet. You can just create it using SQL.

CREATE DATABASE database_name;

Maybe you see an old database you’re not using anymore, simply delete it using:

DROP DATABASE database_name;

Customizing the psql environment

As humans, we prefer working in aesthetically pleasing environments. Well, psql doesn’t offer that but we can at least try making it more appealing. A personalized psql environment can also streamline your workflow and provide context at a glance.

Setting variables

Declaring variables will simplify repetitive commands by injecting dynamic values into queries with \set

\set local_db 'project_name_local_database'

Now you can use this variable by prefixing it with :.

SELECT * FROM :local_db.users LIMIT 10;
      -- or
    \c :local_db

This substitutes project_name_production_database into the query, saving you time and reducing errors.

Customizing prompts

Prompts in psql are the text displayed on the command line to indicate that the tool is ready for your input. By default, the prompt shows the current database name, but you can customize it to display additional context, such as your username or database, making it easier to keep track of your session, especially when juggling multiple databases or roles.

psql supports three prompt types, which you can set using \set:

• PROMPT1: The primary prompt, shown when psql is expecting a new command.

• PROMPT2: The continuation prompt, displayed when a command spans multiple lines (e.g., during a multi-line query).

• PROMPT3: Used in specific contexts, such as when editing the query buffer with \e, though it’s less commonly customized.

Here’s an example configuration:

\set PROMPT1 '%n@%/ %# '
      \set PROMPT2 '%# '
    \set PROMPT3 ''

Let’s break down what these do:

• PROMPT1: '%n@%/ %# ': This uses special sequences that psql replaces with runtime values:

  • %n: The database username (e.g., john).

  • @: A literal @ character for readability.

  • %/: The current database name (e.g., local_db).

  • %#: The prompt character, > for regular users or # for superusers—followed by a space.

  Result: If you’re user john connected to local_db as a super user, the prompt becomes john@local_db #. This gives you immediate insight into your identity and context, which is invaluable in multi-user or multi-database environments.

• PROMPT2: '%# ': For multi-line inputs, this shows only the prompt character (> or #) and a space. For example, if you’re typing a long CREATE TABLE statement, each continuation line starts with > , keeping it minimal and uncluttered.

• PROMPT3: '': Setting this to an empty string suppresses the prompt in rare cases like query buffer editing. Most users won’t notice this, as PROMPT3 is infrequently used, but it ensures no extra text appears in those scenarios.

Customizing prompts enhances usability by embedding session details directly into your workflow, reducing the need to repeatedly check your connection status.

Customizing output formatting

Beyond prompts, you can tweak how psql displays query results to improve readability and fit your terminal’s capabilities. Here are some useful formatting commands:

\x off
      \pset linestyle unicode
      \pset format wrapped
    \pset border 2

• \x off: Controls the display mode for result sets. By default, psql uses a standard table format, but \x toggles to an expanded mode where each row is shown vertically—great for wide tables. Setting \x off ensures the output stays in the standard, compact table layout, which is often more practical for typical queries.

• \pset linestyle unicode: The \pset command adjusts output formatting options, and linestyle defines how table borders are drawn. Setting it to unicode uses Unicode characters (e.g., ┌, ─, ┐) instead of ASCII (+, -, +), creating a cleaner, more modern look in terminals that support Unicode. This builds on your existing mention of \pset linestyle and ties it into a broader customization theme.

• \pset format wrapped: This sets the output format to wrapped, meaning that if a result set is too wide for your terminal, psql wraps the content to fit within the screen width rather than truncating it or forcing horizontal scrolling. It’s a lifesaver for viewing wide tables without losing data, though line breaks may occur within cells.

• \pset border 2: This sets the amount of borders when printing outputs. The \pset border accepts values 0 (no border), 1 (internal dividing lines), and 2 (table frame).

These adjustments make the output more visually appealing and practical, especially when dealing with complex queries or varying terminal sizes.

Making Customizations Permanent with .psqlrc

To ensure your psql personalizations persist across sessions, create a .psqlrc file in your home repository and list your commands inside. Upon startup, psql will automatically execute all commands listed in this file, applying your customizations.

Advanced querying techniques

Now, let’s get into the meaty stuff. PostgreSQL’s querying prowess shines through psql, especially with features that senior engineers can wield for complex data analysis. Let’s explore some advanced techniques, including powerful window functions, JSON querying, and full-text search.

Common Table Expressions (CTEs): Use WITH clauses to break down intricate queries into readable, modular pieces. For example:

WITH active_users AS (
      SELECT user_id, COUNT(*) AS logins
      FROM login_events
      WHERE event_time > NOW() - INTERVAL '30 days'
      GROUP BY user_id
      )
      SELECT u.user_id, u.name, a.logins
      FROM users u
    JOIN active_users a ON u.user_id = a.user_id;

Transaction management

Ever need to test something risky without committing? Transactions in psql are your friend:

BEGIN;
      INSERT INTO users (name) VALUES ('Test');
      SAVEPOINT my_save;
      DELETE FROM users WHERE name = 'Test';
      -- Oops, didn’t mean that
      ROLLBACK TO SAVEPOINT my_save;
    COMMIT;

Savepoints let you backtrack without losing everything. I’ve used this in scripts to safely test updates.

Custom functions and triggers

Want to automate something? Create a function:

CREATE FUNCTION log_changes() RETURNS TRIGGER AS $$
      BEGIN
      INSERT INTO audit_log (table_name, action) VALUES (TG_TABLE_NAME, TG_OP);
      RETURN NEW;
      END;
    $$ LANGUAGE plpgsql;

Then hook it up with a trigger:

CREATE TRIGGER user_audit
      AFTER INSERT OR UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION log_changes();

This logs every change to users. I built something like this for a compliance project—worked like a charm.

Performance tuning

Slow queries are the worst, and psql has tools to hunt them down.

EXPLAIN ANALYZE

Postgres provides an analysis of queries with EXPLAIN.

EXPLAIN ANALYZE SELECT * FROM orders WHERE order_date > '2025-01-01';

Which will produce an output like:

Aggregate  (cost=11.25..11.26 rows=1 width=8) (actual time=0.046..0.047 rows=1 loops=1)
      Buffers: shared hit=1
      ->  Seq Scan on task  (cost=0.00..11.00 rows=100 width=0) (actual time=0.028..0.030 rows=1 loops=1)
      Buffers: shared hit=1
      Planning Time: 0.219 ms
      Execution Time: 0.102 ms
    (6 rows)

• Aggregate: The top node, which computes the count(*). It estimates a cost of 11.25 to 11.26 (start to total) and expects 1 row. In reality, it took 0.046 to 0.047 ms and returned 1 row, executed once (loops=1).

• Seq scan: The child node, scanning table task. It estimates a cost of 0.00 to 11.00 and 100 rows. However, the actual execution shows it took 0.028 to 0.030 ms, returned 1 row, and was executed once (loops=1). This indicates that the table has only 1 row, while the optimizer estimated 100 rows, suggesting that the statistics might be outdated.

• Costs and timing:

  • Estimated costs: These are in arbitrary units (e.g., 1 = cost of a sequential page read). The Seq Scan has a startup cost of 0.00 and total cost of 11.00, while the Aggregate adds a tiny bit more to 11.25 to 11.26.

  • Actual times: Measured in milliseconds, showing the Seq Scan took 0.028 to 0.030 ms, and the Aggregate took 0.046 to 0.047 ms, with a total execution time of 0.102 ms.

• Buffers:

  • Buffers: shared hit=1 indicates that 1 block was found in the cache, meaning the data was already in memory, which is efficient for this small table.

• Footer:

  • Planning Time: 0.219 ms: How long the optimizer took to plan.

  • Execution Time: 0.102 ms: Total runtime, including execution of all nodes.

  Note: The discrepancy between estimated rows (100) and actual rows (1) suggests that the table statistics might be outdated. Running ANALYZE task; could update the statistics and improve the optimizer's estimates for future queries.

Extensions via psql

PostgreSQL extensions are one of the best things about PostgreSQL. Extensions add extra functionality to your database by modifying and enhancing the execution of certain processes, helping you bypass limitations—such as maintaining good performance when your tables grow up to billions of rows—without the hassle of switching to a whole new database.

For instance, you can install pgcrypto for encryption:

CREATE EXTENSION pgcrypto;
    SELECT encrypt('secret data', 'my_key', 'aes');

You can find more extensions on the official Postgres documentation.

Conclusion

psql is far more than a basic CLI—it’s a versatile, feature-rich companion to PostgreSQL that rewards deep exploration. From advanced querying techniques like window functions, JSON querying, and full-text search to performance tuning, administration, scripting, and integration, these capabilities cater to senior engineers seeking mastery.

By leveraging these lesser-known features, you can optimize your database interactions, automate complex tasks, and tackle challenges with finesse. Dive into psql’s depths, and you’ll uncover tools that not only impress but also empower you to push PostgreSQL to its limits.