EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL Show Tables
Secondary Sidebar
PostgreSQL Tutorial
  • Basic
    • What is PostgreSQL
    • PostgreSQL Features
    • How to Install PostgreSQL
    • PostgreSQL Versions
    • PostgreSQL Architecture
    • PostgreSQL GUI
    • Postgres Command-Line
    • PostgreSQL Variables
    • PostgreSQL Data Types
    • PostgreSQL NOT NULL
    • PostgreSQL Integer
    • PostgreSQL Boolean
    • PostgreSQL BIGINT
    • PostgreSQL NULLIF
    • PostgreSQL Administration
    • PostgreSQL Commands
    • PostgreSQL Operators
    • PostgreSQL IN Operator
    • Postgres like query
    • PostgreSQL encode
    • PostgreSQL Cheat Sheet
    • PostgreSQL List Databases
    • PostgreSQL Rename Database
  • Control Statement
    • PostgreSQL IF Statement
    • PostgreSQL if else
    • PostgreSQL CASE Statement
    • PostgreSQL LOOP
    • PostgreSQL For Loop
    • PostgreSQL While Loop
  • Joins
    • Joins in PostgreSQL
    • PostgreSQL Inner Join
    • PostgreSQL Outer Join
    • LEFT OUTER JOIN in PostgreSQL
    • PostgreSQL FULL OUTER JOIN
    • PostgreSQL LEFT JOIN
    • PostgreSQL Full Join
    • PostgreSQL Cross Join
    • PostgreSQL NATURAL JOIN
    • PostgreSQL UPDATE JOIN
  • Queries
    • PostgreSQL Queries
    • PostgreSQL INSERT INTO
    • PostgreSQL WHERE Clause
    • PostgreSQL WITH Clause
    • PostgreSQL ORDER BY
    • PostgreSQL ORDER BY Random
    • PostgreSQL ORDER BY DESC
    • PostgreSQL GROUP BY
    • PostgreSQL group_concat
    • PostgreSQL HAVING
    • PostgreSQL Recursive Query
  • Advanced
    • PostgreSQL Schema
    • Postgres List Schemas
    • PostgreSQL Drop Schema
    • PostgreSQL VARCHAR
    • Array in PostgreSQL
    • PostgreSQL DDL
    • PostgreSQL List Users
    • Postgres Default User
    • Postgres add user
    • PostgreSQL User Password
    • PostgreSQL log_statement
    • PostgreSQL repository
    • PostgreSQL shared_buffer
    • PostgreSQL String Functions
    • PostgreSQL Compare Strings
    • PostgreSQL Text Search
    • PostgreSQL TEXT
    • PostgreSQL String Array
    • PostgreSQL where in array
    • PostgreSQL Constraints
    • PostgreSQL UNIQUE Constraint
    • PostgreSQL CHECK Constraint
    • PostgreSQL INTERSECT
    • PostgreSQL Like
    • Cursors in PostgreSQL
    • PostgreSQL UNION ALL
    • Indexes in PostgreSQL
    • PostgreSQL Index Types
    • PostgreSQL REINDEX
    • PostgreSQL UNIQUE Index
    • PostgreSQL Clustered Index
    • PostgreSQL DROP INDEX
    • PostgreSQL DISTINCT
    • PostgreSQL FETCH
    • PostgreSQL RAISE EXCEPTION
    • PostgreSQL Auto Increment
    • Sequence in PostgreSQL
    • Wildcards in PostgreSQL
    • PostgreSQL Subquery
    • PostgreSQL Alias
    • PostgreSQL LIMIT
    • PostgreSQL Limit Offset
    • PostgreSQL LAG()
    • PostgreSQL Table
    • Postgres Show Tables
    • PostgreSQL Describe Table
    • PostgreSQL Lock Table
    • PostgreSQL ALTER TABLE
    • Postgres Rename Table
    • PostgreSQL List Tables
    • PostgreSQL TRUNCATE TABLE
    • PostgreSQL Table Partitioning
    • Postgres DROP Table
    • PostgreSQL Functions
    • PostgreSQL Math Functions
    • PostgreSQL Window Functions
    • Aggregate Functions in PostgreSQL
    • PostgreSQL Primary Key
    • Foreign Key in PostgreSQL
    • PostgreSQL Procedures
    • PostgreSQL Stored Procedures
    • PostgreSQL Views
    • PostgreSQL Materialized Views
    • Postgres Create View
    • PostgreSQL Triggers
    • PostgreSQL DROP TRIGGER
    • PostgreSQL Date Functions
    • PostgreSQL TO_DATE()
    • PostgreSQL datediff
    • PostgreSQL Timestamp
    • PostgreSQL CURRENT_TIMESTAMP()
    • PostgreSQL Notify
    • PostgreSQL LENGTH()
    • PostgreSQL blob
    • PostgreSQL Median
    • PostgreSQL kill query
    • PostgreSQL Formatter
    • PostgreSQL RANK()
    • PostgreSQL Select
    • PostgreSQL Average
    • PostgreSQL DATE_PART()
    • PostgreSQL EXECUTE
    • PostgreSQL COALESCE
    • PostgreSQL EXTRACT()
    • PostgreSQL Sort
    • PostgreSQL TO_CHAR
    • PostgreSQL Interval
    • PostgreSQL Number Types
    • PostgreSQL ROW_NUMBER
    • Alter Column in PostgreSQL
    • PostgreSQL Identity Column
    • PostgreSQL SPLIT_PART()
    • PostgreSQL CONCAT()
    • PostgreSQL replace
    • PostgreSQL TRIM()
    • PostgreSQL MAX
    • PostgreSQL DELETE
    • PostgreSQL Float
    • PostgreSQL OID
    • PostgreSQL log
    • PostgreSQL REGEXP_MATCHES()
    • PostgreSQL MD5 
    • PostgreSQL NOW()
    • PostgreSQL RANDOM
    • PostgreSQL round
    • PostgreSQL Trunc()
    • PostgreSQL TIME
    • PostgreSQL IS NULL
    • PostgreSQL CURRENT_TIME
    • PostgreSQL MOD()
    • Postgresql Count
    • PostgreSQL Datetime
    • PostgreSQL MIN()
    • PostgreSQL age()
    • PostgreSQL enum
    • PostgreSQL OR
    • PostgreSQL Wal
    • PostgreSQL NOT IN
    • PostgreSQL SET
    • PostgreSQL Current Date
    • PostgreSQL Compare Date
    • PostgreSQL SERIAL
    • PostgreSQL UUID
    • PostgreSQL Merge
    • PostgreSQL Database
    • PostgreSQL Clone Database
    • PostgreSQL Copy Database
    • PostgreSQL Show Databases
    • PostgreSQL Restore Database
    • PostgreSQL DROP DATABASE
    • PostgreSQL ALTER DATABASE
    • Postgres DROP Database
    • Postgres Dump Database
    • PostgreSQL OFFSET
    • PostgreSQL GRANT
    • PostgreSQL COMMIT
    • PostgreSQL ROLLUP
    • PostgreSQL JSON
    • EXPLAIN ANALYZE in PostgreSQL
    • PostgreSQL Temporary Table
    • PostgreSQL Show Tables
    • PostgreSQL cluster
    • PostgreSQL Replication
    • PostgreSQL Logical Replication
    • PostgreSQL flush privileges
    • PostgreSQL Tablespaces
    • CAST in PostgreSQL
    • PostgreSQL CTE
    • hstore in PostgreSQL
    • PostgreSQL Encryption
    • PostgreSQL DECODE()
    • PostgreSQL Vacuum
    • PostgreSQL EXCLUDE
    • Postgres Change Password
    • Postgres Delete Cascade
    • PostgreSQL EXCEPT
    • PostgreSQL Roles
    • PostgreSQL Link
    • PostgreSQL Partition
    • PostgreSQL column does not exist
    • PostgreSQL Log Queries
    • PostgreSQL escape single quote
    • PostgreSQL Query Optimization
    • PostgreSQL Character Varying
    • PostgreSQL Transaction
    • PostgreSQL Extensions
    • PostgreSQL Import CSV
    • PostgreSQL Client
    • PostgreSQL caching
    • PostgreSQL Incremental Backup
    • PostgreSQL JSON vs JSONNB
    • PostgreSQL JDBC Driver
    • PostgreSQL Interview Questions

