Introduction to Postgres Show Tables
Postgres show tables are defined as list tables from a specific database or specific schema; we can retrieve a table from command as \dt and using the query to retrieving data from the pg_catalog schema. In MySQL, we can list all tables from the database using the show tables; in PostgreSQL, we can list all the database tables using the \dt command. For showing tables from the database, we need to connect to the specific database from which we need to show the tables.
Syntax and Parameters
Below is the syntax of show tables in PostgreSQL.
1. Show tables from the database
\c database_name
\dt
OR
\dt+ (Show descriptive output of show tables)
2. Show specific schema tables
\dt shema_name.*
OR
\dt+ shema_name.* (Show descriptive output of show tables)
3. Show all schema tables
SELECT *(Show all rows from pg_tables) FROM pg_catalog.pg_tables;
OR
\dt *.* (All schema and all tables)
Below is the parameter description syntax of show tables in PostgreSQL
- Database name: This is defined as the database name is used to connect to the database to show all tables from a connected database using \dt command. We can use any database name to show all tables from the database.
- \dt: This command is used to show all tables from the connected database.
- \dt+: This command is used to show all table descriptive output from the connected database.
- Schema name: It is defined as to show all tables from the specified schema. We can use any schema name to show all tables from the schema.
- Select: Select operations are used when we show tables from the catalog schema using pg_tables.
- Pg_catalog: This schema uses table name as pg_tables; this schema is a catalog schema in PostgreSQL.
- Pg_tables: This table is a system table that contains the information related to all tables. This contains information like table name, schema name, etc.
- \dt *.*: This is used when we want to show all the schema tables; first, * define as all schema, and second will define as all tables.
- *: This is defined as to show all tables from the specified schema. First is schema name from which we have shown tables, the second * is defined as to show all tables from the specified schema.
How does Show Table work in PostgreSQL?
Below is the working of the show table in PostgreSQL.
To show a table from the specified database its must be present on the database server. The below example shows that we need to connect to the specified database to show the database’s table.
Code:
\dt
\c testing;
\dt
In the above example, we first connected to the default database, i.e. Postgres database; while connecting to this database, it will only display the connected databases’ tables. In the first example, the Postgres database did not contain any tables to return an empty set. But in the second example, we have connected to the testing database; after connecting to the testing database, it will display all tables from the testing database.
Examples to Implement Postgres Show Tables
Below is the example of show tables in PostgreSQL.
Example #1
Show all tables from the specified database.
The below example shows that display all tables from the specified database. We have to retrieve all tables from the testing database. We need first connect to the database to show tables.
Code:
\c testing;
\dt
Output:
Example #2
Show all tables descriptive output from the specified database.
The below example shows that show descriptive output from all tables from the specified database. We have to retrieve all tables from the testing database.
Code:
\c testing;
\dt+
Output:
Example #3
Show description of the specified table
The below example shows the description of the specified table. We have described the student table.
Code:
\c testing;
\d student;
\d+ student;
Output:
Example #4
Show all table from the specified schema.
The below example shows that show all tables from the specified schema. We have to show all tables from the public schema.
Code:
\dt public.*
\dt+ public.*
Output:
Example #5
Show all tables from all schema.
The below example shows that display all tables from all schema.
Code:
\dt *.*
\dt+ *.*
Output:
Example #6
Show all tables from specified schema using the query.
The below example shows that retrieving all tables from the specified schema using the query.
Code:
SELECT * FROM information_schema.tables WHERE table_schema = 'public' ORDER BY table_name;
Output:
Recommended Articles
This is a guide to Postgres Show Tables. Here we discuss an introduction, syntax, parameters, how does it work with examples to implement. You can also go through our other related articles to learn more –
