Once you understand the basic psql meta commands, the next step is exploring the deeper inspection tools available inside PostgreSQL. These commands help you move beyond simply listing tables and start analyzing indexes, constraints, functions, access privileges, schemas, roles, data types, extensions, and many other internal database objects.
The real strength of psql is speed and visibility. Instead of searching through system catalog tables or writing repeated SQL queries, you can use a single backslash command to inspect the exact object you need. This makes database administration, debugging, development, and learning PostgreSQL much faster.
In Part 2, we continue the journey through informational meta commands and focus on commands that give more detailed insight into how PostgreSQL is structured internally.
Understanding Meta Commands
1. \dg - List Roles
Purpose
Shows users and roles.
Usage
\dg
Example Output
List of roles
Role name | Attributes
----------------------------+------------------------------------------------------------
anon | Cannot login
audit_test |
authenticated | Cannot login
authenticator | No inheritance
cybrosys | Superuser
2. \di - List Indexes
Purpose
Shows indexes.
Usage
\di
Example Output
List of indexes
Schema | Name | Type | Owner | Table
--------+------------------------+-------+----------+-------------
public | bg_test_pkey | index | postgres | bg_test
public | customers_pkey | index | postgres | customers
public | docs_pkey | index | postgres | docs
public | documents_pkey | index | postgres | documents
public | idx_order_item_total | index | postgres | order_item
public | idx_orders_combo | index | postgres | orders
3. \dl - List Large Objects
Purpose
Shows stored binary large objects.
Usage
\dl
Example Output
Large objects
ID | Owner | Description
-------+----------+-------------------
24576 | postgres | Project PDF File
4. \dL - List Languages
Purpose
Shows procedural languages.
Usage
dL
Example Output
List of languages
Name | Owner | Trusted | Description
---------+----------+---------+------------------------------
plpgsql | postgres | t | PL/pgSQL procedural language
(1 row)
5. \dm - List Materialized Views
Purpose
Shows materialized views.
Usage
\dm
Example Output
List of materialized views
Schema | Name | Type | Owner
--------+----------------+-------------------+----------
public | employees_data | materialized view | postgres
(1 row)
6. \dn - List Schemas
Purpose
Shows schemas.
Usage
List of schemas
Name | Owner
------------------+-------------------
analytics | postgres
auth | supabase_admin
dbms_job | postgres
dbms_lock | postgres
7. \do - List Operators
Purpose
Shows operators like +, -, <, =.
Usage
\do
Example Output
List of operators
Schema | Name | Left arg type | Right arg type | Result type | Description
--------+------+---------------+----------------+-------------+-------------
public | < | ean13 | ean13 | boolean |
public | < | ean13 | isbn | boolean |
public | < | ean13 | isbn13 | boolean |
public | < | ean13 | ismn | boolean |
public | < | ean13 | ismn13 | boolean |
public | < | ean13 | issn | boolean |
8. \dO - List Collations
Purpose
Shows sorting and locale collations.
Usage
\dO
Example Output
List of collations
Schema | Name | Provider | Collate | Ctype | Locale | ICU Rules | Deterministic?
--------+------------+----------+---------+-------+--------+-----------+----------------
public | my_english | icu | | | en-US | | yes
(1 row)
9. \dp - Access Privileges
Purpose
Shows object permissions.
Usage
\dp
Example Output
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+---------------------+-------------------+---------------------------------+-------------------+----------
public | bg_test | table | | |
public | bg_test_id_seq | sequence | | |
public | customers | table | postgres=arwdDxtm/postgres +| |
| | | anon=arwdDxtm/postgres +| |
| | | authenticated=arwdDxtm/postgres+| |
| | | service_role=arwdDxtm/postgres | |
public | customers_id_seq | sequence | postgres=rwU/postgres +| |
| | | anon=rwU/postgres +| |
| | | authenticated=rwU/postgres +| |
| | | service_role=rwU/postgres | |
10. \dP, \dPi, \dPt - Partitioned Relations
Purpose
Shows partitioned tables and indexes.
Usage
\dP
Example Output
List of partitioned relations
Schema | Name | Owner | Type | Table
--------+----------------+----------+-------------------+-------
public | sales | postgres | partitioned table |
public | idx_sales_date | postgres | partitioned index | sales
(2 rows)
Usage
\dPi
Example Output
List of partitioned indexes
Schema | Name | Owner | Table
--------+----------------+----------+-------
public | idx_sales_date | postgres | sales
(1 row)
Usage
\dPt
Example Output
List of partitioned tables
Schema | Name | Owner
--------+-------+----------
public | sales | postgres
(1 row)
11. \drds - Per-Database Role Settings
Purpose
Shows settings applied to specific roles.
Usage
\drds
Example Output
List of settings
Role | Database | Settings
------------------------+----------+-----------------------------------------------
anon | | statement_timeout=3s
authenticated | | statement_timeout=8s
authenticator | | session_preload_libraries=safeupdate +
| | statement_timeout=8s +
| | lock_timeout=8s
postgres | | search_path="\$user", public, extensions
supabase_admin | | search_path="$user", public, auth, extensions+
| | log_statement=none
supabase_auth_admin | | search_path=auth +
| | idle_in_transaction_session_timeout=60000 +
| | log_statement=none
supabase_storage_admin | | search_path=storage +
| | log_statement=none
(7 rows)
12. \drg - Role Grants
Purpose
Shows role memberships.
Usage
\drg
Example Output
List of role grants
Role name | Member of | Options | Grantor
-------------------------+------------------+--------------+----------
authenticator | anon | SET | postgres
authenticator | authenticated | SET | postgres
authenticator | service_role | SET | postgres
supabase_read_only_user | pg_read_all_data | INHERIT, SET | postgres
(4 rows)
13. \dRp - Publications
Purpose
Shows logical replication publications.
Usage
dRp
Example Output
List of publications
Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
-------------------+----------+------------+---------+---------+---------+-----------+-------------------+----------
supabase_realtime | postgres | f | t | t | t | t | none | f
(1 row)
14. \dRs - Subscriptions
Purpose
Shows logical replication subscriptions.
Usage
\dRs
Example Output
List of subscriptions
Name | Owner | Enabled | Publication
-------------+-----------+---------+-------------
sub_products| postgres | yes | {pub_products}
15. \ds - List Sequences
Purpose
Shows sequences.
Usage
\ds
Example Output
List of sequences
Schema | Name | Type | Owner
--------+--------------------+----------+----------
public | bg_test_id_seq | sequence | postgres
public | customers_id_seq | sequence | postgres
public | docs_id_seq | sequence | postgres
public | documents_id_seq | sequence | postgres
public | log_table_id_seq | sequence | postgres
public | order_item_id_seq | sequence | postgres
16. \dt - List Tables
Purpose
Shows only tables.
Usage
\dt
Example Output
List of tables
Schema | Name | Type | Owner
--------+-------------+-------+----------
public | bg_test | table | postgres
public | customers | table | postgres
public | data | table | postgres
public | demo | table | postgres
public | docs | table | postgres
public | documents | table | postgres
public | emp | table | postgres
public | employees | table | postgres
17. \du - List Roles
Purpose
Same as \dg.
Usage
\du
Example Output
List of roles
Role name | Attributes
----------------------------+------------------------------------------------------------
anon | Cannot login
audit_test |
authenticated | Cannot login
authenticator | No inheritance
cybrosys | Superuser
dashboard_user | Create role, Create DB, Cannot login, Replication
18. \dT - List Data Types
Purpose
Shows built-in and custom data types.
Usage
\dT
Example Output
List of data types
Schema | Name | Description
--------+---------+--------------------------------------------------
public | address |
public | ean13 | International European Article Number (EAN13)
public | isbn | International Standard Book Number (ISBN)
public | isbn13 | International Standard Book Number 13 (ISBN13)
public | ismn | International Standard Music Number (ISMN)
public | ismn13 | International Standard Music Number 13 (ISMN13)
public | issn | International Standard Serial Number (ISSN)
public | issn13 | International Standard Serial Number 13 (ISSN13)
public | upc | Universal Product Code (UPC)
(9 rows)
19. \dv - List Views
Purpose
Shows views.
Usage
\dv
Example Output
List of views
Schema | Name | Type | Owner
--------+---------------------+------+----------
public | pg_buffercache | view | postgres
public | pg_buffercache_numa | view | postgres
(2 rows)
20. \dx - List Extensions
Purpose
Shows installed extensions.
Usage
\dx
Example Output
List of installed extensions
Name | Version | Default version | Schema | Description
----------------+---------+-----------------+------------+----------------------------------------------------------
isn | 1.3 | 1.3 | public | data types for international product numbering standards
pg_buffercache | 1.6 | 1.6 | public | examine the shared buffer cache
pglogical | 2.4.6 | 2.4.6 | pglogical | PostgreSQL Logical Replication
plpgsql | 1.0 | 1.0 | pg_catalog | PL/pgSQL procedural language
postgres_fdw | 1.2 | 1.2 | public | foreign-data wrapper for remote PostgreSQL servers
(5 rows)
21. \dX - Extended Statistics
Purpose
Shows advanced planner statistics.
Usage
\dX
Example Output
List of extended statistics
Schema | Name | Definition | Ndistinct | Dependencies | MCV
--------+-----------+---------------------------------+-----------+--------------+---------
public | emp_stats | department, city FROM employees | defined | defined | defined
(1 row)
22. \dy - Event Triggers
Purpose
Shows event triggers for DDL actions.
Usage
\dy
Example Output
List of event triggers
Name | Event | Owner | Enabled | Function | Tags
------------+-----------------+----------+---------+----------+------
ddl_logger | ddl_command_end | postgres | enabled | log_ddl |
(1 row)
In conclusion, Part 2 of the psql Informational Meta Commands blog explores advanced commands that provide deeper insight into PostgreSQL’s internal structure and functionality. By understanding features such as partitioning, extended statistics, event triggers, subscriptions, and large objects, users can efficiently access critical information needed for database administration, performance optimization, and troubleshooting.
Instead of searching through catalog tables or relying on external tools, you can inspect advanced database structures directly from the terminal in seconds. This saves time and helps you understand your system more clearly.
Mastering these commands makes psql more than a query tool.It becomes a complete PostgreSQL inspection environment for both developers and database administrators.