How to Display All Tables in PostgreSQL

Introduction on How to Display All Tables in PostgreSQL

This tutorial will explain how to use the PostgreSQL list table function and will specifically cover how to display all of the tables in PostgreSQL. One of the more common database administration tasks is being able to discern what databases holds what information. This tutorial will explain two ways of displaying all of the PostgreSQL list tables stored on the server, including the standard way to display all tables PostgreSQL and also how to show all of the existing tables using the pg_catalog schema.

Prerequisites for using PostgreSQL

  • PostgreSQL must be properly installed and working. Execute the service postgresql status command to confirm the status is active and then press the CTRL + C keys to exit.

  • The interactive PSQL command-line for PostgreSQL must be properly installed and working. Confirm the status with the psql -V command.

The results should resemble the following screenshot:

Screenshot of a terminal window getting the PostgreSQL server status and psql version

Accessing the PostgreSQL using the ‘psql’ command-line interface

Access the PostgreSQL database on the localhost server by executing the following command with the psql command-line interface:

sudo su - postgres

The system will request the password. Enter the password and press the Return key to gain access to Postgres.

Now execute the following command to access the database:

psql some_username -h 127.0.0.1 -d some_database

The system will again request the user’s password; input the password again and press Return.

Create a PostgreSQL Database

Open a command prompt window if working in Windows or a terminal window if working with mac or Linux system. Execute the following command to create a PostgreSQL database:

CREATE DATABASE db_name;

Following is a specific example:

CREATE DATABASE test;

Note that using the command \l in the psql command line interface will display all of the current PostgreSQL databases.

Connect to a PostgreSQL database using PSQL

To connect into a specific database, execute the \c command followed by the database name to enter and write queries.

Create a PostgreSQL table

After creating the PostgreSQL database, execute the following code to create a PostgreSQL table:

CREATE TABLE TABLE_NAME(COLUMN_NAME + DATATYPE + CONSTRAINTS[OPTIONAL]);

A specific example is:

CREATE TABLE sample_tbl(
ID INT NOT NULL PRIMARY KEY,
SAMPLE_NAME TEXT
);

Show the List of Table Names in PostgreSQL

Following is the ‘psql’ command for showing the table in PostgreSQL:

\dt

The above code should return a result that resembles the following:

List OF relations
Schema | Name | TYPE | Owner
--------+--------------+-------+----------
public | employee | TABLE | postgres
public | sample_table | TABLE | postgres
public | student | TABLE | postgres
(3 ROWS)

Show the list of tables using the pg_catalog schema

The following SELECT statement demonstrates another way of displaying the list of table names in PostgreSQL:

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

The above code should return a result that resembles the following:

test=# SELECT * FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+--------------+------------+------------+------------+----------+-------------+-------------
public | employee | postgres | | t | f | f | f
public | student | postgres | | t | f | f | f
public | sample_table | postgres | | t | f | f | f
(3 ROWS)

Conclusion on How to use the PostgreSQL Show Tables Function

This tutorial demonstrated how to display all of the tables in PostgreSQL, specifically explaining two ways of showing the PostgreSQL list tables. These included the standard way of showing the list of table names in PostgreSQL and also how to show the list of tables using the pg_catalog schema. The article also covered how to create a PostgreSQL database, how to access the PostgreSQL database using the ‘psql’ command-line interface, how to connect to a PostgreSQL database using PSQL and how to create a PostgreSQL table. Remember that the system will require entering the user’s password twice in order to gain access to Postgres database.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.