PostgreSQL Show Tables

PostgreSQL Show Tables

Introduction to PostgreSQL Show Tables

We often need to study and observe the complete data structures and the objects stored in the database that we are using. Knowing beforehand the tables that are present in our current database not only helps us to understand our database clearly but also lets us know the names of the tables and the properties of them before using them. In Postgres, we can show all the tables that are present in a particular database by using either of the two methods that are 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.

To get the tables present in the database, we can make the use of 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 that is present in pg_catalog schema that stores the details of all the tables of the database. In this article, we will make the use of both these methods to list out the tables that are 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 make the use of the \dt meta-command that is 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 that password will be entered by me after the execution of the following command.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Code:

psql -U postgres -W

The execution of the above command statement gives an output which is as shown below which demands the password as soon as the command is fired and we need to mention the password that is 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 out all the available databases that are present in the database server. So that we can choose the one and connect to it and retrieve the tables of that database. To get the list of all the database names, we can make the use of meta-command named \d as shown below.

Code:

\d

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,241 ratings)

Output:

PostgreSQL Show Tables 2

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

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

Code:

\c educba

Output:

PostgreSQL Show Tables 3

Now, to show all the tables that are store inside the educba database, we can make the use of meta-command \dt.

We can simply execute the following command.

Code:

\dt

Output:

\dt

We can observe that there are two tables namely demo and educba_demo that are present in the Postgres database and are public. To get the additional details about the tables that are retrieved, we can make the use of 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 the use of the alternative PostgreSQL query on the table pg_tables that 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 PostgreSQL database. The system tables also called schemas named information_schema and pg_catalog are already present in our PostgreSQL once it is 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

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

There is one more way, using which we can retrieve the list of the tables of the particular database. We can make the use of 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 three records are retrieved instead of two as this query retrieves the views along with tables and remote_educba_data is the view present in the educba database.

Conclusion – PostgreSQL Show Tables

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

Recommended Articles

This is a guide to PostgreSQL Show Tables. Here we discuss the introduction, Psql metacommand to retrieve the tables, using the pg_tables to get the list of tables. You may also have a look at the following articles to learn more –

  1. PostgreSQL IS NULL
  2. PostgreSQL Restore Database
  3. PostgreSQL ORDER BY Random
  4. PostgreSQL TO_DATE()
Popular Course in this category
PostgreSQL Course (2 Courses, 1 Project)
  2 Online Courses |  1 Hands-on Project |  7+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more