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 “unrecognized configuraton parameter” 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! First, the SHOW command has its own semantics in PostgreSQL. Second, it’s not part of the SQL standard. And probably it never will be, because the standard committee decided to use a different approach, called the Information Schema. However, even now, information schema support is not at 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 best to show tables in PostgreSQL:
timetable=> SELECT n.nspname AS schema,
t.relname AS table_name,
t.relkind AS type,
t.relowner::regrole AS owner
FROM pg_class AS t
JOIN pg_namespace AS n ON t.relnamespace = n.oid
/* only tables and partitioned tables */
WHERE t.relkind IN ('r', 'p')
/* exclude system schemas */
AND n.nspname !~~ ALL ('{pg_catalog,pg_toast,information_schema,pg_temp%}');
schema | table_name | type | owner
--------------+-------------------+------+-----------
timetable | chain_log | r | scheduler
timetable | dummy_log | r | scheduler
...
public | test | r | pasha
public | location | r | scheduler
(75 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.
The 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 the 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.
In case you were wondering how to compare the content of two PostgreSQL tables, see Hans’ post about creating checksums for tables.