How to List Tables in PostgreSQL
- Get Started with PostgreSQL Database
-
Use
\dtCommand to Show Tables in PostgreSQL - Show Tables in a Specific Schema in PostgreSQL
- Show Tables in All the Schemas in PostgreSQL
-
Use
information_schemato Show Tables in PostgreSQL
This tutorial will use the PostgreSQL database to show the different commands that we can use to return a collection of database tables.
In the MySQL database, the common command you will encounter is SHOW TABLES, but in PostgreSQL, this command is not understood by the database management system.
Get Started with PostgreSQL Database
You can install the PostgreSQL database and use the following command to log in to your database.
>psql -U postgres
A password is prompted, and we should enter the password that we specified during the installation process and press enter.
We might have created multiple databases, and we should use the following command to list all the available databases.
\l
Output:
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+--------------------+--------------------+-----------------------
employee | postgres | UTF8 | English_Kenya.1252 | English_Kenya.1252 |
postgres | postgres | UTF8 | English_Kenya.1252 | English_Kenya.1252 |
template0 | postgres | UTF8 | English_Kenya.1252 | English_Kenya.1252 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | English_Kenya.1252 | English_Kenya.1252 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
On the column labeled Name, we can see three databases employee, postgres, template0, and template1. To select the database we want to use, employee, use the following command.
The connection shifts from postgres, the currently connected database, to the employee we want to use.
postgres=# \c employee;
Use \dt Command to Show Tables in PostgreSQL
The \dt command is used in PostgreSQL to describe all tables and is used as shown below. The command returns one row since we only have one table in the database.
employee=# \dt
Output:
List of relations
Schema | Name | Type | Owner
--------+----------+-------+----------
public | employee | table | postgres
(1 row)
Show Tables in a Specific Schema in PostgreSQL
Since we can have different schemas holding different databases in PostgreSQL, we can specify the schema we want in our query, and all the tables in that schema will be returned to us.
The following command returns all the tables in the public schema.
employee=# \dt public.*
Output:
List of relations
Schema | Name | Type | Owner
--------+----------+-------+----------
public | employee | table | postgres
(1 row)
Show Tables in All the Schemas in PostgreSQL
Just as we did above to return the tables list in the public schema, we can use the same command without specifying any schema to return all the tables in our database.
employee=# \dt *.*
Output:
List of relations
Schema | Name | Type | Owner
--------------------+-------------------------+-------+----------
information_schema | sql_features | table | postgres
information_schema | sql_implementation_info | table | postgres
information_schema | sql_parts | table | postgres
information_schema | sql_sizing | table | postgres
pg_catalog | pg_aggregate | table | postgres
pg_catalog | pg_am | table | postgres
pg_catalog | pg_amop | table | postgres
pg_catalog | pg_amproc | table | postgres
pg_catalog | pg_attrdef | table | postgres
pg_catalog | pg_attribute | table | postgres
pg_catalog | pg_auth_members | table | postgres
pg_catalog | pg_authid | table | postgres
pg_catalog | pg_cast | table | postgres
pg_catalog | pg_class | table | postgres
pg_catalog | pg_collation | table | postgres
pg_catalog | pg_constraint | table | postgres
pg_catalog | pg_conversion | table | postgres
pg_catalog | pg_database | table | postgres
pg_catalog | pg_db_role_setting | table | postgres
pg_catalog | pg_default_acl | table | postgres
pg_catalog | pg_depend | table | postgres
pg_catalog | pg_description | table | postgres
pg_catalog | pg_enum | table | postgres
pg_catalog | pg_event_trigger | table | postgres
pg_catalog | pg_extension | table | postgres
pg_catalog | pg_foreign_data_wrapper | table | postgres
-- More --
Use information_schema to Show Tables in PostgreSQL
The information_schema is a table that holds information about the current database, and we can query it using a select statement to find the public entities in our database.
This query might not be easy to read. We can use the following command to switch on the expanded display to solve this issue.
employee=# \x
select * from information_schema.tables where table_schema='public';
Output:
employee=# select * from information_schema.tables where table_schema='public';
-[ RECORD 1 ]----------------+-----------
table_catalog | employee
table_schema | public
table_name | employee
table_type | BASE TABLE
self_referencing_column_name |
reference_generation |
user_defined_type_catalog |
user_defined_type_schema |
user_defined_type_name |
is_insertable_into | YES
is_typed | NO
commit_action |
David is a back end developer with a major in computer science. He loves to solve problems using technology, learning new things, and making new friends. David is currently a technical writer who enjoys making hard concepts easier for other developers to understand and his work has been published on multiple sites.
LinkedIn GitHub