PostgreSQL's psql CLI tool is a favorite among database administrators and developers alike, but most users only utilize a small portion of its real power. While commands like \dt and \q are well-known, psql also includes a range of lesser-known meta-commands that offer incredible productivity boosts, in-depth visibility into your database, and even automation.
In this blog, we’ll walk through 15 underused but highly effective psql meta-commands, explaining not just what they do, but why and how you should use them.
1. \conninfo: View Connection Details Instantly
What it does:
Displays information about your current connection, such as the database name, user, host, port, and SSL status.
Why it’s useful:
Quickly confirm which database you're connected to, especially useful in environments with multiple PostgreSQL instances or remote connections.
Example:
\conninfo
Typical Output:
You are connected to database "sales_db" as user "admin" via socket in "/tmp" at port "5432".
2. \dx+: Get Full Extension Insights
What it does:
Displays all installed PostgreSQL extensions with detailed metadata, including version, schema, and description.
Why it’s useful:
Essential for validating that the correct extensions (like postgis, pg_stat_statements, uuid-ossp, etc.) are installed and correctly configured.
Example:
\dx+
Use Case:
While troubleshooting performance issues or setting up features like time-series data or encryption, quickly verify that required extensions are present.
3. \dT+: List All Data Types with Metadata
What it does:
Lists all user-defined and built-in data types, including domains and composite types, along with additional information like internal type structure.
Why it’s useful:
Critical when you're working with custom domains, reusable types, or inspecting how types are used across schemas.
Example:
\dT+
Insight:
This command is especially helpful when reverse-engineering complex schemas or ensuring type compatibility in integrations.
4. \sf function_name: Show Function Source Code
What it does:
Outputs the complete definition of a given SQL or procedural function.
Why it’s useful:
Instantly review or debug functions without querying pg_proc or browsing your SQL files.
Example:
\sf pg_stat_statements
Note:
Supports functions in PL/pgSQL, PL/Python, and other procedural languages.
5. \errverbose: Decode the Last Error
What it does:
Provides a full breakdown of the most recent error, including context, SQLSTATE code, and internal diagnostics.
Why it’s useful:
Helps you understand cryptic or vague error messages, especially when working with nested functions or triggers.
Example :
\errverbose
6. \gexec: Execute Dynamic SQL from Query Output
What it does:
Takes the output of the last query and runs it as a new SQL command.
Why it’s useful:
Dynamically execute generated SQL commands like DROP, ALTER, or even mass inserts/updates.
Example:
SELECT 'DROP TABLE IF EXISTS ' || tablename || ';' FROM pg_tables WHERE schemaname = 'public';
\gexec
Real-world use:
Clean up test tables or apply batch changes with one smart command.
7. \watch: Auto-Refresh Queries
What it does:
Reruns your last query every few seconds and shows live results.
Why it’s useful:
Monitor changing data in real time — for example, see active connections, queue status, or job progress without leaving your terminal.
Example:
SELECT count(*) FROM orders;
\watch 5
Use Case:
Live dashboard-like monitoring during a migration, import, or high-traffic event.
8. \dtS+: Explore System Tables
What it does:
Lists PostgreSQL’s internal system tables along with extended information.
Why it’s useful:
Helps you understand how PostgreSQL manages catalogs, dependencies, and configuration — useful when debugging internal mechanics or system schema issues.
Example:
\dtS+
What you’ll see:
Tables from pg_catalog, such as pg_authid, pg_class, and their storage details.
9. \x: Toggle Expanded Output Mode
What it does:
Switches between horizontal and vertical display formats for query output.
Why it’s useful:
Makes wide or long tables easier to read — especially helpful for viewing rows with many columns.
Example:
\x
SELECT * FROM employees WHERE id = 1;
Tip:
Once enabled, all future queries are shown in expanded mode until you toggle it off with \x again.
10. \ef [function]: Edit Functions in Your Editor
What it does:
Opens your default text editor (like Vim or Nano) to modify a stored function directly.
Why it’s useful:
Quickly edit and update stored procedures without writing full CREATE OR REPLACE FUNCTION blocks manually.
Example:
\ef pg_stat_statements
Outcome:
After saving and exiting, the function is automatically updated in PostgreSQL.
11. \d+ table_name: Inspect Table Metadata
What it does:
Displays extended metadata about a table, including column details, constraints, indexes, and storage settings.
Why it’s useful:
A one-stop overview to understand how a table is structured and how efficiently it’s stored.
Example:
\d+ customers
Helpful for:
Database audits, refactoring, and identifying bottlenecks or misconfigured columns.
12. \do+: Dive Into Custom Operators
What it does:
Lists PostgreSQL operators and provides metadata like operand types, operator function, and return types.
Why it’s useful:
Helps when working with custom operators (e.g., <@, @>, ~) especially in advanced apps using full-text search or GIS.
Example:
\do+
Use Case:
You can inspect how specific operations are defined and used internally.
13. \z: Display Access Privileges
What it does:
Shows GRANTs and REVOKEs on tables, schemas, and other objects.
Why it’s useful:
Simplifies permission audits, which would otherwise require querying information_schema.role_table_grants.
Example:
\z public.*
Output:
Lists all privileges assigned per user, like SELECT, INSERT, etc.
14. \gdesc: Preview Query Result Structure
What it does:
Displays the expected output structure (column names and types) of a query without actually running it.
Why it’s useful:
Useful when testing or building queries on large datasets, where running the full query would be expensive.
Example:
SELECT id, name FROM users;
\gdesc
Benefit:
You see:
id | integer
name | text
—without fetching any actual data.
15. \pset: Customize Output Formats
What it does:
Sets output formatting options — including expanded output, borders, and CSV/HTML formats.
Why it’s useful:
Fine-tune your CLI output for scripts, exports, or better readability.
Example:
\pset format csv
SELECT * FROM products;
Result:
Output is:
"id","name","price"
1,"Phone",699.99
2,"Laptop",1199.00
Tip:
Use \pset format unaligned for easy copy-paste into spreadsheets or scripts.
conclusion
While PostgreSQL is renowned for its robustness and feature set, its full potential often remains hidden behind the simplicity of the psql interface. By mastering these 15 advanced meta-commands, you can go beyond basic queries and gain powerful insights, streamline complex tasks, and interact with your database more efficiently.
Whether you're troubleshooting an issue, optimizing performance, or simply exploring your schema, these commands give you the precision and control you need—right from your terminal. Ever wondered what happens behind the scenes when you create a new database in PostgreSQL? This article, titled "How to Understand the Internal Process of Database Creation in PostgreSQL," delves into the foundational steps, template databases (like template1 and template0), and system catalogs that work together to bring your new PostgreSQL database to life.