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
Once connected to the desired database using psql, the easiest way to list all tables in the current database is to use the
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
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';
Sometimes, you may also want to list views in addition to tables. Use the
\dv command to list all views in the current database:
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:
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.
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.