How to show tables in Postgres

PostgreSQL, commonly known as Postgres, is a powerful open-source relational database management system used by developers worldwide in various applications. In Postgres, tables are the fundamental building blocks for storing and organizing data. Knowing how to view these tables is important for anyone working with databases. In this Answer, we’ll explore a few simple techniques for displaying tables in PostgreSQL.

Note: Before we display the tables, the \x command is worth mentioning. The \x command is a handy toggle that switches between expanded and unexpanded display modes. When enabled, it formats query results in a more human-readable, vertically aligned format for better readability. Use this command before executing other commands with outputs to view them in an expanded format.

Using the \dt command

The simplest way to display tables in Postgres is through the meta-command \dt. This built-in command lists all the tables present in the currently connected database. Here’s an example:

We'll see a neat list of tables along with their schemas. This is a quick and convenient way to get an overview of the tables in our database.

We can get more information about the tables using the \dt+ command. This will return the size and description of the tables as well.

Using SQL queries

We can also use SQL queries to retrieve information about the tables. The SQL query method allows us to fine-tune our search and explore tables based on specific criteria. In PostgreSQL, metadata about tables is stored in the information_schema — a special schema that houses information about the database itself. The information_schema.tables view, in particular, contains table details, such as their names, schemas, and more.

Let's break down the SQL query used in this technique:

• SELECT table_name: This part of the query specifies the information we want to retrieve, which, in this case, is the names of the tables.

• FROM information_schema.tables: Here, we specify the source of our information, the information_schema.tables view, where metadata about tables is stored.

• WHERE table_schema = 'public';: This is a condition that filters the results. In this example, we’re selecting tables only from the 'public' schema. We can modify this condition to match the schema we are interested in, allowing us to focus on specific parts of the database.

Note: Try to remove the last condition to see tables that are normally used by Postgres itself.

Using the pg_catalog schema

The pg_catalog schema also contains system catalog tables that store metadata about the database. While using information_schema.tables is more portable across different database systems, querying the pg_catalog schema is specific to PostgreSQL. This query follows a similar structure to the one we discussed earlier.

Using pg_catalog can be advantageous if we want to access PostgreSQL-specific details or if we need more low-level information. However, relying on system catalog tables may make our queries less portable across different database systems.

Note: We can use SELECT * to get all of the columns present in this schema.

Conclusion

In summary, showcasing tables in PostgreSQL relies on two main methods: utilizing built-in meta-commands like \dt or employing SQL queries to retrieve information from either the information_schema or pg_catalog schemas. These techniques offer different levels of granularity and portability, allowing users to efficiently navigate and analyze PostgreSQL databases according to their specific needs.