EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL Show Tables
 

PostgreSQL Show Tables

Updated May 24, 2023

PostgreSQL Show Tables

 

 

Introduction to PostgreSQL Show Tables

We often need to study and observe the complete data structures and the objects stored in our database. Knowing beforehand the tables that are present in our current database not only helps us understand our database clearly but also lets us know the names of the tables and their properties before using them. In Postgres, we can show all the tables in a particular database by using either of the two methods available in PostgreSQL. One of the most efficient and easy ways of doing so is by using the facility provided in Postgres names psql utility which provides various metacommands that are short and easy to use.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

To get the tables present in the database, we can use the psql meta-command named \dt that lists out all the tables of the connected database. Another way of doing so is to use the pg_tables table in the pg_catalog schema that stores the details of all the database tables. In this article, we will use both these methods to list the tables present in a particular database with the help of an example.

Psql metacommand to Retrieve the Tables

The metacommands are the psql utility provided in PostgreSQL, which provides us with a set of stored commands that can be accessed by simply prepending the \ backslash before the alphabetic letters that signify certain command that is followed by different operators on the optional basis, which enhance the operation. To retrieve the tables present in the database and show them in the list format, we can use the \dt meta-command provided in PostgreSQL.

We will first need to login to our PostgreSQL database server through the terminal using the following command where Postgres is the user name using which I will log in to my Postgres database server, and W is to mention I will enter that password after the execution of the following command.

Code:

psql -U postgres -W

The execution of the above command statement gives an output, as shown below, which demands the password as soon as the command is fired, and we need to mention the password set against the user using which we are trying to login. For example, here, the user is Postgres, whose password is “a”.

Output:

PostgreSQL Show Tables 1

Further, after connecting to the database server, we will need to list all the available databases in the database server so that we can choose the one and connect to it and retrieve the tables of that database. We can use a meta-command named \d, as shown below, to get the database names list.

Code:

\d

Output:

PostgreSQL Show Tables 2

Now, we will connect to the educba database using the meta-command \c and the name of the database. We can use the alternative command named \connect that helps us connect to the database.

We will use the metacommand \c as shown below to connect to the educba database.

Code:

\c educba

Output:

PostgreSQL Show Tables 3

Now, to show all the tables stored inside the educba database, we can use meta-command \dt.

We can execute the following command.

Code:

\dt

Output:

\dt

We can observe that two tables, namely demo and educba_demo, are present in the Postgres database and are public. To get the additional details about the retrieved tables, we can use the same meta-command and append the + plus operator to it, as shown below.

Code:

\dt+

Output:

demo and educba_demo

We can observe that besides the name of the table, its owner, and schema type, we can get the size of the table and the additional description if set against the table.

Using the pg_tables to Get the List of Tables

We can also make use of the alternative PostgreSQL query on the table pg_tables, which is located inside the pg_catalog database that stores the details of all the tables that include all the user-defined tables and the system tables present in the PostgreSQL database. The system tables, also called schemas named information_schema and pg_catalog, are already present in our PostgreSQL once installed. Hence, we will need to retrieve the records of the pg_tables table except for the records whose schema names are information_schema and pg_catalog. Finally, we can execute the following query to show the tables in PostgreSQL.

Code:

SELECT *
FROM pg_catalog.pg_tables
WHERE schemaname != 'information_schema'
AND schemaname != 'pg_catalog' ;

Output:

to Get the List

From the above query, we can observe the details about whether the tables have the indexes on them, rules, triggers, and security associated with it.

There is one more way to retrieve the list of the tables of the particular database. We can use the following query statement using the following query as that tables table inside the information_schema stores the details of the tables of the database.

Code:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name;

Output:

PostgreSQL Show Tables 8

We can observe that three records are retrieved instead of two as this query retrieves the views along with tables, and remote_educba_data is the view in the educba database.

Conclusion – PostgreSQL Show Tables

We can show all the tables of a particular database in PostgreSQL using either of the three ways that include the metacommands of the psql utility, using the pg_tables table of pg_catalog, and using the tables table of information_schema.

Recommended Articles

We hope that this EDUCBA information on “PostgreSQL Show Tables” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. PostgreSQL IS NULL
  2. Restore Database in PostgreSQL
  3. PostgreSQL ORDER BY Random
  4. PostgreSQL TO_DATE()

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW