JIYIK CN >

This article will use the PostgreSQL database to show the different commands we can use to return a collection of database tables.

MySQLA common command you will encounter in databases is , SHOW TABLESbut in PostgreSQL, the database management system does not understand this command.

Getting started with PostgreSQL

You can install PostgreSQL database and log in to your database using the following command.

>psql -U postgres

Prompt for password, we should enter the password we specified during the installation process and press Enter.

We might have created multiple databases, we should list all available databases using the following command.

\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, , template0and template1. To select the database we want to work with employee, use the following command.

The connection is transferred from the currently connected database postgresto the one we want to use employee.

postgres=# \c employee;

In PostgreSQL, use \dtthe command to display the tables.

\dtThe command is used to describe all the tables in PostgreSQL and is used as shown below. This command returns one row as we have only one table in the database.

employee=# \dt

Output:

          List of relations
 Schema |   Name   | Type  |  Owner
--------+----------+-------+----------
 public | employee | table | postgres
(1 row)

Displaying tables in a specific schema in PostgreSQL

Since we can have different schemas in PostgreSQL to hold different databases, 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 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 schemas in PostgreSQL

Just as we did above to return a list of tables in the public schema, we can use the same command without specifying any schema to return all the tables in the 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  --

In PostgreSQL, use information_schemaSHOW TABLE

information_schemaIs a table that contains information about the current database, and we can selectquery it using the statement to find common entities in the database.

This query may not be easy to read. We can fix this by turning on extended display using the following command.

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                |