Tables are the most important database object in any relational database like PostgreSQL as they serve as the primary means of storing data in rows and columns. Once a table is created, users can perform different operations on it. However, it is crucial to confirm the existence/presence of a specific table before working with it in PostgreSQL.

This blog post will discuss various methods of checking whether a particular table exists in a database.

How to Check/Verify if a Specific Table Exists in PostgreSQL Database?

Accessing or using a particular Postgres table demands the existence of that specific table in the database. Therefore, Postgres offers various methods to check the presence of a specific table in a database. In this post, the below-listed methods will be discussed to check the existence of a Postgres table:

• Using information_schema
• Using pg_catalog
• Using pg_tables

How to Check if a Specific Table Exists in PostgreSQL Database Using information_schema?

Postgres provides a system schema named “information_schema” that helps us fetch the metadata about the database objects. Using information_schema, users can test the presence of a specific table. The below snippet illustrates the basic syntax of using the information_schema to check the existence of a table:

SELECT EXISTS (
SELECT 1
FROM information_schema.tables
WHERE table_name = 'name_of_table'
) AS table_existence;

In the above syntax, the EXISTS operator is used with the information_schema to validate the existence of a particular table.

Example: Check the Table’s Existence Using information_schema

The below coding example tests the existence of the “emp_details” table by utilizing the “information_schema”:

SELECT EXISTS (
SELECT 1
FROM information_schema.tables
WHERE table_name = 'emp_details'
) AS table_existence;

The output snippet retrieves true, proving that the selected table exists in the connected database:

How to Check if a Specific Table Exists in PostgreSQL Database Using pg_catalog?

Postgres provides a system schema named “pg_catalog” that allows us to fetch the metadata about the tables and views. Using pg_catalog, users can test the presence of a specific table. The below snippet illustrates how to use the pg_catalog to check the presence of a specific table:

SELECT EXISTS (
SELECT 1
FROM pg_catalog.pg_class
WHERE relname = 'name_of_table'
AND relkind = 'r'
) AS table_existence;

In the above syntax, the EXISTS operator is used with the pg_catalog to test the existence of a particular table. The relkind = 'r' indicates that the relation type must be a regular table.

Example: Check the Table’s Existence Using pg_catalog

The following snippet checks the existence of the “emp_details” table using the “pg_catalog”:

SELECT EXISTS (
SELECT 1
FROM pg_catalog.pg_class
WHERE relname = 'emp_details'
AND relkind = 'r'
) AS table_existence;

The boolean “true” in the output verifies that the selected table exists in the connected database:

How to Check if a Specific Table Exists in PostgreSQL Database Using pg_tables?

PostgreSQL offers a system view named pg_tables view that retrieves details about tables of a specific schema or all schemas in the current database. Here is the syntax for utilizing the pg_tables view in Postgres:

SELECT EXISTS (
SELECT 1
FROM pg_tables
WHERE tablename = 'name_of_table'
) AS table_existence;

The above syntax utilizes the EXISTS operator with the “pg_tables” view to confirm the presence of a particular table.

Example: Check the Table’s Existence Using pg_tables

Type the following piece of code to check the table’s existence using the pg_tables view:

SELECT EXISTS (
SELECT 1
FROM pg_tables
WHERE tablename = 'emp_details'
) AS table_existence;

The output demonstrates that the selected table exists in the current database:

That’s all about checking the existence of a specific table in the Postgres database.

Conclusion

In PostgreSQL, the “information_schema”, “pg_catalog”, and “pg_tables” are used to check the existence of a specific table in the current database. All these methods retrieve a boolean true or false, which indicates the presence of the selected table in the current database. This post has illustrated three different methods to check the presence of a particular table in the Postgres database.