How to display tables in PostgreSQL

Introduction

PostgreSQL is one of the most advanced and widely used open source relational database systems in the world. It offers a wide range of features and capabilities, and when working with it, one of the core tasks is to list or display tables in a database. This article will walk you through various methods of displaying tables in PostgreSQL.

Connect to PostgreSQL

Before you can display tables, make sure you are connected to your PostgreSQL server. You can connect to it using the PostgreSQL command-line client, psql, by entering:

psql -U username -d myDatabase

List tables using the \dt command

Once connected to the desired database using psql, the easiest way to list all tables in the current database is to use the \dt command:

\dt

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

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

Query 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'

Filter the list of tables

If you are interested in filtering the table list based on a particular schema, you can modify the query accordingly:

SELECT tablename FROM pg_tables WHERE schemaname = 'your_schema_name';

List views

Sometimes, you may also want to list views in addition to tables. Use the \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';

Browsing for additional metadata

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

\d table_name

Detailed information about the specified table will be displayed.

Using GUI tools

If you're more comfortable with a graphical interface, there are a number of GUI tools 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 data management and querying.

Conclusion

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