CYBERTEC Logo

SHOW TABLES in PostgreSQL: what's wrong with it?

10.2021 / Category: / Tags: |

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 ?

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.

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

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:

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.

0 0 votes
Article Rating
Subscribe
Notify of
guest
1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Darth Maul
1 year ago

I deleted the original reply as it was wrong.

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram