Here are a couple of options for getting a list of tables in a database in PostgreSQL.

The first option is a psql command, the second involves querying an information schema view.

The \dt Command

When using psql, the quickest and easiest way to get a list of tables with the \dt command.

Example:

\dt

Example result:

          List of relations
+--------+---------+-------+--------+
| Schema |  Name   | Type  | Owner  |
+--------+---------+-------+--------+
| public | albums  | table | barney |
| public | artists | table | barney |
| public | genres  | table | barney |
+--------+---------+-------+--------+

We can also add a plus sign (+) to output more information about each table:

\dt+

Example result:

                       List of relations
+--------+---------+-------+--------+------------+-------------+
| Schema |  Name   | Type  | Owner  |    Size    | Description |
+--------+---------+-------+--------+------------+-------------+
| public | albums  | table | barney | 8192 bytes |             |
| public | artists | table | barney | 8192 bytes |             |
| public | genres  | table | barney | 8192 bytes |             |
+--------+---------+-------+--------+------------+-------------+

It’s the t part that outputs tables. We can use \dtv to output both tables and views if required.

Actually, we can follow \d with E, i, m, s, t, and/or v. These stand for foreign table, index, materialised view, sequence, table, and view, respectively. You can specify any or all of these letters, in any order, to obtain a listing of objects of these types.

Using \d alone is the equivalent of \dtvmsE (i.e. it outputs all of the aforementioned relation types).

We can also append a pattern to return just those tables that match the pattern.

Example:

\dt a*

Example result:

          List of relations
+--------+---------+-------+--------+
| Schema |  Name   | Type  | Owner  |
+--------+---------+-------+--------+
| public | albums  | table | barney |
| public | artists | table | barney |
+--------+---------+-------+--------+

In this example, I returned just those tables that start with the letter “a”.

The tables Information Schema View

Another way to return all tables in a database is to query the information_schema.tables view.

Example:

SELECT * FROM information_schema.tables
WHERE table_type = 'BASE TABLE' 
AND table_schema = 'public';

Example result:

+---------------+--------------+------------+------------+------------------------------+----------------------+---------------------------+--------------------------+------------------------+--------------------+----------+---------------+
| table_catalog | table_schema | table_name | table_type | self_referencing_column_name | reference_generation | user_defined_type_catalog | user_defined_type_schema | user_defined_type_name | is_insertable_into | is_typed | commit_action |
+---------------+--------------+------------+------------+------------------------------+----------------------+---------------------------+--------------------------+------------------------+--------------------+----------+---------------+
| music         | public       | artists    | BASE TABLE |                              |                      |                           |                          |                        | YES                | NO       |               |
| music         | public       | albums     | BASE TABLE |                              |                      |                           |                          |                        | YES                | NO       |               |
| music         | public       | genres     | BASE TABLE |                              |                      |                           |                          |                        | YES                | NO       |               |
+---------------+--------------+------------+------------+------------------------------+----------------------+---------------------------+--------------------------+------------------------+--------------------+----------+---------------+

Here I limited the results to just the public schema, and base tables. We could easily remove those restrictions by removing the WHERE clause (or modifying it).

We can also limit the columns returned and/or limit the output to just those tables that match a pattern:

SELECT
    table_catalog,
    table_schema,
    table_name,
    table_type,
    is_insertable_into
FROM information_schema.tables
WHERE table_type = 'BASE TABLE' 
AND table_schema = 'public'
AND table_name LIKE 'a%';

Example result:

+---------------+--------------+------------+------------+--------------------+
| table_catalog | table_schema | table_name | table_type | is_insertable_into |
+---------------+--------------+------------+------------+--------------------+
| music         | public       | albums     | BASE TABLE | YES                |
| music         | public       | artists    | BASE TABLE | YES                |
+---------------+--------------+------------+------------+--------------------+

The tables view is one of many information schema views. The information schema consists of a set of views that contain information about the objects defined in the current database.

The information schema is defined in the SQL standard and is therefore expected to be portable between compliant RDBMSs. In other words, the above query can be used on other RDBMSs, such as MariaDB, MySQL, SQL Server, etc.