This tutorial aims to explore the various methods and techniques that we can use to check if a given table exists in the database. You can use the provided ways to implement the error-handling features and improve your database and application stability.
Method 1: Using the Information Schema Catalog
Like MySQL, PostgreSQL provides us with an access to the system catalog called “information schema”. This catalog stores the metadata about all the database objects sthat are tored in the server. This includes all tables, columns, constraints, functions, etc.
To verify whether a table exists in the database using the “information schema” catalog, we can query it as follows:
SELECT 1
FROM information_schema.tables
WHERE table_name = 'table_name'
);
Replace the table_name with the name of the table whose existence you wish to verify.
The query returns a Boolean value: “t” if the table exists or “f” if it doesn’t. You can use this result to execute your queries conditionally.
Take the follwoing example query that verifies the existence of the “weight” table from the usda database.
Since the table exists in the database, the query returns the following output:
--------
t
(1 row)
We use the following query if we run the query on a table that doesn’t exist:
Output:
--------
f
(1 row)
Method 2: Using the Pg_Tables System Catalog
We also have access to the pg_tables system catalog in PostgreSQL. This catalog holds the information about all the tables in a PostgreSQL database.
To query this catalog for the existence of a given table, we can run the query as follows:
SELECT 1
FROM pg_tables
WHERE tablename = 'table_name'
);
Replace the table_name with the target table.
Example:
Similarly, the query should return a Boolean True which is denoted as “t” if the table exists, or a Boolean False which is denoted as “f” if the table does not exist.
Method 3: Using the Pg_catalog.pg_class System Catalog
As you can guess, we can also use the pg_catalog.pg_class catalog to check if a table exists. This catalog stores information about all the database objects on the server.
The query is as follows:
SELECT 1
FROM pg_catalog.pg_class
WHERE relname = 'table_name'
AND relkind = 'r'
);
Example:
r');
Resulting Output:
--------
t
(1 row)
The given output indicates that the specified table exists in the server.
NOTE: In this query, the relKind parameter defines the object type. The type “r” refers to a regular database table.
Method 4: Using a Custom Function
Instead of calling either of the methods that we previously provided, we can encapsulate the functionality of checking the existence of a table into a custom function.
The following example shows how to define a function that checks whether the table with the provided name exists on the server by querying the information_schema catalog:
BEGIN
RETURN EXISTS (
SELECT 1
FROM information_schema.tables
WHERE table_schema = schema_name
AND table_name = tbl_name
);
END;
$$ LANGUAGE plpgsql;
The given exmample defines a function called “table_exists”. The function accepts the table name as a string type and returns a Boolean value.
The function then queries the information_schema.tables catalog for the table with the specified name. If the table exists, the function returns a Boolean True and returns a Boolean False otherwise.
We can then call this function and pass the target table name as follows:
Example:
The previous query uses the table_exists() function to check whether the “weight” table exists in the “public” schema.
Output:
--------------
t
(1 row)
In this case, the function returns True as the table exists in the defined schema.
Conclusion
This tutorial explored the various methods to check if a table exists in PostgreSQL. Whether you prefer querying the information schema, system catalogs, or using a PL/pgSQL function, you can ensure that your queries don’t fail due to non-existent tables.
