How to Check if a Table Exists in PostgreSQL

Avatar

By squashlabs, Last Updated: Oct. 30, 2023

How to Check if a Table Exists in PostgreSQL

Checking for a Table Presence with the System Catalog Tables

One of the simplest ways to verify table existence in PostgreSQL is by querying the system catalog tables. PostgreSQL stores metadata about tables, columns, indexes, and other database objects in system catalog tables. By querying these catalog tables, we can determine if a specific table exists in the database.

Here is an example of how to check if a table exists using the system catalog tables:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SELECT EXISTS (
SELECT 1
FROM pg_tables
WHERE schemaname = 'public'
AND tablename = 'my_table'
);
SELECT EXISTS ( SELECT 1 FROM pg_tables WHERE schemaname = 'public' AND tablename = 'my_table' );
SELECT EXISTS (
   SELECT 1
   FROM pg_tables
   WHERE schemaname = 'public'
   AND tablename = 'my_table'
);

In this example, we use the

pg_tables
pg_tables catalog table to check if a table named 'my_table' exists in the 'public' schema. The query returns a boolean value indicating whether the table exists or not.

Another approach to check table presence is by using the

information_schema
information_schema views. The
information_schema
information_schema views provide a standardized way to access metadata about database objects. Here is an example using the
information_schema.tables
information_schema.tables view:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SELECT EXISTS (
SELECT 1
FROM information_schema.tables
WHERE table_schema = 'public'
AND table_name = 'my_table'
);
SELECT EXISTS ( SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'my_table' );
SELECT EXISTS (
   SELECT 1
   FROM information_schema.tables
   WHERE table_schema = 'public'
   AND table_name = 'my_table'
);

This query is similar to the previous example but uses the

information_schema.tables
information_schema.tables view instead. It also returns a boolean value indicating the existence of the table.

Related Article: Merging Two Result Values in SQL

Determining Table Existence with to_regclasss

In addition to querying the system catalog tables, PostgreSQL provides a built-in function called

to_regclass
to_regclass that can be used to determine the existence of a table. The
to_regclass
to_regclass function takes a table name as an argument and returns the OID (Object ID) of the table if it exists, or
NULL
NULL if it does not exist.

Here is an example of how to use the

to_regclass
to_regclass function to determine table existence:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SELECT to_regclass('public.my_table');
SELECT to_regclass('public.my_table');
SELECT to_regclass('public.my_table');

In this example, we pass the fully qualified table name ('public.my_table') to the

to_regclass
to_regclass function. If the table exists, the function will return the OID of the table. Otherwise, it will return
NULL
NULL.

Using the pg_class Catalog table

Related Article: Executing Queries to Remove Duplicate Rows in PostgreSQL

Another way to find table existence in PostgreSQL is by querying the

pg_class
pg_class catalog table. The
pg_class
pg_class table stores information about various objects in the database, including tables. By filtering the table name and schema name, we can determine if a specific table exists.

Here is an example of how to find table existence using the

pg_class
pg_class table:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SELECT EXISTS (
SELECT 1
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
AND c.relname = 'my_table'
AND c.relkind = 'r'
);
SELECT EXISTS ( SELECT 1 FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = 'public' AND c.relname = 'my_table' AND c.relkind = 'r' );
SELECT EXISTS (
   SELECT 1
   FROM pg_class c
   JOIN pg_namespace n ON n.oid = c.relnamespace
   WHERE n.nspname = 'public'
   AND c.relname = 'my_table'
   AND c.relkind = 'r'
);

In this example, we join the

pg_class
pg_class table with the
pg_namespace
pg_namespace table to get the schema information. We then filter the results based on the schema name ('public'), table name ('my_table'), and object type ('r' for a table). The query returns a boolean value indicating the existence of the table.

Updating JSONB Columns in PostgreSQL

This tutorial teaches you how to efficiently update JSONB data in your PostgreSQL database. From updating specific key-value pairs to setting new val… read more

Integrating Fluent Bit with PostgreSQL Databases

Fluent Bit and PostgreSQL databases can be seamlessly integrated to enhance logging capabilities. By leveraging Fluent Bit, developers can achieve im… read more

Adjusting Output Column Size in Postgres Queries

Modifying the output column size in PostgreSQL queries is a crucial procedure for optimizing data presentation. This article explores the process of … read more

Tutorial: Nested SQL Joins in Databases

In this technical examination, we explore the use of nested SQL joins within databases. From an overview of nested joins to syntax, relational databa… read more

How to Extract Data from PostgreSQL Databases: PSQL ETL

In this article, we will guide you through the process of extracting data from PostgreSQL databases using PSQL ETL. You will learn about various tech… read more

How to Create a Database from the Command Line Using Psql

Creating a database from the command line using psql is a process that can be done in a few simple steps. This article provides a step-by-step guide … read more

Using SQL Between for Date Ranges in MySQL and PostgreSQL

This article serves as a guide for using SQL BETWEEN in MySQL and PostgreSQL to work with date ranges. It covers various topics such as querying date… read more

Resolving Scalar Join Issues with SQL Tables in Databases

Troubleshoot and solve scalar join issues in SQL databases with this article. Learn about working with scalar values, structuring and managing databa… read more

How to Select Specific Columns in SQL Join Operations

When performing SQL join operations, it is important to know how to select specific columns. This article will guide you through the process, providi… read more

Determining if Your PostgreSQL Query Utilizes an Index

When it comes to PostgreSQL query optimization, understanding how indexes are utilized is crucial for improved efficiency. This article provides insi… read more