Show Tables in PostgreSQL – TecAdmin

PostgreSQL is one of the world’s most advanced and widely used open-source relational database systems. It offers a broad range of features and capabilities, and when working with it, one of the most fundamental tasks is listing or showing the tables in a database. This article will guide you through various methods of showing tables in PostgreSQL.

1. Connect to PostgreSQL

Before you can show tables, ensure that you’re connected to your PostgreSQL server. You can connect to it using the PostgreSQL command line client, psql, by entering:

psql -U username -d myDatabase 

2. List tables using \dt command

Once you’re connected to your desired database using psql, the simplest way to list all the tables in the current database is by using the \dt command:

\dt 

This command will show you a list of tables, including their name, type, owner, schema, and more.

Output:
           List of relations
 Schema |    Name    | Type  |  Owner
--------+------------+-------+----------
 public | results    | table | tecadmin
 public | roles      | table | tecadmin
 public | employee   | table | tecadmin
 public | users      | table | tecadmin
(4 rows)

3. Querying the pg_catalog Schema

If you want to get a list of tables programmatically or with more flexibility in terms of filtering, you can query the pg_tables catalog table. Here’s a basic SQL query to list all tables in the current database:

SELECT tablename FROM pg_tables WHERE schemaname = 'public';

4. Filtering the Table List

If you’re interested in filtering the list of tables based on a particular schema, you can adjust the query accordingly:

SELECT tablename FROM pg_tables WHERE schemaname = 'your_schema_name';

5. Listing Views

Sometimes, you might also want to list views in addition to tables. Use \dv command to list all views in the current database:

\dv 

Or you can query the pg_views catalog table:

SELECT viewname FROM pg_views WHERE schemaname = 'public';

6. Exploring Additional Metadata

To explore more information about the tables such as columns, data types, and more:

\d table_name 

This will display detailed information about the specified table.

7. Using GUI tools

If you’re more comfortable with a graphical interface, there are numerous GUI tools available like PgAdmin, DBeaver, and DataGrip that allow you to connect to your PostgreSQL instance and visually see all the tables and other objects in your database. These tools often come with additional features for managing and querying your data.

Conclusion

Whether you are a beginner or an experienced user, being able to quickly view and navigate the tables in a PostgreSQL database is crucial. Using psql commands or SQL queries, you can efficiently and effectively explore the structure of your database and retrieve necessary metadata. As you become more familiar with PostgreSQL, you’ll find that these commands become second nature and are invaluable in your daily interactions with the system.