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?
Table of Contents
People who come from MySQL are always asking the same question: why doesn't the command SHOW TABLES work in PostgreSQL ?
|
1 2 |
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.
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.
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.
Answers to this question may be found in a lot of places. I will just repeat them here.
If you're using the psql command-line utility, then try the dt built-in command.
Mnemonic rule: dt = Describe Table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
-- 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) |
psql, then these SQLs are probably the best to show tables in PostgreSQL:|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
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.
As I said, there is a SQL-standard way to show tables in PostgreSQL by querying information_schema:
|
1 2 3 4 5 6 7 8 9 10 |
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) |
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.
+43 (0) 2622 93022-0
office@cybertec.at
I deleted the original reply as it was wrong.