When using PostgreSQL’s psql command line tool, we can use the \dt command to get a list of tables. This meta-command can display all tables, or just tables that match certain patterns. Below is an overview of how \dt works, its options, and examples of its various uses.

Basic Usage

The \dt command lists all tables in the current database, along with details about them. By default, it returns the schema name, table name, object type, and owner.

Example:

\dt

Example output:

               List of relations
 Schema |        Name        | Type  |  Owner   
--------+--------------------+-------+----------
 public | actors             | table | postgres
 public | array_test         | table | barney
...

Filtering by Schema

We can restrict the list of tables to a specific schema by using the schema name prefixed by a dot (.).

Example:

\dt sales.*

This command lists all tables in the sales schema only.

Without specifying the schema, the pattern matches only tables that are visible in the current schema search path.

Filtering by Pattern

We can provide a pattern to match against tables names by using the \dt pattern syntax.

Example:

\dt product*

That will list all tables that start with product.

Another example:

\dt *actors*

That will list all tables that have actors in their name:

            List of relations
 Schema |     Name     | Type  |  Owner   
--------+--------------+-------+----------
 public | actors       | table | postgres
 public | movie_actors | table | postgres

Detailed Table Information

To get more detailed information about the table, we can add a plus sign (+), so that it becomes \dt+. This displays additional details like its persistence status (permanent, temporary, or unlogged), its access method, size on disk, etc.

Example:

\dt+ sales.orders

Example output:

                                     List of relations
 Schema |  Name  | Type  | Owner  | Persistence | Access method |    Size    | Description 
--------+--------+-------+--------+-------------+---------------+------------+-------------
 sales  | orders | table | barney | permanent   | heap          | 8192 bytes | 

We can also omit the t, so that it becomes \d+ to get even more details, including columns, NOT NULL constraints, DEFAULT constraints, all indexes on the table, etc:

\d+ sales.orders

Example output:

                                                                        Table "sales.orders"
    Column    |         Type          | Collation | Nullable |                    Default                     | Storage  | Compression | Stats target | Description 
--------------+-----------------------+-----------+----------+------------------------------------------------+----------+-------------+--------------+-------------
 order_id     | integer               |           | not null | nextval('sales.orders_order_id_seq'::regclass) | plain    |             |              | 
 customer_id  | integer               |           | not null |                                                | plain    |             |              | 
 order_date   | date                  |           | not null |                                                | plain    |             |              | 
 status       | character varying(20) |           | not null |                                                | extended |             |              | 
 total_amount | numeric(10,2)         |           | not null |                                                | main     |             |              | 
Indexes:
    "orders_pkey" PRIMARY KEY, btree (order_id)
    "orders_customer_id_idx" btree (customer_id)
    "orders_status_idx" btree (status) WHERE status::text = 'active'::text
Access method: heap

Practical Example

Here’s an example that creates the schema, tables and indexes so that you can run various \dt command variations and see the results.

First, run the following script to create a new schema and put a table in it, along with some indexes:

-- Create the sales schema
CREATE SCHEMA IF NOT EXISTS sales;

-- Create the orders table
CREATE TABLE sales.orders (
    order_id SERIAL PRIMARY KEY,             -- Primary key index (orders_pkey)
    customer_id INT NOT NULL,                -- Will have a foreign key index
    order_date DATE NOT NULL,
    status VARCHAR(20) NOT NULL,             -- This will be used in a partial index
    total_amount NUMERIC(10, 2) NOT NULL
);

-- Create a foreign key index on customer_id (orders_customer_id_idx)
CREATE INDEX orders_customer_id_idx
ON sales.orders (customer_id);

-- Create a partial index on status column (orders_status_idx)
-- This index is specifically for 'active' orders.
CREATE INDEX orders_status_idx
ON sales.orders (status)
WHERE status = 'active';

This script creates a sales schema and puts a table called orders in it with the following indexes:

• A primary key index: orders_pkey
• A foreign key index: orders_customer_id_idx
• A partial index: orders_status_idx (on status = 'active')

Here are some examples of using \dt with different arguments to return information about the table:

1. List only tables that are visible in the current schema search path:

   \dt

2. List all tables in the sales schema:

   \dt sales.*

3. List tables that start with the letter o:

   \dt sales.o*

4. Get detailed information on the orders table:

   \d+ sales.orders

By using these different variations, you can easily explore and manage tables in your PostgreSQL database. It’s also possible to use regular expressions as a pattern. See the PostgreSQL documentation for more detail on the patterns that can be used with the \dt command.