In this article, I will answer the questions: why isn’t there a  SHOW TABLES command in PostgreSQL, when will SHOW TABLES in PostgreSQL be available, and how do I list tables with native PostgreSQL methods?

Why isn’t there a SHOW TABLES command in PostgreSQL?

People who come from MySQL are always asking the same question: why doesn’t the command SHOW TABLES work in PostgreSQL ?

postgres=> SHOW TABLES;
ERROR:  unrecognized configuration parameter "tables"

First of all, there is a SHOW command in PostgreSQL, but it’s responsible for returning the value of a run-time parameter. Now you know what the error message means: there is no run-time parameter with the name “tables”.

By the way, we have a comprehensive article on how to set PostgreSQL parameters.

But how can it be that the same command means opposite things in the two most popular open-source RDBMS? The answer is quite obvious: the SHOW command is not part of the SQL ISO or ANSI standard. That allows anybody to use it as an extension to standard SQL.

When will SHOW TABLES be available in PostgreSQL?

I would say never! Firstly, the SHOW command has its own semantics in PostgreSQL. Secondly, it’s not part of the SQL standard. And probably it will never be, because the standard committee decided to use a different approach, called Information Schema. However, even now, information schema support is not on the appropriate level in most databases.

How do I show tables in PostgreSQL?

Answers to this question may be found in a lot of places. I will just repeat them here.

The PostgreSQL way

If you’re using the psql command-line utility, then try the \dt built-in command.

Mnemonic rule: \dt = Describe Table.

-- list visible tables from search_path
timetable=> \dt
              List of relations
 Schema |      Name       | Type  |   Owner
--------+-----------------+-------+-----------
 public | bar             | table | scheduler
 public | foo             | table | scheduler
 public | history_session | table | scheduler
 public | location        | table | scheduler
 public | migrations      | table | scheduler
 public | test            | table | pasha
(6 rows)

-- list tables from "timetable" schema
timetable=> \dt timetable.*
               List of relations
  Schema   |      Name      | Type  |   Owner
-----------+----------------+-------+-----------
 timetable | active_session | table | scheduler
 timetable | chain          | table | scheduler
 timetable | chain_log      | table | scheduler
 timetable | dummy_log      | table | scheduler
 timetable | execution_log  | table | scheduler
 timetable | log            | table | scheduler
 timetable | migration      | table | scheduler
 timetable | parameter      | table | scheduler
 timetable | run_status     | table | scheduler
 timetable | task           | table | scheduler
(10 rows)

-- describe table "bar"
timetable=> \dt bar
         List of relations
 Schema | Name | Type  |   Owner
--------+------+-------+-----------
 public | bar  | table | scheduler
(1 row)

-- describe table "bar" with details
timetable=> \dt+ bar
                            List of relations
 Schema | Name | Type  |   Owner   | Persistence |  Size   | Description
--------+------+-------+-----------+-------------+---------+-------------
 public | bar  | table | scheduler | permanent   | 0 bytes |
(1 row)

If you’re using any other utility than psql, then these SQLs are probably the simplest to show tables in PostgreSQL:

timetable=> SELECT oid :: regclass AS table_name FROM pg_class WHERE relkind = 'r';
                table_name
--------------------------------------------
 timetable.chain_log
 pg_statistic
 pg_type
 timetable.dummy_log
...
 test
 location
(82 rows)

timetable=> SELECT * FROM pg_catalog.pg_tables;
     schemaname     |        tablename        | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
--------------------+-------------------------+------------+------------+------------+----------+-------------+-------------
 timetable          | chain_log               | scheduler  |            | t          | f        | f           | f
 pg_catalog         | pg_statistic            | pasha      |            | t          | f        | f           | f
 pg_catalog         | pg_type                 | pasha      |            | t          | f        | f           | f
 timetable          | dummy_log               | scheduler  |            | t          | f        | f           | f
...
(82 rows)

But as you can see, they will list all the tables in the database, so you probably will need filtering anyway.

ANSI standard way

As I said, there is a SQL-standard way to show tables in PostgreSQL by querying information_schema:

timetable=> SELECT * FROM information_schema.tables WHERE table_type = 'BASE TABLE';
 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
---------------+--------------+------------------+------------+------------------------------+----------------------+---------------------------+--------------------------+------------------------+--------------------+----------+---------------
 timetable     | timetable    | chain_log        | BASE TABLE |                              |                      |                           |                          |                        | YES                | NO       |
 timetable     | pg_catalog   | pg_type          | BASE TABLE |                              |                      |                           |                          |                        | YES                | NO       |
 timetable     | timetable    | dummy_log        | BASE TABLE |                              |                      |                           |                          |                        | YES                | NO       |
 timetable     | pg_catalog   | pg_foreign_table | BASE TABLE |                              |                      |                           |                          |                        | YES                | NO       |
 timetable     | timetable    | migration        | BASE TABLE |                              |                      |                           |                          |                        | YES                | NO       |
...
(75 rows)

Conclusion

There is no universal way of doing common things in all databases. Even though the SQL standard declares Information Schema to be the proper way, not every database manufacturer implemented its support on a decent level. If you’re using the native command-line psql tool, you will have built-in commands; otherwise, you need to query system tables and/or views. If you’re using a GUI application, then you probably don’t have this problem. Every known GUI application with PostgreSQL support allows you to list, filter, and find any objects.